Do Clustered Index Columns Count Against Max Nonclustered Key Size?

SQL Server 2016 and Azure SQL Database have a lot of little perks. One little thing that changed is that the maximum key size allowed for nonclustered indexes was raised from 900 bytes to 1700 bytes.

Whoa! That’s room for a lot more key columns in there. Bring on the SSDs! Load up the memory! THE KEY COLUMNS  COMETH!

Well, maybe not so fast. Remember that larger nonclustered indexes not only use up more memory and storage, but also…

• They increase backup time and resources
• More indexes means more index maintenance
• You gotta check ‘em all for corruption (not quite like Pokemon, but close enough)
• They require more space everywhere they are restored

So make sure you really need all that junk in your nonclustered index trunk. Er, key.

But even with the expanded size of key columns, sometimes I get asked a question: do columns that “secretly” get added to the key of a nonclustered index count against the maximum allowed nonclustered index key length?

Spoiler, for those of you short on time: Key columns explicitly stated in your index definition are restricted to 900 or 1700 bytes (depending on SQL Server version as mentioned above). Clustered index key columns secretly added to the nonclustered index definition get an extra 900 bytes to themselves.

Background: SQL Server often sneaks the key of a table’s clustered index into the key of each nonclustered index

Behind the scenes, SQL Server needs a unique way to identify every row in an index - and it also needs a way to find the exact same row in other indexes on the table.

So let’s say you’ve got a nonclustered index that doesn’t enforce uniqueness: it’s just used to make reads faster.

To make everything unique…

The question is, when CX columns are snuck into the key, does it count against the max row size?

They do NOT count. It’s easy to prove, let’s take a look.

Proving that “secret” key columns don’t count

Just for fun, we’re going to prove that those index pants got more stretchy at the same time on SQL Server 2016.

First, we create a table with three columns. Just to keep it from being lonely, we give it three rows.

CREATE TABLE dbo.KeySizeTest (
    FourBytes INT IDENTITY NOT NULL,
    AVeryWideColumn CHAR(1696) NOT NULL,
    ASmallColumn CHAR(4) NOT NULL default ('Wee')
);
GO

INSERT dbo.KeySizeTest (AVeryWideColumn)
    VALUES ('meep'), ('mope'), ('moop');
GO

The maximum column length for all the columns in this table is 1703– but let’s test what we can get away with.

First, create our four byte unique clustered index:

CREATE UNIQUE CLUSTERED INDEX cx_KeySizeTest on dbo.KeySizeTest (FourBytes);
GO

Now to test out that big key size! This two column index comes to 1700 per row:

CREATE NONCLUSTERED INDEX nc_KeySizeTest_AVeryWideColumn_ASmallColumn 
    on dbo.KeySizeTest (AVeryWideColumn, ASmallColumn);
GO

And that works!

What if we try to explicitly add the clustered index key to the end of the index (where SQL Server is going to put it, anyway– cause this baby isn’t unique)?

CREATE NONCLUSTERED INDEX nc_KeySizeTest_AVeryWideColumn_ASmallColumn_FourBytes
    on dbo.KeySizeTest (AVeryWideColumn, ASmallColumn, FourBytes);
GO

Ooooooo, that fails.

Msg 1944, Level 16, State 1: nonclustered index key size

In failing, it gives us more info:

Msg 1944, Level 16, State 1, Line 32 Index ‘nc_KeySizeTest_AVeryWideColumn_ASmallColumn’ was not created because the index key size is at least 1704 bytes. The nonclustered index key size cannot exceed 1700 bytes. If the index key includes implicit key columns, the index key size cannot exceed 2600 bytes.

 “Implicit” or “Secret” Key Columns get 900 bytes of their own

This makes sense, because the size limit of those columns is 900 bytes.

But … wouldn’t it also make sense for SQL Server to have recognized that FourBytes was going to be implicitly added to that index, anyway?

Well, yes, that would be nice.

But Kendra, you didn’t really prove that the clustered index key was implicitly added

It’s true, the proof wasn’t entirely in that error message. We can use a bit of code to prove that the REAL key of the index named nc_KeySizeTest_AVeryWideColumn_ASmallColumn contains the column FourBytes, which we didn’t explicitly ask for.

First, use the undocumented dynamic management view sys.dm_db_database_page_allocations to find the page number of an allocated index page:

SELECT allocated_page_page_id, *
FROM sys.dm_db_database_page_allocations (DB_ID(), OBJECT_ID('KeySizeTest'),NULL, NULL, 'detailed') as pa
JOIN sys.indexes AS si on pa.object_id=si.object_id and
    pa.index_id= si.index_id
WHERE si.name='nc_KeySizeTest_AVeryWideColumn_ASmallColumn'
    and pa.is_allocated=1
    and pa.is_iam_page=0;
GO

In my case this landed at page 166584.

I plug that page number into another undocumented command to return a bird’s eye system view of that data page, using this code:

/* This trace flag tells SQL Server to return the results of DBCC PAGE to your session window */
DBCC TRACEON (3604);
GO
/* This looks at the index page. Swap in the page number for your database */
          /* Database name, Data file number, Page Number, Page dump style */
DBCC PAGE ('SQLIndexWorkbook', 1, 166584, 3);
GO

Here’s what the page really looks like:

There are three key columns– and FourBytes is in there at the end of the key, even though we didn’t ask for it.

Takeaways on max key length and things to remember

We’ve gone a long way around the block. Here’s a little wrap-up:

• Just because you have more rooms in your key columns doesn’t mean you should fill it: remember impact to modifications, storage, memory, and maintenance
• Maximum key length for clustered indexes (B-Tree, Rowstore) is 900 bytes
• Maximum key length for nonclustered indexes in SQL Server 2016 and Azure SQL Database is 1700 bytes, and that’s only for the columns you explicitly define in the index
• Maximum key length for nonclustered indexes in prior versions of SQL Server is 900 bytes, and that’s only for the columns you explicitly define in the index

It’s not too late to sign up for a full day of index training from me in Seattle this fall!