CODEWITHSUNDEEP
sqlsql-server
user94967
user94967

Reputation: 91

MS SQL Server fastest way of changing column definition

I am tasked with changing a table's column datatype from smallint to int for a table that has 32 million rows in it. The requirement is to have the lowest downtime possible (basically the fastest way of achieving this). We are using SQL Server 2000.

Please help!

Upvotes: 1

Views: 590

Answers (2)

HLGEM
HLGEM

Reputation: 96570

Whatever you do, do not use Enterprise Manager to change the datatype. What it will do is create a whole new table, populate it, rename the old table, rename the new table to the old table's name and drop it all in one nasty transaction locking the table for hours.

Upvotes: 2

Cade Roux
Cade Roux

Reputation: 89671

There are a couple options, you could make an online copy of the table and then use sp_rename to switch it out.

Another way is to add a new column, populate it, then drop the old column and rename the new column.

32m rows could be considered big or small depending on the width of the row.

Upvotes: 4

Related Questions