Is there a fast way to change column type without dropping the clustered index?

Question

I have a large table that is clustered index on a bigint. We would like to change it to just an int to reduce the space and improve the performance. However, dropping the clustered index and recreate the index is extremely slow.

Is there a way to speed this process, or by design this is the only to change the data type?

[Update] Just want to elaborate a bit about my question, the column that I like to update is a date dimension key. It is currently a bigint (8 bytes), and I would like to convert it to int (4 bytes). This should reduce the size of the database, and it should theoretically improve the database performance in general.

Answer 1

To change datatype, you have to drop and recreate the Index as below (alternatively you can use SSMS, which does similar thing behind the scenes) :

1. drop any foreign keys in other tables that references this index.
2. drop the index
3. change the column datatype
4. rebuild the index
5. Put back any foreign keys that you dropped in step 1.

Alternatively, you can wrap below in a transaction with TABLE_LOCK so that no one can insert/update/delete while the change is occurring --

1. Create another table with Name_staging and columns with correct datatype
2. create Indexes, FK, etc
3. Insert the data into the Name_staging table
4. Rename the Name_staging to Original table.

Note: Its best to perform above task in maintenance window.

Answer 2

If you have Enterprise Edition, are on 2008 or greater (always useful to specify version), and most or all of the values in this column are less than the upper bound of INT, you can get all the space back and probably quite a bit more by applying data compression.

My guess is you can improve I/O by at least as much, probably more depending on the nature of the rest of the data (and your workload), but without having to make the change to the column directly (and hence all of the constraints that apply to it, foreign key columns in other tables, etc).

Something that's certainly worth testing, especially since it's so much less complicated than changing the data type.

Of course, you should establish first whether I/O is even your bottleneck, and whether stripping 4 bytes from this particular column is likely to help you much at all. On its own, I'd have to guess: no.