CODEWITHSUNDEEP
sqlsql-servert-sqldatabase-administration
RMu
RMu

Reputation: 869

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

I have a temporary variable with a computed column based on SHA1 hasbyte. When I create a UNIQUE non clustered index on the computed column, it throws the warning::

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

I remember reading out put of SHA1 byte being limited to some 160 bytes. I'm unable to understand why I get this warning.

If check the max length of all the columns involved in computed column by the below query it adds up to some 8148 bytes. But i thought with the hash I'm using I should get not more than 160 bytes for the computed column.

SELECT Sum(c.max_length)
FROM SYS.COLUMNS C INNER JOIN SYS.TABLES tb ON tb.[object_id] = C.  [object_id]
INNER JOIN SYS.TYPES T ON C.system_type_id = T.user_type_id
WHERE tb.[is_ms_shipped] = 0
and OBJECT_NAME(tb.object_id)='xyz'

Please advise what I'm missing here.

Upvotes: 2

Views: 2653

Answers (1)

Juan Carlos Oropeza
Juan Carlos Oropeza

Reputation: 48197

Im not expert on Hash:

But I suggest you create a column combine varchar(750) (15*50)

Then update that column with SET combine = Col1+ Col2+ Col3+.. Col15

And try:

CompSum AS HashBytes('SHA1', combine)

That way you are using a field which will never be bigger than 750

Upvotes: 2

Related Questions