如何在SQL Server 2005中具有数据类型varchar（4096）的列上解析900密钥长度限制索引？ [英] How to resolve 900 key length limit index on the column which have datatype varchar(4096) in SQL Server 2005?

问题描述

这是用于创建索引的查询
在BFPRODATTRASSOCIATION上创建索引idx_ncl_2（value，attributeid）include（productid）

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

BFPRODATTRASSOCIATION的表结构

Table structure of BFPRODATTRASSOCIATION

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

我收到此错误：

最大密钥长度为900字节。索引'idx_ncl_2'的最大长度为1237字节。

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

我必须在此列上创建非聚集索引。有没有办法可以为数据类型为varchar且大小超过900的列创建索引。

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.

请建议。

推荐答案

您不能 - 因为错误消息已经明确说明，任何索引条目的长度不能超过900个字节。

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

您无法索引varchar（4096）字段 - 句点。没办法 - 这是一个严格的SQL Server限制 - 无法配置，更改它，使其更大。请参阅联机丛书 - 索引密钥的最大大小以进行确认。

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.

您需要将值列限制为小于900字节，或者找到另一种方法来存储该数据 - 或者只是不将其包含在索引中。如果您只希望索引中的值字段具有覆盖索引（以便能够满足索引条目中的查询），则可以将该字段移动到索引中的包含列 - 那些不在900字节限制之内。

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)

该指数应该有效。

这篇关于如何在SQL Server 2005中具有数据类型varchar（4096）的列上解析900密钥长度限制索引？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！