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.
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.