I have an existing table with millions of rows in it where I have a column which is the SMALLMONEY data type and I want to ALTER it to be MONEY. To make things a bit more complicated it has an index on it which which INCLUDE (s) that column so I need to remove that prior to the ALTER and then add it in again after the ALTER.
I have been running through my test deployment and each of these operations (index removal, table alter and index addition) takes about 4 – 6 minutes each.
I really need to come up with some way to do this faster.
This table is constantly being inserted into and so the shorter the duration the better.
Any pointers would be appreciated.
I am using SQL Server 2008 Enterprise.
Best Answer
You're using Enterprise edition, so you should be able to use online indexing. Add the
WITH ONLINE = ONoption to your index DDL statement. The operation will still take just as long (possibly longer), but SQL Server will do some magic to keep the table accessible throughout. Here's some documentation with assorted restrictions, but they mostly only come into play if you've got LOB columns in the table:http://msdn.microsoft.com/en-us/library/ms190981.aspx
This won't help you with altering the column data type, of course. If it's constantly being inserted, but not necessarily updated or read (and you can live with such operations being unavailable during the transition), you can rename the original table, create a new one with the desired structure and permissions, move all the data over, then recreate the indexes on the new table. This may be slightly more complicated if you have an identity column and want to preserve values, but it's still doable.