Hi Friends,

Today, I just want to focus on the warning “Warning The maximum key length is 900 bytes……”

Actually, sometimes we create index having variable key length greater than 900 bytes.At the time of creating such indexes sql server gives us warning. We should consider such type of warning seriously. Let me explain this with an example:

Create a table with the below script:

Now just create an index with below script:

but at the time of creating such index SQL Server gives us warming:

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

Here we face warning because data type of key column is variable length type.Now we just want to insert the data in to xtTest table:

above query runs succesfully and insert one row in to xtTest table, because total length of data for index key column is less than 900 bytes. Suppose after many days we want insert a row which is having index key length greater than 900 bytes.What will happen when we insert key data of length maximum than 900 bytes. Insert the another row with below mention query,but first replace 400 and 500 character length strings

when we run the above query,Error comes

Operation failed. The index entry of length 905 bytes for the index ‘IX_xtTest’ exceeds the maximum length of 900 bytes.

This error comes, because we ignore the warning which come earlier at the time of index IX_xtTest creation. So my purpose is here “Never ignore such type of warnings”.

At the time of index creation always consider the fact “index key columns length should not exceed 900 bytes”.

If you realy want to add such data type of columns in index then use include option. That means you can create above index as shown below:

Here index key columns are only name and city.

 

Regards

Prince Rastogi

Like us on FaceBook Follow us on Twitter | Join the fastest growing SQL Server group on FaceBook

Follow me on TwitterFollow me on FaceBook