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.




Related Articles :

  • No Related Articles Found

No comments yet... Be the first to leave a reply!

Software Reviews | Book Reviews | FAQs | Tips | Articles | Performance Tuning | Audit | BI | Clustering | Developer | Reporting | DBA | ASP.NET Ado | Views tips | | Developer FAQs | Replication Tips | OS Tips | Misc Tips | Index Tuning Tips | Hints Tips | High Availability Tips | Hardware Tips | ETL Tips | Components Tips | Configuration Tips | App Dev Tips | OLAP Tips | Admin Tips | Software Reviews | Error | Clustering FAQs | Performance Tuning FAQs | DBA FAQs |