2

I have a database containing tables that each have auto-increment int columns serving as the primary key column.

When I do a rebuild or reorganize operation on my indexes, does either operation potentially change the values of this column?

Does it matter if the indexes are 'clustered'?

I'm using various versions of MS T-SQL from 2008 onward.

  • 3
    Index reorganizations or rebuilds will not alter data, only make sure it is laid out on disk in the proper order. The only thing that can change an auto-increment value (other than an insert/update/delete) is a truncate (resets the increment) or an RESEED command. – Jonathan Fite Dec 3 '18 at 15:36
  • 3
    @JonathanFite perhaps you should add this as an answer rather than a comment? – George.Palacios Dec 3 '18 at 15:46
  • @JonathanFite thank you for the information. – Dragonsdoom Dec 3 '18 at 16:40
9

No, they will not.

Rebuilding or reorganizing an index should never alter the underlying data itself, only its placement within database pages / extents.

However, as an aside have a read through this post - you might find that you don't need to rebuild these indexes in the first place.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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