SQL Server Identities and Sequential GUIDS - Will reuse of id value mess up page-files

Question

If I'm using either identities or sequential guids and have them as primary keys and have e.g 100 000 rows stored and then delete one of the rows and insert a new row with the same Id value as the row deleted, then I guess I will mess up the "performance" by not getting keys ordered correctly in the page-files. Is this correct? If, is there a way to "refresh". e.g. by running DBCC DBREINDEX and/or UPDATE STATISTICS?

Answer 1

No. Keys are always kept in order and no operation can mess the key order.

What you probably heard of is index fragmentation, the process of divergence between the logical order and the physical order of an index. Certain patterns of operations lead to higher fragmentation, but deleting a key and inserting back the same row with same key is not such a pattern. Fragmentation can be eliminated through index reorganization (ALTER INDEX ... REORGANIZE) or index rebuild (ALTER INDEX ... REBUILD), see Reorganizing and Rebuilding Indexes. As a rule of thumb, everything you read about how bad index fragmentation is is greatly exaggerated.

Updating statistics has nothing to do with key order nor with fragmentation. Outdated statistics have other causes and lead to different problems (bad cardinality estimates), see Using Statistics to Improve Query Performance.

Answer 2

You may find Paul Randal's post useful.

It's not really clear what you mean by "page-files" though.