error The index entry of length 1890 bytes for the index 'IDX_EStrat' exceeds the maximum length of 1700 bytes for nonclustered indexes

ahmed salah 3,131 Reputation points
2021-11-25T18:43:15.923+00:00

i have table create index on it
but it give me error

so how to solve it please

CREATE TABLE [dbo].[TGen](
    [TradeCodeControlID] [int] IDENTITY(1,1) NOT NULL,
    [ZfeatureType] [nvarchar](200) NULL,
    [EStrat] [nvarchar](2500) NULL,
    [EEnd] [nvarchar](2500) NULL
) ON [PRIMARY]

GO

CREATE NONCLUSTERED INDEX IDX_EStrat ON ExtractReports.dbo.TGen(EStrat);

Warning! The maximum key length for a nonclustered index is 1700 bytes. The index 'IDX_EStrat' has maximum length of 5000 bytes. For some combination of large values, the insert/update operation will fail.
Msg 1946, Level 16, State 3, Procedure dbo.SP_TradeCodeGenerateByProduct, Line 403
Operation failed. The index entry of length 1890 bytes for the index 'IDX_EStrat' exceeds the maximum length of 1700 bytes for nonclustered indexes.

so how to solve error above please
and why this error display

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
8,578 questions
{count} votes

Accepted answer
  1. Guoxiong 7,681 Reputation points
    2021-11-25T21:26:14.47+00:00

    Based on the Microsoft documentation, the maximum allowable size of the combined index values is 1700 for a nonclustered index. Since the EStrat column has the maximum 2500 characters, you cannot add the index on it. I would suggest to drop that index. You can add a CHECKSUM column and then build a hash index on it, which can help improve indexing speed when the column is a long character column:

    USE [ExtractReports];
    GO
    
    ALTER TABLE [dbo].[TGen]
    ADD CS_EStrat AS CHECKSUM([EStrat]);
    GO
    CREATE INDEX [IX_TGen_CS_EStrat] ON [dbo].[TGen] ([CS_EStrat]);
    GO
    

0 additional answers

Sort by: Most helpful