Indexes on VARCHAR Columns

Years ago I wrote about how SQL Server stores VARCHAR columns in the Navigation Structure of an index. As you might know, the key columns of an index in SQL Server (Clustered Index, Non-Clustered Index) have a size limitation of a maximum of 900 bytes.

Imagine now that you want to fool SQL Server, and create an index on a VARCHAR(8000) column and insert more than 900 bytes in the index key column. How do you think SQL Server reacts in this situation? No idea? So let’s try it out. In the following listing I create a simple table with a VARCHAR(8000) column, and I create a Non-Clustered Index on that column.

-- Create a simple table
CREATE TABLE Foo
(
	Bar VARCHAR(8000)
)
GO

-- Create a simple Non-Clustered Index
CREATE NONCLUSTERED INDEX idx_Bar ON Foo(Bar)
GO

SQL Server gives you here already a warning that INSERT/UPDATE operations might fail…

Warning! The maximum key length is 900 bytes. The index ‘idx_Bar’ has maximum length of 8000 bytes. For some combination of large values, the insert/update operation will fail.

And now the nasty thing happens: I insert a record with a column size of 901 bytes:

-- Insert a too large index key column...
INSERT INTO Foo VALUES(REPLICATE('x', 901))
GO

Ouch – that INSERT will now not happen as you can see:

SQL Server still makes sure that our key size is within the limits of 900 bytes. So let’s insert a record with a key size of exactly 900 bytes.

-- Insert a too large index key column...
INSERT INTO Foo VALUES(REPLICATE('x', 900))
GO

This will now work without any problems. But what happens if we update that column again and make it larger than 900 bytes?

-- This UPDATE will fail
UPDATE Foo SET BAR = REPLICATE('x', 901)
GO

The UPDATE statement will also fail! That makes sense. SQL Server again has to make sure that the key size remains within its limits. The moral of the story? It doesn’t matter how you try, you can’t break SQL Server 😉

Thanks for your time,

-Klaus