About Me

My photo
Mumbai, Maharastra, India
He has more than 7.6 years of experience in the software development. He has spent most of the times in web/desktop application development. He has sound knowledge in various database concepts. You can reach him at viki.keshari@gmail.com https://www.linkedin.com/in/vikrammahapatra/ https://twitter.com/VikramMahapatra http://www.facebook.com/viki.keshari

Search This Blog

Tuesday, November 29, 2016

Max Key length warning in SQL Server 2016 for NC Index

There are certain times when your index gets created with warning message

Warning! The maximum key length for a nonclustered index is 1700 bytes. The index 'test_index' has maximum length of 1705 bytes. For some combination of large values, the insert/update operation will fail.

Remember SQL Server 2016 increase maximum key size for Non Cluster Index from 900 byte to 1700 byte. That seems interesting correct!  You can now have more key column in your NC index Wow!!

Well it is not so wow! The larger index comes with lot of cons, which you need to consider before designing larger NC index, I will write a separate note on this.

Let reciprocate the above error to understand more on it.

CREATE SCHEMA indexDemo GO

CREATE TABLE indexDemo.IndexByteSQL2016 ( MyCol1 varchar(1705) );

Here we created table with a single column of length 1705, just do remember SQL Server 2016 allow only 1700 byte key column length.  


Now lets create NC index
CREATE INDEX test_index ON indexDemo.IndexByteSQL2016 (MyCol1);

Warning! The maximum key length for a nonclustered index is 1700 bytes. The index 'test_index' has maximum length of 1705 bytes. For some combination of large values, the insert/update operation will fail.

NC index created with warning, this warning we cannot ignore since it will affect your insertion. Let try to fire some insert statement to test this warning

INSERT INTO indexDemo.IndexByteSQL2016
SELECT cast(replicate('a', 700) AS varchar(1000)); -- Success
(1 row(s) affected)

INSERT INTO indexDemo.IndexByteSQL2016
SELECT cast(replicate('b', 901) AS varchar(1000)); -- Success
(1 row(s) affected)

INSERT INTO indexDemo.IndexByteSQL2016
SELECT cast(replicate('c', 1703) AS varchar(2000)); -- Fail
Msg 1946, Level 16, State 3, Line 20
Operation failed. The index entry of length 1703 bytes for the index 'test_index' exceeds the maximum length of 1700 bytes for nonclustered indexes.


The above insertion fails, why? Though you are trying to insert record of permissible length defined by your datatype i.e. varchar(1705) but this column is participating in NC index key creation and in SQL Server 2016 the permissible length of NC IndexKey columns is 1700.

So this particular value cast(replicate('c', 1703) AS varchar(2000) won’t get space in index creation thus insertion fails


Enjy coding…SQL J

Post Reference: Vikram Aristocratic Elfin Shar