900 byte index size limit in character length

Question

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?

The smallest character set I ever used had 1 byte per char (Latin-8859?). Guessing yours is at least that big. — keyser, Oct 03 '12 at 21:43
It is a SQL Server 2012 64 bit software. Nothing special about character sets. Just Windows 8 64 bit running SQL Server 2012 64 bit out of the box. US-English Windows. — iefpw, Oct 03 '12 at 21:44

score 53 · Accepted Answer · edited Oct 05 '15 at 15:20

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.

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.

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.

score 9 · Answer 2 · answered Dec 11 '13 at 16:11

9

On a related note, another option you can try, to get an index on a wide column, is outlined at http://www.brentozar.com/archive/2013/05/indexing-wide-keys-in-sql-server/ where a hash column is added to the table then indexed and used in your queries.

answered Dec 11 '13 at 16:11

lmingle

889
10
12

2

I used the `HASHBYTE` function to great effect, thanks! The OP was discussing SQLServer2012, but note the `SHA2_512` algorithm was only introduced in SQLServer2012, so if you are using an early version you have to use a different algorithm, as specifying `SHA2_512` for earlier versions just returns `null`! Here's the [docs for 2008](http://technet.microsoft.com/en-us/library/ms174415%28v=sql.100%29.aspx). For example: `select HASHBYTES('SHA2_512', 'The quick brown fox') as sha2_512, HASHBYTES('MD5', 'The quick brown fox') as md5, HASHBYTES('SHA1', 'The quick brown fox') as sha1`. – DarthPablo Feb 25 '14 at 10:11

TheGameiswar · Answer 3 · 2018-02-22T13:20:36.630

7

For those on SQLServer 2016, Index key size was increased to 1700 bytes..What's new in Database Engine - SQL Server 2016

The maximum index key size for NONCLUSTERED indexes has been increased to 1700 bytes.

Demo:

create table test
(
id varchar(800),
id1 varchar(900)
)

insert into test
select replicate('a',800),replicate('b',900)

create index nci on test(id,id1)

edited Feb 22 '18 at 13:20

answered Feb 22 '18 at 13:14

TheGameiswar

27,855
8
56
94

900 byte index size limit in character length

3 Answers3

Linked