Post reply

error The index entry of length 1890 bytes for the index 'IDX_EStrat' exceeds th

  • November 25, 2021 at 6:55 pm

    #3956928

    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

  • November 26, 2021 at 3:24 pm

    #3957271

    I see this posted on a lot of other sites and the solution is pretty easy - don't add HUGE columns to a nonclustered index.  You need to shrink the size of the column to be within the limits or not have the nonclustered index on that column.

    SQL Server 2016 will let you make that index, but you get the same warning as you did so I wouldn't want to try doing any inserts on it (don't want those to fail).

    Is that a column you NEED an index on?  Is it often used in a WHERE clause or JOIN (likely not a JOIN; I imagine you would use the ID for those).

    The above is all just my opinion on what you should do. 
    As with all advice you find on a random internet forum - you shouldn't blindly follow it.  Always test on a test server to see if there is negative side effects before making changes to live!
    I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.

  • November 26, 2021 at 4:09 pm

    #3957288

    thank you for reply

    I solve issue by adding

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

     

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply