Change column type in SQL Server

There are two ways to change the column type in SQL Server:

1- Is by writing a quick code in query script.

2- Is by using the enterprise manger: SSMS (SQL Server Management Studio).

 

Method 1

Run the following code in SQL server:

ALTER TABLE <table name> ALTER COLUMN <column name> DATA TYPE

Example Code:
ALTER TABLE tblExampleTable ALTER COLUMN ID INT

See Microsoft webpage for full Alter Table syntax details:
http://msdn.microsoft.com/en-us/library/ms190273.aspx

Method 2

To do the changes using the second method follow these steps:

a- Open SSMS

b- Navigate to your table which you want to modify column

c- Find column and Right click the choose Modify

image

d- A window will open in Edit mode so that you can change the type of each column:

image

e- Click on Save once you are done.

Note that when you click Save, you might show the below warning message. Saving changes is not permitted:

image

In this case, you can change some settings to allow this save and avoid the warning. To do this simply follow these steps:

1- In SSMS, click on Tools

2- Choose Options

3- Click on the Designers section from the left side menu

4- Un-check the box for: Prevent saving changes that require table re-creation

image

Now try to save again and this time it will work.