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.
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!