What happen when changing column type for a table with clustered index? I have a very large table (200M+ row), and it is clustered indexed. I would like to change one of the foreign key column from a bigint to smallint.

My questions are:


Does SQL need to rebuild the clustered index?
What if this table is a heap and not a clustered index?
1: If the column you're changing is part of the clustered index definition, then yes. If not, no. Any non-clustered indexes involving that column will have to be rebuilt when you change the column type, also.

2: A heap just means no clustered index, so nothing to rebuild. Same as answer #1 for non-clustered indexes.