0

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?

slang
  • 626
  • 7
  • 26
Daniel
  • 8,133
  • 5
  • 36
  • 51

2 Answers2

3

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.

Remus Rusanu
  • 288,378
  • 40
  • 442
  • 569
  • Thanks, yes I was thinking of index fragmentation. So if I delete row 50000 and take the sequential guid value and insert a new row with this Id at position 100001 the indexes aren't messed up and you don't lose performance that you should get out of sequential guids? – Daniel May 01 '11 at 23:24
  • Rows don't have 'positions', they have keys. If you delete a row with a key and then insert back a row with the same key then you *may* cause fragmentation if the insert causes a page split (the new row does not fit in the room left behind by the deleted row). There are many more factors at play: transaction boundaries, row versioning, other transactions activities. The type of the key is completely irrelevant for this scenario, a `uniqueidentifier` and an `int` will behave identically from this point of view. – Remus Rusanu May 02 '11 at 00:46
0

You may find Paul Randal's post useful.

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

Pavel Nefyodov
  • 876
  • 2
  • 11
  • 29