4

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.

Shashi
  • 12,487
  • 17
  • 65
  • 111

2 Answers2

12

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.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
4

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.

KM.
  • 101,727
  • 34
  • 178
  • 212
  • See also Remus Rusanu's answer to a similar question here: http://stackoverflow.com/a/8002357/4515489 – jk7 Sep 17 '15 at 18:16