Previous Next Warning the Maximum Key Length is 900 bytes

数据库中添加索引的时候有可能会产生警告“Warning The maximum key length is 900 bytes……”

 

　　创建测试表

CREATE TABLE [dbo].[xtTest](
    [id] [int] NOT NULL,
    [name] [varchar](50) NOT NULL,
    [city] [varchar](400) NOT NULL,
    [description] [varchar](500) NOT NULL
) ON [PRIMARY]

　　索引脚本

create index IX_xtTest on xtTest(name,city,description)

　　添加索引的时候会产生一个警告

Warning! The maximum key length is 900 bytes. The index ‘IX_xtTest’ has maximum length of 950 bytes. For some combination of large values, the insert/update operation will fail.

当前索引包含的三个字段长度超过了900字节

 

插入一条测试信息

insert into xtTest values(1,'prince','gurgaon','gurgaon is in haryana state')

　　可以成功执行数据插入脚本，没有任何异常

 

如果city，description包含更多的字符，比如刚好是400和500个，加上name的字段的几个字符，超过了900

insert into xtTest values(2,'kamal','insert any string containing 400 characters','insert any string containing 500 characters')

　　脚本执行失败，并提示The index entry of length 905 bytes for the index ‘IX_xtTest’ exceeds the maximum length of 900 bytes

 

所以不要忽略创建索引时的警告信息，或者确保不会插入超过900字节的内容（不合理的设计）

 

如果必须将description这样的字段添加到索引，建议这样做

create index IX_xtTest on xtTest(name,city)
include(description)