Having indexes on character columns is fairly common in OLTP systems that deal with multiple look-up values. However, indexes on character columns come with a small catch.

The maximum size of an index is 900 bytes. With character data forming part of the index columns, 900 bytes can fill up very fast depending upon the overall size of the character columns involved. While an index on the character columns can still be created if the existing data in the columns does not exceed 900 bytes, the system may reject an insert/update because the total size of the columns being inserted/updated exceed 900 bytes.

While I have never actually experienced this situation, I have seen SQL Server warning about index length exceeding 900 bytes. This prompted me to do a small test, which I am sharing via this post.

The scenario

To simulate the scenario, I will create a test table (for representational purposes) with a couple of columns which will be used on an index.

USE tempdb ;
GO

--(C) SQLTwins, nakulvachhrajani.com

--Safety Check
IF OBJECT_ID('dbo.IndexLengthTest', 'U') IS NOT NULL 
    DROP TABLE dbo.IndexLengthTest ;
GO

--Create the test table
--NOTE: For representational purposes only!
CREATE TABLE dbo.IndexLengthTest
    (
      KeyValue1 NVARCHAR(250),
      KeyValue2 NVARCHAR(250),
      KeyValue3 NVARCHAR(250),
      KeyValue4 UNIQUEIDENTIFIER
    )
GO

--Create the test Index
--NOTE: For representational purposes only!
CREATE UNIQUE NONCLUSTERED INDEX uncidx_IndexLengthTest 
    ON dbo.IndexLengthTest ( KeyValue1, KeyValue2, KeyValue3, KeyValue4 ) ;
GO

NOTE that when the index is created, the following warning is seen in the “Messages” tab of the SQL Server Management Studio (SSMS).

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

The warning clearly indicates that  the maximum possible size of the key in the proposed index is 1516 byes [(2*250)*3 + 16 = 1516 bytes]. Because the existing data in the table is less than 900 bytes, SQL Server will go ahead and create an index on the selected columns.

The Test

I will now  attempt to insert a record in the table whose total size exceeds 900 bytes.

INSERT  INTO dbo.IndexLengthTest
        (
          KeyValue1,
          KeyValue2,
          KeyValue3,
          KeyValue4
        )
VALUES  (
          REPLICATE(N'a', 250),
          REPLICATE(N'b', 250),
          REPLICATE(N'c', 250),
          NEWID()
        ) ;
GO

SQL Server immediately returns the following error:

Msg 1946, Level 16, State 3, Line 1
Operation failed. The index entry of length 1516 bytes for the index ‘uncidx_IndexLengthTest’ exceeds the maximum length of 900 bytes.

To summarize, SQL Server prevents the user from inserting records that violate the index key length which is why caution needs to be exercised when designing indexes on character columns.

Further Reading

• CREATE INDEX [Books On Line Link]

Until we meet next time,

Be courteous. Drive responsibly.