Do primary keys change when indexes are rebuilt or reorganized?

Question

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.

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, 2018 at 15:36
@JonathanFite perhaps you should add this as an answer rather than a comment? — George.Palacios, Dec 3, 2018 at 15:46

George.Palacios · Accepted Answer · 2018-12-03 15:42:25Z

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.

answered Dec 3, 2018 at 15:42

George.Palacios

5,56021 silver badges48 bronze badges

Add a comment |

1 Answer 1