Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
2  
index removal ... takes 4-6 minutes. Non clustered index removal is basically instantaneous. The fact that it takes 4-6 minutes indicates that it has to wait 4-6 until it acquires the necessary locks, which in turn implies that you have reads lingering for 4-6 minutes, blocking the DDL. Which questions whether not everything is blocked by these slow reads. –  Remus Rusanu Dec 11 '12 at 15:03

2 Answers 2

up vote 3 down vote accepted

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.

share|improve this answer

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
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.