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:

See Microsoft webpage for full Alter Table syntax details:

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.