CODEWITHSUNDEEP
sql-serverkey
ShodowOverRide
ShodowOverRide

Reputation: 471

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

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.

Upvotes: 37

Views: 77631

Answers (3)

sapy
sapy

Reputation: 9592

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);

Upvotes: 2

Elsayed
Elsayed

Reputation: 3012

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

Upvotes: 12

Rahul Hera
Rahul Hera

Reputation: 61

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.

Upvotes: 4

Related Questions