Indexes on VARCHAR 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.)

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.

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:

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

We have broken our INSERT statement!

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.

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

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 😉

Like or share to get the source code.

Thanks for your time,


It`s your turn

Your email address will not be published. Required fields are marked *


Copyright © 2015 by SQLpassion · Klaus Aschenbrenner · Imprint · Offerings · Academy · Contact · Go to Top