Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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?

share|improve this question
up vote 2 down vote accepted

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.

share|improve this answer

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!

share|improve this answer

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.