Sql-server – fast way to change column type without dropping the clustered index

alter-tableddlperformancesql server

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.

Best Answer

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.

Related Question