How do you change the datatype of a column in SQL Server?

Question

I am trying to change a column from a varchar(50) to a nvarchar(200). What is the SQL command to alter this table?

score 664 · Accepted Answer · edited Jul 10 '17 at 12:43

664

ALTER TABLE TableName 
ALTER COLUMN ColumnName NVARCHAR(200) [NULL | NOT NULL]

EDIT As noted NULL/NOT NULL should have been specified, see Rob's answer as well.

edited Jul 10 '17 at 12:43

siddhartha jain

1,006
10
16

answered Mar 09 '09 at 16:07

cmsjr

56,771
11
70
62

2

Why should it be specified? What is the benefit? Everything I want to leave as it is I don't have to mention, I think. – The incredible Jan Mar 10 '17 at 07:48
8

@TheincredibleJan That would be cool, but unfortunately it doesn't work that way. If you issue an `ALTER TABLE TableName ALTER COLUMN ColumnName` command to change an existing column that is `[NOT NULL]`, and you don't explicitly specify it, it will be `[NULL]` afterwards, as that's the default. – takrl Apr 19 '17 at 06:10
4

This only works if you don't have constraints and indexes on that column, otherwise, you need to drop everything and recreate it, which is tedious and grunt work, especially if you have many foreign references on that column. e.g. when changing from tinyint to int. – Hrvoje Batrnek Jun 09 '18 at 14:18
Careful if you have an IDE open (like SSMS). Even with my Designer Tab closed on the Table I was Altering (after running the script and right-clicking on the Table to select "Design") it still showed the old Data Types! It was only after closing ALL of my Tabs in Management Studio and opening the Design View again that it finally showed the updated DataType. Very scary, so be careful (it may be a cache-bug that is fixed by now or one MS never bothers fixing). For those wondering why I ran T-SQL when I use Design-View, I wanted to alter my DataTypes to `SysName` (which SSMS does not allow). – MikeTeeVee Apr 10 '19 at 02:37
I had no luck with these commands in XAMPP.`ALTER TABLE table MODIFY COLUMN column datatype` worked for me. – Pavindu May 06 '19 at 14:28
Pavindu, that is because MODIFY / XAMPP is not T-SQL (SQLServer). That is MySQL, which is different. – Eddy Howard Dec 11 '19 at 18:58

score 193 · Answer 2 · answered Mar 11 '09 at 16:38

193

Don't forget nullability.

ALTER TABLE <schemaName>.<tableName>
ALTER COLUMN <columnName> nvarchar(200) [NULL|NOT NULL]

answered Mar 11 '09 at 16:38

Rob Garrison

6,984
4
25
23

3

What is the matter with nullability? If I don't want to change it - what is the benefit in setting it again? – The incredible Jan Mar 10 '17 at 07:50
13

^ It will default to NULL after the alter table statement finishes executing without explicitly defining what it should be. – sc305495 Jul 31 '17 at 20:29
2

@sc305495 To be exact, it will default to whetever your ANSI_NULL_DEFAULT settings is. – Zikato Apr 15 '19 at 12:17

John Sansom · Answer 3 · 2013-02-26T18:08:14.787

26

Use the Alter table statement.

Alter table TableName Alter Column ColumnName nvarchar(100)

edited Feb 26 '13 at 18:08

answered Mar 09 '09 at 16:08

John Sansom

41,005
9
72
84

score 13 · Answer 4 · edited Oct 12 '15 at 06:39

13

The syntax to modify a column in an existing table in SQL Server (Transact-SQL) is:

ALTER TABLE table_name
    ALTER COLUMN column_name column_type;

For example:

ALTER TABLE employees
    ALTER COLUMN last_name VARCHAR(75) NOT NULL;

This SQL Server ALTER TABLE example will modify the column called last_name to be a data type of VARCHAR(75) and force the column to not allow null values.

see here

edited Oct 12 '15 at 06:39

pbaris

4,525
5
37
61

answered Oct 11 '15 at 21:15

Yogesh Bende

139
1
2

1

please add more information to your answer and format the code / query! – Sebastian Brosch Oct 11 '15 at 21:22

score 7 · Answer 5 · answered Mar 05 '20 at 07:53

7

ALTER TABLE [dbo].[TableName]
ALTER COLUMN ColumnName VARCHAR(Max) NULL

answered Mar 05 '20 at 07:53

Umar Asif

151
2
10

3

This doesn't seem to add anything over the existing accepted answer from years ago, and also the changes you've posted to the table structure don't actually match the question. – PeterJ Mar 05 '20 at 11:05

score 5 · Answer 6 · answered Sep 26 '16 at 20:12

5

For changing data type

alter table table_name 
alter column column_name datatype [NULL|NOT NULL]

For changing Primary key

ALTER TABLE table_name  
ADD CONSTRAINT PK_MyTable PRIMARY KEY (column_name)

answered Sep 26 '16 at 20:12

Alexander Zaldostanov

2,907
4
30
39

score 4 · Answer 7 · answered Dec 23 '14 at 18:25

4

As long as you're increasing the size of your varchar you're OK. As per the Alter Table reference:

Reducing the precision or scale of a column may cause data truncation.

answered Dec 23 '14 at 18:25

jocassid

4,429
1
13
6

score 1 · Answer 8 · answered Mar 09 '21 at 06:15

1

in 11g:

ALTER TABLE TableName
Modify ColumnName DataType;

EG: ALTER TABLE employees Modify BIRTH_DATE VARCHAR(30);

answered Mar 09 '21 at 06:15

Biman Pal

391
4
9

1

Question explicitly mentions SQL Server. – DatumPoint Jan 06 '23 at 01:29

score -1 · Answer 9 · edited Nov 10 '21 at 17:12

-1

ALTER TABLE [Performance].[dbo].[CRA_283_Violation]
ALTER COLUMN [Throughput_HS_DC_NodeB_3G_Alarm] bit

edited Nov 10 '21 at 17:12

mxmissile

11,464
3
53
79

answered Apr 06 '21 at 06:42

Babimetro

19
2

score -1 · Answer 10 · answered Sep 18 '22 at 13:52

The original question is for 'SQL Server' However, in the case you read this post when you are with a MySql Server, the 'ALTER COLUMN' cannot be used to change the column type.

To change the column type on a MYSQL server, you can use:

ALTER TABLE `USER`
    MODIFY SESSION_ID VARCHAR(100) NULL;

I used the line above to extend from varchar(20) to varchar(100) the column with a MySql Server.

score -1 · Answer 11 · answered Aug 29 '23 at 12:34

-1

For postgres sql it will be:

ALTER TABLE "TableName" ALTER "ColumnName" TYPE DataType;

answered Aug 29 '23 at 12:34

Bartosz546

95
5

score -13 · Answer 12 · edited Jan 29 '15 at 14:10

-13

Try this:

ALTER TABLE "table_name"
MODIFY "column_name" "New Data Type";

edited Jan 29 '15 at 14:10

Marius

15,148
9
56
76

answered Jan 29 '15 at 13:50

Kai Tzer

53
5

1

"Modify" obviously is not correct. Please compare with other answers. – Frank Jan 29 '15 at 17:01
3

This is old, but appears that Kai Tzer was proving MySql and/or Oracle DDLs. – Sheldon Cohen Mar 07 '18 at 19:55
1

Agree with Sheldon, the answers here not helped me with oracle sql, just this – AsfK Apr 12 '18 at 09:44

How do you change the datatype of a column in SQL Server?

12 Answers12

Linked

Related