How SQL Server stores indexes on variable length columns

(Be sure to checkout the FREE SQLpassion Performance Tuning Training Plan - you get a weekly email packed with all the essential knowledge you need to know about performance tuning on SQL Server.)

In the last months I had done a lot of performance tuning workshops, and there is almost the same question: How SQL Server stores indexes on variable length columns? Therefore I thought this would be a great topic for a weblog posting. To examine the concrete storage details of an index defined on a variable length column, I have created the following table and populated it with 80.000 records:

As you can see I have created the PRIMARY KEY constraint on a VARCHAR(255) column, which is enforced by a UNIQUE CLUSTERED INDEX by SQL Server. In the next step I have retrieved some physical information about the created clustered index by using the DMF sys.dm_db_index_physical_stats:

When you look into the output, you can see that the column min_record_size_in_bytes show you a value of 7 and the column max_record_size_in_bytes shown you the value 28 in the index page. This leads us to the conclusion that the clustered keys are stored as variable length columns inside an index record. So let’s examine an index record on an index page. I’ve used the DBCC IND command to retrieve all pages for our clustered index and stored that output in a little helper table.

In my case SQL Server stored the index root page on the page id 458, which I have dumped out through the DBCC PAGE command (after enabling the trace-flag 3604 to get the DBCC PAGE output):

Each slot in the output represents one index record, like:

Let’s examine those hex values.

  • 26: The first byte represents status bits.
  • 95020000: The next 4 bytes are the child-page-id to which this index record points
  • 0100: The next 2 bytes are the child-file-id to which this index record points
  • 0100: The next 2 bytes are the number of variable length columns
  • 1b00: For each variable length column SQL Server stores a 2 byte entry which points to the offset at which the variable length column ends – it’s the same as when you store variable length columns on a regular data page. In this case we have one variable length column, therefore SQL Server has to store one 2 byte offset – byte offset 27 in this case. This means that the next bytes until the byte offset 27 are part of our variable length column – the clustered key.
  • 43757374 6f6d6572 4e616d65 31333533: The hex value for our clustered key – the column CustomerName

As you can see from this explanation SQL Server stores variable length index columns in the same format as regular variable length columns on data pages. But you have to be aware that there is a slight overhead with this, because you need 2 additional bytes for storing the count of variable length columns and for each variable length column you need 2 bytes in the variable column offset array. Keep this in mind when you are designing your indexes and when you calculate how many index records fits onto one index page. You can download the whole script from here and play around with it.

-Klaus

Do you like this post? Why not share it with the SQL Server community?