Index '%.*ls' was not created. This index has a key length of at least %d bytes. The maximum permissible key length is %d bytes.

Error Message:
Msg 1944, Level 16, State 1, Line 6
Index ‘%.*ls’ was not created. This index has a key length of at least %d bytes. The maximum permissible key length is %d bytes.

Severity level:
16.

Description:
This error message appears when you try to create an index on a set of columns that exceed the maximum allowed key length.

Consequences:
The T-SQL statement can be parsed, but causes the error at runtime.

Resolution:
Error of the Severity level 16 are generated by the user and can be fixed by the SQL Server user. The statement cannot be run this way. You can’t create an index that exceeds the maximum allowed key length. Consider other index strategies like moving columns to the INCLUDE clause (SQL Server 2005 and above).

Versions:
All versions of SQL Server.

Example(s):
IF OBJECT_ID(‘dbo.t’) IS NOT NULL
    DROP TABLE dbo.t ;
GO
CREATE TABLE dbo.T
(
    c1 char(1000)
);

CREATE INDEX ix_t_c1 ON dbo.t (c1)

DROP TABLE dbo.t;

Remarks:
In the above example we try to create an index on a char(1000) column. The the columns length exceeds the maximum allowed key length, the error is raised.

]]>

Leave a comment

Your email address will not be published.