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:
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.
Regards
Prince Rastogi
Like us on FaceBook | Follow us on Twitter | Join the fastest growing SQL Server group on FaceBook
Follow me on Twitter | Follow me on FaceBook
Perhaps, if someone from the team can write more on covering index. That would be just great to understand this content in greater detail.
Hi Raunak,
Actually here i just want to explain the warning, in my next blog i will explain Covering Indexes.
CREATE TABLE [dbo].[Cities]
(
[CityId] [int] IDENTITY,
[CityName] [varchar](400)
)
CREATE TABLE [dbo].[xtTest]
(
[id] [int] NOT NULL,
[name] [varchar](50) NOT NULL,
[fkCityId] [int] NOT NULL,
[description] [varchar](500) NOT NULL,
CONSTRAINT [FK_dbo_xtTest___fkCityId] FOREIGN KEY ([fkCityId]) REFERENCES [dbo].[Cities]([CityId])
CREATE VIEW [dbo].[vxtText]
SELECT
[xt].[id],
[xt].[name],
[c].[CityName] as [City],
[xt].[description]
FROM
[dbo].[xtTest] [xt] INNER JOIN [dbo].[Cities] [c] ON [c].[CityId] = [xt].[fkCityId]
How about teaching basic normalization?
By breaking out City into a separate table, you gain the following benefits:
1.Data Quality. If a city doesn’t exist in [dbo].[Cities], it can’t be referenced in [dbo].[xtTest]. This way you aren’t storing typos and mistakes and wind up with several misspellings of the same city.
2.Storage Efficiency. A city name takes up space exactly once. After that, each reference to the City takes up the space required to store an [int] (4 bytes)
3.Index efficience. Any time a column refering to a city is referenced, the index entry takes up 4 bytes, not the length of the name of the city
4.Processing Efficiency. An [int] is a native processor type, it doesn’t require any pre-processing to be evaluated. String types must first be cast to a byte array of integers; something that happens fairly quickly, but not as fast as a processor evaluating an integer directly.
You need to do just a few more things to make this as fast as a native table:
Create an index on the view. That gives the view its own clustered index. All values are read from the clustered index, just like a native table.
Create an INSTEAD OF trigger on the view that redirects INSERT, UPDATE and DELETE statements on the view. This way the view looks and acts exactly the same as the table you originally described. This is easy to generate directly out of system tables like sys.foreign_keys and sys.foreign_key_columns.
The warning is exactly that… a warning that you have likely done something wrong. In this case, it was the table design, which led to the requirement of an index which exceeds SQL Server’s ability to handle.
When you cover INCLUDES be sure to cover the storage impact of having a table filled with varchar columns and an index that includes all of them. While the INCLUDEd columns are not counted towards the 900 byte limit, the INCLUDEd columns are still included in the index storage… which means you are storing the values twice. You’ve now doubled your storage requirement.
Hi ,
I am not getting any error.
CREATE TABLE #xtTest (
[id] [int] NOT NULL,
[name] [varchar](5000) NOT NULL,
[city] [varchar](5000) NOT NULL,
[description] [varchar](500) NOT NULL
)
create index IX_xtTest on #xtTest(name,city,description)
insert into #xtTest values(2,’kamal’,’insert any string containing 400 charactersinsert any string containing 400 charactersinsert any string containing 400 charactersinsert any string containing 400 charactersinsert any string containing 400 charactersinsert any string containing 400 charactersinsert any string containing 400 charactersinsert any string containing 400 charactersinsert any string containing 400 charactersinsert any string containing 400 charactersinsert any string containing 400 characters’,’insert any string containing 500 characters’)
Hi Srikant,
try this..
CREATE TABLE #xtTest (
[id] [int] NOT NULL,
[name] [varchar](5000) NOT NULL,
[city] [varchar](5000) NOT NULL,
[description] [varchar](500) NOT NULL
)
create index IX_xtTest on #xtTest(name,city,description)
–Here you will got a warning as mention below
–Warning! The maximum key length is 900 bytes. The index ‘IX_xtTest’ has maximum length of 10500 bytes. For some combination of large values, the insert/update operation will fail.
–TRY to insert values more than 900 bytes data length
–(a)- Less than 900 bytes = success
insert into #xtTest values(2,’kamal’,Replicate(’10characterSampledat’,19),Replicate(’10characterSampledat’,25))
–(b)- more than 900 bytes = failure wit below message
insert into #xtTest values(2,’kamal’,Replicate(’10characterSampledat’,20),Replicate(’10characterSampledat’,25))
–Msg 1946, Level 16, State 3, Line 2
–Operation failed. The index entry of length 905 bytes for the index ‘IX_xtTest’ exceeds the maximum length of 900 bytes.
Thanks Prince.Now it is giving error.
But I was not getting error when I was executing my script as posted .why?
Hi Srikant,
IN Your case issue was the length of DATA. Which was not > 900 bytes
Select LEN(‘insert any string containing 400 charactersinsert any string containing 400 charactersinsert any string containing 400 charactersinsert any string containing 400 charactersinsert any string containing 400 charactersinsert any string containing 400 charactersinsert any string containing 400 charactersinsert any string containing 400 charactersinsert any string containing 400 charactersinsert any string containing 400 charactersinsert any string containing 400 characters’)
–Output: 473
Select LEN(‘insert any string containing 500 characters’)
–Output: 43