Warning! The maximum key length is 900 bytes. The index has maximum length of 1000 bytes

Question

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.

Answer 1

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

Answer 2

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.