-1

i am getting the error as:

"The index entry of length 904 bytes for the index 'IX_companyinfo_exhibition' exceeds the maximum length of 900 bytes".

exblist nvarchar(600),i changed this to nvarchar(450).i dropped the index ie.IX_companyinfo_exhibition.

i tried everything.even i used this:

CREATE NONCLUSTERED INDEX IX_companyinfo_exhibition ON dbo.companyinfo(exblist) include(companyname,website,country,contactperson,telphone)

i dont know whats wrong with this.still i am getting the same error.

enter image description here

enter image description here

SP_helpindex for table companyinfo

chetan kambli
  • 794
  • 5
  • 21
  • Good background info here: https://technet.microsoft.com/en-us/library/ms191241(v=sql.105).aspx – Peter B Feb 22 '18 at 11:48
  • 3
    Could I suggest changing the length to 448? I am guessing that will give you back the extra 4 bytes. Why would you be indexing such a large column? – Gordon Linoff Feb 22 '18 at 11:48
  • 1
    @jdweng The limits for column size aren't the same as the limits for index size. https://technet.microsoft.com/en-us/library/ms191241(v=sql.105).aspx – mjwills Feb 22 '18 at 11:49
  • kindly see the below 2 links......https://ibb.co/d9X0dH https://ibb.co/gauDyH – chetan kambli Feb 22 '18 at 11:50
  • 1
    `SELECT * FROM CompanyInfo WHERE DATALENGTH(exblist) > 900` should give you back the offending row(s). – Jeroen Mostert Feb 22 '18 at 11:50
  • @chetankambli Please include the images in the question, not in a comment that is too easy to miss – Peter B Feb 22 '18 at 11:51
  • 2
    Possible duplicate of [900 byte index size limit in character length](https://stackoverflow.com/questions/12717317/900-byte-index-size-limit-in-character-length) – Peter B Feb 22 '18 at 11:52
  • @mjwills...if i removed include part also,it gives me the same error... – chetan kambli Feb 22 '18 at 11:53
  • Please run `sp_help dbo.companyinfo` and update your post with the result. – mjwills Feb 22 '18 at 11:55
  • Try posting the schema of table as text and the repro steps – TheGameiswar Feb 22 '18 at 13:08
  • You should really look into how to design a database. Based on your column names and types, it looks like several of your columns contain some sort of comma etc. separated lists. That's going to be awful to manage & query. – James Z Feb 22 '18 at 18:02
  • @mjwills...i updated...kindly check the image – chetan kambli Feb 23 '18 at 04:43

1 Answers1

1

Based on SQL Server documentation, nvarchar(450) should work.

I do find the "904" to be confusing. The actual size of nvarchar(450) is 902 bytes (see here). No doubt, the extra two bytes are coming from somewhere.

So, you should be able to fix this by using nvarchar(448).

If that is not possible, you can add another column and index that:

alter t add exblist448 as (left(exblist, 1, 448))

and then index this.

All that said, I don't think this will solve your real problem. I don't see why an index on a 450-character string would be needed. I also don't understand why a column name would contain "list".

I strongly suspect that you really need either a second table with one row per list item and company. Or, you need a full text index.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • ...when i am trying to insert some emailids it throws out that error with different numbers..sometimes it is 904...sometimes its is 908..depending upon the entered emailids..For some entered emailids it is inserting fine... – chetan kambli Feb 22 '18 at 12:14
  • @chetankambli . . . As suspected. First, you don't really want to put email ids into a single column. A separate table with one row per email id would be the correct approach. Second, an index on the column is not going to be very helpful. – Gordon Linoff Feb 22 '18 at 12:25
  • @Gordon...If i am removing that index(i.e.IX_companyinfo_exhibition), then that error should not have come...right? – chetan kambli Feb 23 '18 at 03:46
  • @gordon....https://ibb.co/hMwWkx....... this is the link from where i entered emailid (i.e. info@pakona.com),and then i clicked on save button and then i got error that "904 exceeds size limit 900"....so if i removing na or a from info@pakona.com..it is saving and not throwing that error... – chetan kambli Feb 23 '18 at 03:54
  • @GordonLinoff actually i already mentioned that to him on his first question https://stackoverflow.com/a/48878560/2705620 – Albert Laure Mar 05 '18 at 05:30
  • @GordonLinoff...i removed that index....and it worked perfectly for me...thank u so much...:) – chetan kambli Mar 21 '18 at 11:56