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

Question

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

Answer 1

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