How to set index on the column which have datatype varchar(4096) in SQL Server 2005?

Question

This is the query for creating index create index idx_ncl_2 on BFPRODATTRASSOCIATION (value,attributeid) include (productid)

Table structure of BFPRODATTRASSOCIATION

ProdAttrAssociationId bigint no 8
ProductId             bigint no 8
AttributeId           bigint  no  8
Value                 varchar no 4096

I am getting this error:

The maximum key length is 900 bytes. The index ‘idx_ncl_2’ has maximum length of 1237 bytes.

I have to create a nonclustered index on this column. Is there any way i can create index for the column which have datatype of varchar and size is greater than 900.

Please suggest.

Answer 1

You can't - as the error message already clearly states, any index entry cannot be more than 900 bytes long.

You cannot index a varchar(4096) field - period. No way around that - it's a hard SQL Server limit - no way to configure it, change it, make it bigger. See Books Online - Maximum Size of Index Keys for confirmation.

You need to either limit your "value" column to less than 900 bytes, or find another way to store that data - or just not include it in the index. If you only want your "value" field in the index to have a covering index (to be able to satisfy queries from the index entry), you could move the field to be an included column in the index - those don't fall under the 900 byte limit.

CREATE NONCLUSTERED INDEX idx_ncl_2 
  ON BFPRODATTRASSOCIATION(attributeid) 
  INCLUDE (productid, value)

That index should work.

Answer 2

You could create and index on a computed column that is the hash of the string. See Hash functions in T-SQL and Intelligent Database Design Using Hash Keys for some ideas and limitations.