900 字节索引大小限制在字符长度

900 byte index size limit in character length(900 字节索引大小限制在字符长度)

问题描述

SQL Server 2012 具有的 900 字节索引限制的总字符限制是多少.我创建了一个包含 varchar(2000) 的列，但我认为它超过了 SQL Server 限制的 900 字节?适合 900 字节索引列的最大 varchar(?) 是多少?

What is the total character limit for a 900 byte index limit that SQL Server 2012 has. I created a column that has varchar(2000), but I think that it exceeding the 900 byte that SQL Server limited? What would be a max varchar(?) to fit inside the 900 byte index column?

推荐答案

varchar 是输入数据的实际长度 + 2 个字节.即使列本身有 2 字节的开销，您也可以承受 900 字节 varchar 值到一个被索引的列中.

The storage size for varchar is the actual length of the data entered + 2 bytes. Even though the column itself has that 2 byte overhead, you can put up to 900 byte varchar values into a column which is indexed.

在实践中，您可以创建一个大于 900 字节的列的索引，但是如果您实际尝试插入大于900 字节:

In practice, you can create an index on a column larger than 900 bytes in size, but you will have a problem if you actually try to insert something larger than 900 bytes:

create table test (
    col varchar(1000)
);
create index test_index on test (col);
-- Warning! The maximum key length is 900 bytes. The index 'test_index' has maximum length of 1000 bytes. For some combination of large values, the insert/update operation will fail.
insert into test select cast(replicate('x', 899) as varchar(1000)); -- Success
insert into test select cast(replicate('y', 900) as varchar(1000)); -- Success
insert into test select cast(replicate('z', 901) as varchar(1000)); -- Fail
-- Msg 1946, Level 16, State 3, Line 8
-- Operation failed. The index entry of length 901 bytes for the index 'test_index' exceeds the maximum length of 900 bytes.

请注意，900 字节的限制包括给定索引键的所有列，如下例所示:

Be aware that the 900-byte limit includes all columns of a given index key, as this example shows:

create table test (
      col varchar(1000)
    , otherCol bit -- This column will take a byte out of the index below, pun intended
);
create index test_index on test (col, otherCol);
insert into test select cast(replicate('x', 899) as varchar(1000)), 0; -- Success
insert into test select cast(replicate('y', 900) as varchar(1000)), 0; -- Fail
insert into test select cast(replicate('z', 901) as varchar(1000)), 0; -- Fail

对于这些通常对于索引键来说太大的列，您可以通过 将它们包含在索引中.

For these columns that are normally too large for an index key, you may be able to gain some benefits of indexing by including them in an index.

这篇关于900 字节索引大小限制在字符长度的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持编程学习网！