Change Data Type - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Change Data Type

Author

Topic

Hub
Starting Member

5 Posts

Posted - 2011-02-17 : 03:10:16

Hi, I am using SQL2005, is there anyway to change the data type of a column from int to varchar(50) instead of drop the table and create it again ?

This field is 1 of the primary key in the table and currently holding > 40 millions of records.

yadhu_cse
Constraint Violating Yak Guru

252 Posts

Posted - 2011-02-17 : 03:17:27

if there are no records u can do by using
alter table xmz
modify column name datatype

pk_bohra
Master Smack Fu Yak Hacker

1182 Posts

Posted - 2011-02-17 : 03:38:15

One way :

Drop the primary key constraint (since the column you are modifying is part of it)

Alter the column datatype
Syntax: Alter table <tableName>
Alter column <columnname> <datatype>

Recreate the PrimaryKey.

Incase this primary key is getting referenced, then it will not be easy.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2011-02-18 : 11:09:18

quote:
Originally posted by yadhu_cse

if there are no records u can do by using
alter table xmz
modify column name datatype

Modify is not supported in SQL Server

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources