35

I transfer a db script from a 64 bit system to a 32 bit system. When I execute the script it gives me the following error,

Warning! The maximum key length is 900 bytes. The index 'UQ__Users__7E800015145C0A3F' has maximum length of 1000 bytes. For some combination of large values, the insert/update operation will fail.

Google results and Stack Overflow questions did not help to solve this problem.

Breeze
  • 2,010
  • 2
  • 32
  • 43
ShodowOverRide
  • 451
  • 2
  • 5
  • 15
  • Please specify what DBMS you are using? –  Mar 04 '14 at 12:42
  • sql express server 10.50.2500 – ShodowOverRide Mar 04 '14 at 12:43
  • possible duplicate of [900 byte index size limit in character length](http://stackoverflow.com/questions/12717317/900-byte-index-size-limit-in-character-length) – Cyril Durand May 28 '15 at 14:56
  • 4
    I solved this by altering `NVARCHAR(512)` to `NVARCHAR(450)`, since in my case, 2 * SIZE = 2 * 450 = 900 bytes. But, it was only limited because I supplied `UNIQUE` to that field. – Alexandru Jun 16 '16 at 23:01
  • great solution. an aside as of sql server 2016 sha1 is deprecated and should sha2_256 or sha2_512 according microsoft: https://learn.microsoft.com/en-us/sql/t-sql/functions/hashbytes-transact-sql?view=sql-server-ver15 – gg89 Sep 22 '21 at 22:46

3 Answers3

12

For indexing columns that have Large sizes ,sqlserver indexes only columns that have size up to 900 bytes.

To solve this problem

Firstly : I added a column hashCol to generate hashcode of Type SHA1 for MyCol

alter table myTable
    add 
    hashCol AS HASHBYTES('SHA1', CONVERT(VARCHAR(90), MyCol))

Secondly : I added a unique constrain for hashCol to uniquely Identify MyCol

 ALTER  TABLE  myTable
     ADD CONSTRAINT hashCol_Unique UNIQUE (hashCol)

By this way I overcame the problem of Indexing columns that have large size

references

Generate Unique hash for a field in SQL Server in-sql-server

Community
  • 1
  • 1
Elsayed
  • 2,712
  • 7
  • 28
  • 41
  • Got an error "Column 'HashCol' in table 'MyTable' cannot be used in an index or statistics or as a partition key because it is non-deterministic.". How to fix it? – LCJ Sep 03 '20 at 18:46
  • To make sure the indexing works, I would rather do the following: hashCol as convert(varchar(100), hashbytes('SHA1', MyCol), 2) persisted not null – Mike O. Aug 20 '22 at 17:12
  • 1
    This is okay but be sure to check that this approach doesn't generate collisions in your database which happened to me. – HamiltonPharmD Jul 05 '23 at 20:48
4

Storage length of the varchar type will be +2.

Solution

ALTER TABLE table_name
ALTER COLUMN column_name varchar(255)

so try to reduce the column length to 255 character and try indexing.

Andrey Korneyev
  • 26,353
  • 15
  • 70
  • 71
Rahul Hera
  • 61
  • 6
2

The combined length of all the columns should be less than 900.

-- make combined colum length(to be indexed / add constrains) less <= 900
ALTER TABLE tabbleName ALTER COLUMN col1 VARCHAR (500) NULL;
ALTER TABLE tabbleName ALTER COLUMN col2 VARCHAR (200) NULL;
ALTER TABLE tabbleName ALTER COLUMN col3 VARCHAR (200) NULL;
-- Then add the index
ALTER TABLE tabbleName ADD CONSTRAINT uck UNIQUE (col1, col2, col3);
sapy
  • 8,952
  • 7
  • 49
  • 60