How SQL Server stores indexes on variable length columns

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:

-- Create a new table
CREATE TABLE Customers
(
    CustomerName VARCHAR(255) NOT NULL PRIMARY KEY,
    Filler CHAR(138) NOT NULL
)
GO

-- Insert 80.000 records
DECLARE @i INT = 1
WHILE (@i <= 80000)
BEGIN
    INSERT INTO Customers VALUES
    (
        'CustomerName' + CAST(@i AS VARCHAR),
        'Filler' + CAST(@i AS VARCHAR)
    )
   
    SET @i += 1
END
GO

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:

-- Retrieve physical information about the clustered index
SELECT * FROM sys.dm_db_index_physical_stats
(
    DB_ID('VariableClusteredKeyStructure'),
    OBJECT_ID('Customers'),
    NULL,
    NULL,
    'DETAILED'
)
GO

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.

-- Create a helper table
CREATE TABLE HelperTable
(
  PageFID TINYINT,
  PagePID INT,  
  IAMFID TINYINT,
  IAMPID INT,
  ObjectID INT,
  IndexID TINYINT,
  PartitionNumber TINYINT,
  PartitionID BIGINT,
  iam_chain_type VARCHAR(30),   
  PageType TINYINT,
  IndexLevel TINYINT,
  NextPageFID TINYINT,
  NextPagePID INT,
  PrevPageFID INT,
  PrevPagePID INT,
  PRIMARY KEY (PageFID, PagePID)
)
GO

-- Write everything in a table for further analysis
INSERT INTO HelperTable EXEC('DBCC IND(VariableClusteredKeyStructure, Customers, 1)')
GO

-- Retrieve the root index page (1 page)
SELECT * FROM HelperTable
WHERE IndexLevel = 2
GO

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

DBCC TRACEON (3604)
GO

-- Dump out the root index page
DBCC PAGE(VariableClusteredKeyStructure, 1, 458, 1)
GO

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

26 95020000 0100 0100 1b00 43757374 6f6d6572 4e616d65 31333533

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

Posted in: SQL