Previous Next Warning the Maximum Key Length is 900 bytes

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)

　　

posted on 2016-05-06 09:50  capad 阅读( ...) 评论( ...) 编辑 收藏

转载于:https://www.cnblogs.com/clith/p/5464523.html