Is there a way to speed up an ALTER statement in SQL Server

Question

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.

Answer 1

You're using Enterprise edition, so you should be able to use online indexing. Add the WITH ONLINE = ON option 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.

Answer 2

One of the solutions is to create a new table and transfer data. Rename the new table afterwards:

1. Create a DDL script of the old table
2. Change the column type
3. Create a NewTable using the script
4. Transfer all the records from the OldTable into NewTable

insert into NewTable (column list) select * from OldTable

Now the only difference are the records added in the meantime. If the existing records have been updated or deleted in the meantime, you can use a third party tool to synchronize them

1. Create the indexes on NewTable
2. Drop the constraints on the OldTable and create them on NewTable
3. Rename the OldTable to OldTable1 (to be on the safe side)
4. Rename the NewTable to OldTable