Alter Column SmallINT to BigINT

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

SQL Server Administration (2005)

Alter Column SmallINT to BigINT

Author

Topic

andriancruz
Starting Member

38 Posts

Posted - 2009-05-18 : 02:03:12

Dear Expert,

I have a concern regarding altering the column. I have a column ID the properties is SMALLINT data type, NOT NULL and the Identity is set to YES. and this table has a million of records. If I change the data type to BIGINT and use the generate scripts from SQL. I understand that the scripts is creating a temporary table and rename the table once the scripts successfully done. Is there any other way to alter the ID column to BIGINT without any creating any temporary table? I will highly appriciated you advice and suggestion. Thank you in advanced.

More power...

Thank you & best regards,
Andrian

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-05-18 : 03:18:35

Wait a minute...
You have a table with million of records, and use an SMALLINT identity column?
You know smallint only can store values between -32768 and 32767, ie 65536 values?

E 12°55'05.63"
N 56°04'39.26"

andriancruz
Starting Member

38 Posts

Posted - 2009-05-18 : 06:21:32

ops... sorry typo error. I mean hundred thousand of records. and i need to insert more records more than 32768 records. but return me this error.

Msg 50000, Level 16, State 1, Line 14348
Arithmetic overflow error for data type smallint, value = 32768.

I need to change the data type into BIGINT without creating temporary table. Thanks

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-05-18 : 06:26:38

drop constraint on the ID column if there is any

alter table <table name> alter column ID bigint

re-create the constraint on the ID column

KH
[spoiler]Time is always against us[/spoiler]

andriancruz
Starting Member

38 Posts

Posted - 2009-05-18 : 07:58:53

Thanks KH for the reply... I will try your advice and I let you know.

Thanks again and more power...

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources