change column datatype - 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 2008 Forums

SQL Server Administration (2008)

change column datatype

Author

Topic

arkiboys
Master Smack Fu Yak Hacker

1433 Posts

Posted - 2011-10-17 : 10:19:32

Hi,
In development, there is a table with over 36 million records.
One of the fields is varchar(50) and I would like to change it to char(4) but changing this in design window takes a long time and therefore gives a timeout error.

How is this solved please?
Thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-17 : 10:21:32

ALTER TABLE tablename ALTER COLUMN columnname char(4) NULL

i hope it doesnt have constraint/key on it

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

arkiboys
Master Smack Fu Yak Hacker

1433 Posts

Posted - 2011-10-17 : 10:40:48

This field is one of the Primary keys.
Why is this method better than making the change in the designer?

ALTER TABLE myTable ALTER COLUMN Code char(4) NOT NULL

After running the sql above it gives the error message:
Msg 5074, Level 16, State 1, Line 1
The object 'PK_myTable' is dependent on column 'Code'.
Msg 4922, Level 16, State 9, Line 1
ALTER TABLE ALTER COLUMN Code failed because one or more objects access this column.

Thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-17 : 10:54:03

then you need to first drop the constraint using ALTER TABLE DROP CONSTRAINT

and then alter column and finally create it back using ALTER TABLE ADD CONSTRAINT

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-17 : 10:55:50

quote:
Originally posted by arkiboys

This field is one of the Primary keys.
Why is this method better than making the change in the designer?

ALTER TABLE myTable ALTER COLUMN Code char(4) NOT NULL

After running the sql above it gives the error message:
Msg 5074, Level 16, State 1, Line 1
The object 'PK_myTable' is dependent on column 'Code'.
Msg 4922, Level 16, State 9, Line 1
ALTER TABLE ALTER COLUMN Code failed because one or more objects access this column.

Thanks

actually whatever you're doing its implementing it through code itself on backend but problem with ui is it will hang if table is large

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

arkiboys
Master Smack Fu Yak Hacker

1433 Posts

Posted - 2011-10-17 : 11:01:13

I think I have to drop the pk, change the datatype and then place the pk back in.
?
Thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-17 : 11:07:39

quote:
Originally posted by arkiboys

I think I have to drop the pk, change the datatype and then place the pk back in.
?
Thanks

yep

it will be like


ALTER TABLE <tablename> DROP CONSTRAINT <pkconstraintname>

ALTER TABLE <tablename> ALTER COLUMN <columnname> <datatype> <nullability>

ALTER TABLE <tablename> ADD CONSTRAINT <pkconstraintname> ....

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

arkiboys
Master Smack Fu Yak Hacker

1433 Posts

Posted - 2011-10-17 : 11:41:28

Thank you

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-17 : 12:38:37

wc

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources