字符长度限制为900字节的索引大小 [英] 900 byte index size limit in character length

问题描述

SQL Server 2012具有900字节索引限制的字符总限制是多少。我创建了一个具有 varchar（2000）的列，但我认为它超过了SQL Server限制的900字节？什么是max varchar（？）以适应900字节的索引列？

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字节的索引大小的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！