Error the index entry of length 1890 bytes for the index 'IDX_estrat' exceeds the maximum length of 1700 bytes for nonclustered indexes

Question

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 25-Nov-21 7:46am

ahmed_sa

Updated 25-Nov-21 7:58am

Add a Solution

1 solution

Add a Solution

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

OriginalGriff · Accepted Answer · 2021-11-25T07:58:00

Solution 1

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.

Posted 25-Nov-21 7:58am

OriginalGriff

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

phil.o 25-Nov-21 14:29pm

If you want an index on that column, you cannot have more than 1700 bytes in a single row. So it seems that, as it is, you won't be able to create an index.
If you have the possibility to shrink down this field down to maximum 1700 bytes, then you will be able to index the column.

OriginalGriff 25-Nov-21 14:31pm

How would we know? It's *your* data, not ours!

We have no idea what your data contains, so we can't have any idea how to "reduce it's length" without corrupting it. Almost certainly, you can't unless you can reduce the size of the data by packing it into fewer bits per character, but we can have no idea if that's even possible.

ahmed_sa 25-Nov-21 14:39pm

table created by this statment
SELECT TradeCodeControlID ,
VersionYear ,
Version ,
CodeTypeID ,
RevisionID ,
Code ,
ZPLID ,
ZfeatureKey ,
ZfeatureType ,
IIF(CHARINDEX('~', ZValue)>0, SUBSTRING(ZValue,1,CHARINDEX('~', ZValue)-1),TC.ZValue) AS EStrat,
LTRIM(IIF(CHARINDEX('~', ZValue)>0, SUBSTRING(ZValue,CHARINDEX('~', ZValue)+1,LEN(ZValue)),'')) AS EEnd
INTO ExtractReports.dbo.TGen
FROM Parts.TradeCodeControl TC with(nolock)
WHERE TC.ZPLID=@productID

how to case column Estrat to can create index it

OriginalGriff 25-Nov-21 14:48pm

It's not the table creation that's the problem - *it's the data you are putting in it*

As the error message very clearly says, the max length of a clustered index is 1700 bytes.
If your column is longer than that, it can't be indexed.
If your data is longer than that, it can't be fitted in the column if it is indexed.

Simple version: "You can't put a quart in a pint pot". Capiche?

ahmed_sa 25-Nov-21 15:16pm

how to cast this line
IIF(CHARINDEX('~', ZValue)>0, SUBSTRING(ZValue,1,CHARINDEX('~', ZValue)-1),TC.ZValue) AS EStrat,
to varchar(3000) to accept create index

OriginalGriff 25-Nov-21 16:34pm

I see.

We need to start with basic mathematics...

Which of these two numbers is the biggest: 3000 or 1700?

phil.o 25-Nov-21 17:35pm

I voted 5 just for the surrealistic conversation :laugh:

OriginalGriff 25-Nov-21 17:49pm

Ceci n'est pas une pipe.

Richard Deeming 26-Nov-21 6:56am

Based on the OP's question history, I'm starting to suspect Ceci n'est pas un développeur.

Error the index entry of length 1890 bytes for the index 'IDX_estrat' exceeds the maximum length of 1700 bytes for nonclustered indexes

1 solution

Solution 1

Add your solution here

Preview 0

Error the index entry of length 1890 bytes for the index 'IDX_estrat' exceeds the maximum length of 1700 bytes for nonclustered indexes

1 solution

Solution 1

Add your solution here

Preview 0

Existing Members

...or Join us