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?

Answer 1

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.

Answer 2

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.