A workaround for limitation of 900 bytes index key

 

 

You may get the warning if you try to create an index on the column(s), whose total length is above 900 bytes.

 

For example:
CREATE TABLE TEST
(
COL1 INT,
COL2 NVARCHAR(500),
COL3 nvarchar(500),
)
GO
Create unqiue index IDX_A on Test(col1, col2,, col3)
go
--Notes:

--you can create the index while it reports to you that the insert or update operation may fail

--when the length of value you insert or update exceeds 900 bytes

 

insert into test

values ( 1,'460 bytes', ' 460 bytes ')

go

--insert runtime failed

--Error report as below:

Msg 1946, Level 16, State 3, Line 26

Operation failed. The index entry of length 1024 bytes for the index 'IDX_A' exceeds the maximum length of 900 bytes.

 

The first option, if you do NOT insist to create the unique one , you can use include clause in the index to avoid this.

From the business prosperity , if you do need the unique constraint, the below workaround could be claimed to be perfect.

Using the build-in function CHECKSUM, which will generate a hask key for the combined the columns then create an unique index on this computed column.

 

Taking an instance:

 

CREATE TABLE TEST

(

COL1 INT ,

COL2 NVARCHAR ( 500),

COL3 nvarchar ( 500),

COL4 AS CHECKSUM ( CAST ( COL1 AS NVARCHAR ( 10)) + COL2 + COL3)

)

 

GO

 

CREATE UNIQUE INDEX IDX_Test ON TEST ( COL4)

GO

 

INSERT INTO TEST ( COL1, COL2, COL3) VALUES ( 1, 'ABRACADABRA-1' , 0)

GO

INSERT INTO TEST ( COL1, COL2, COL3) VALUES ( 2, 'ABRACADABRA-2' , 0)

GO

 

--this should failed because of the validation of duplicated values

--error reports as below

/*

Msg 2601, Level 14, State 1, Line 1

Cannot insert duplicate key row in object 'dbo.TEST' with unique index 'IDX_Test'.

The statement has been terminated.

*/

INSERT INTO TEST ( COL1, COL2, COL3) VALUES ( 2, 'ABRACADABRA-2' , 0)

go