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 PAGE command (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