33

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
  • 1,912
  • 2
  • 33
  • 40
ShodowOverRide
  • 431
  • 2
  • 5
  • 14
  • 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://docs.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,382
  • 4
  • 23
  • 39
  • 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
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
  • 25,929
  • 15
  • 67
  • 67
Rahul Hera
  • 61
  • 5
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
  • 7,948
  • 7
  • 44
  • 58