Monday, April 17, 2017

Things to keep in mind when creating a "CLUSTERED" index in SQL Server

You should use extreme care when picking a clustering key - it should be:

  1. narrow (4 bytes ideal)
  2. unique (it's the "row pointer" after all. If you don't make it unique SQL Server will do it for you in the background, costing you a couple of bytes for each entry times the number of rows and the number of nonclustered indices you have - this can be very costly!)
  3. static (never change - if possible)
  4. ideally ever-increasing so you won't end up with horrible index fragmentation (a GUID is the total opposite of a good clustering key - for that particular reason)
  5. it should be non-nullable and ideally also fixed width - a varchar(250) makes a very poor clustering key

No comments:
Write comments
Recommended Posts × +