Change Data Type in SQL Server

Today, I will talk about how to change the data type of a column which has data.

Let’s create one simple table

CREATE TABLE EMPLOYEE_WORKING_HRS(EMP_ID INT, ENAME VARCHAR(30), YR INT, MON VARCHAR(3), WORKING_HRS INT, RATE INT);

Let’s insert few records into this table.

INSERT INTO EMPLOYEE_WORKING_HRS VALUES (100,'SAM',2009,'JAN',176,100);
INSERT INTO EMPLOYEE_WORKING_HRS VALUES (101,'LIMO',2009,'JAN',176,110);
INSERT INTO EMPLOYEE_WORKING_HRS VALUES (102,'NAT',2009,'JAN',176,120);
INSERT INTO EMPLOYEE_WORKING_HRS VALUES (103,'BECK',2009,'JAN',176,130);

Now I want to change the data type of YR column from INT to VARCHAR.

SP_HELP 'EMPLOYEE_WORKING_HRS'



Normally for people who are coming from different database backgrounds, it’s really a painful task. That is you need to add a column with this new data type and then update this column and rename the columns. This is really a lengthy process. With the SQL Server you don’t have to do all these. Just one simple ALTER STATEMENT can change this.

ALTER TABLE EMPLOYEE_WORKING_HRS ALTER COLUMN YR VARCHAR(4)

Simple isn’t it.

SP_HELP 'EMPLOYEE_WORKING_HRS'