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

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.

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.

StackExchange Database Administrators Q#42373, answer score: 3