Sql-server – What happen when changing column type for a table with clustered index

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.

The clustered index "is" the table. Rebuilding the index will require enough space to duplicate each existing non-deleted row, page-by-page. Since you don't have Enterprise Edition, you do not have the luxury of rebuilding the index online. This means the table will not be accessible during the rebuild operation.

You'd likely be better off rebuilding by creating a duplicate empty table on a different drive that has plenty of free space with room for foreseeable growth, moving data row-by-row in key order, then renaming the old and new tables. For instance if the original table is Table1 and the new copy is Table2, rename Table1 as Table1-old then rename Table2 as Table1.