Click here to Skip to main content
15,566,394 members
Please Sign up or sign in to vote.
1.00/5 (1 vote)
See more:
I work on sql server 2014

I have table when create index on column Estrat
but it give me error
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

What I have tried:

SQL
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);
Posted
Updated 25-Nov-21 8:58am

1 solution

Read the error message:
Quote:
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.

The column you are indexing is larger than the maximum length.

We can't fix that: either don't index that column, or reduce the size of the column.
 
Share this answer
 
Comments
ahmed_sa 25-Nov-21 14:06pm    
so reduce to what
now it is nvarchar(2500)
so make it as what
how much
phil.o 25-Nov-21 14:21pm    
"The maximum key length for a nonclustered index is 1700 bytes."
This should be a huge tip.
OriginalGriff 25-Nov-21 14:31pm    
You'd have thought so, wouldn't you? :laugh:
ahmed_sa 25-Nov-21 14:26pm    
imake select MAX(LEN(EStrat)) from [dbo].[TGen]
it give 2252
ahmed_sa 25-Nov-21 14:27pm    
so how to reduce it
for what i will reduce

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900