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:
CREATE TABLE [dbo].[xtTest]( [id] [int] NOT NULL, [name] [varchar](50) NOT NULL, [city] [varchar](400) NOT NULL, [description] [varchar](500) NOT NULL ) ON [PRIMARY]
Now just create an index with below script:
create index IX_xtTest on xtTest(name,city,description)
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:
insert into xtTest values(1,'prince','gurgaon','gurgaon is in haryana state')
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
insert into xtTest values(2,'kamal','insert any string containing 400 characters','insert any string containing 500 characters')
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:
create index IX_xtTest on xtTest(name,city) include(description)
Here index key columns are only name and city.