Change column type in SQL Server

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

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

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:

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

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