What happen when changing column type for a table with clustered index?

Question

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:

1. Does SQL need to rebuild the clustered index?

2. What if this table is a heap and not a clustered index?

Answer 1

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.

Answer 2

If your table is a heap, you will get a lot of key lookups, which are performance wise not good.

If you have so much records in the database, you also might wanna take a look at partitioning your data. Spread it over multiple files and over multiple disks/raid configurations will speed up things very much!