creating the composite index with more than 900 bytes

Creating the composite index with more than 900 bytes:
Sql server is having some constraints while creating the index. The index entry will not allow if the data size is more than 900 bytes. It gives the error while inserting the data.

There is a way to insert the more than 900 bytes into composite index. we see that in this article.

Intial setup:

Creating the table with two columns and total size is more than 900 bytes.

use tempdb
create table tmp
(
c1 varchar(10),
c2 varchar(1000)
)

Index creation:

The create index statement gives the warnings. Because total size is havng more than 900 bytes.

create index idx_tmp on tmp(c1,c2)

Warning! The maximum key length is 900 bytes. The index 'idx_tmp' has maximum length of 1010 bytes. For some combination of large values, the insert/update operation will fail.

Test data Length:
select len('

test_data test_data test_data test_data test_data test_data test_data test_data test_data test_data test_data
test_data test_data test_data test_data test_data test_data test_data test_data test_data test_data test_data
test_data test_data test_data test_data test_data test_data test_data test_data test_data test_data test_data
test_data test_data test_data test_data test_data test_data test_data test_data test_data test_data test_data
test_data test_data test_data test_data test_data test_data test_data test_data test_data test_data test_data
test_data test_data test_data test_data test_data test_data test_data test_data test_data test_data test_data
test_data test_data test_data test_data test_data test_data test_data test_data test_data test_data test_data
test_data test_data test_data test_data test_data test_data test_data test_data test_data test_data test_data
test_data test_data test_data test_data test_data test_data test_data test_data test_data test_data test_data
')




Insert statement with more than 900 bytes:

The toltal size of the insert is 1010 bytes here. It is not allowing to insert and giving the error.


insert into tmp values( 'hai',
'test_data test_data test_data test_data test_data test_data test_data test_data test_data test_data test_data
test_data test_data test_data test_data test_data test_data test_data test_data test_data test_data test_data
test_data test_data test_data test_data test_data test_data test_data test_data test_data test_data test_data
test_data test_data test_data test_data test_data test_data test_data test_data test_data test_data test_data
test_data test_data test_data test_data test_data test_data test_data test_data test_data test_data test_data
test_data test_data test_data test_data test_data test_data test_data test_data test_data test_data test_data
test_data test_data test_data test_data test_data test_data test_data test_data test_data test_data test_data
test_data test_data test_data test_data test_data test_data test_data test_data test_data test_data test_data
test_data test_data test_data test_data test_data test_data test_data test_data test_data test_data test_data'
)

INCLUDE clause:

There is another way to avoid this error and create a index on both the columns.

drop index idx_tmp on tmp

By using the INCLUDE clause of the CREATE INDEX statement, the index key could be defined as (c1) and c2 defined as a nonkey column. In this way, the index key size would be 10 bytes.


CREATE INDEX idx_tmp2
ON tmp (c1)
INCLUDE (c2);

Insert statement with more than 900 bytes:

The toltal size of the insert is 1010 bytes here. This time it is allowing to insert

insert into tmp values( 'hai',
'test_data test_data test_data test_data test_data test_data test_data test_data test_data test_data test_data
test_data test_data test_data test_data test_data test_data test_data test_data test_data test_data test_data
test_data test_data test_data test_data test_data test_data test_data test_data test_data test_data test_data
test_data test_data test_data test_data test_data test_data test_data test_data test_data test_data test_data
test_data test_data test_data test_data test_data test_data test_data test_data test_data test_data test_data
test_data test_data test_data test_data test_data test_data test_data test_data test_data test_data test_data
test_data test_data test_data test_data test_data test_data test_data test_data test_data test_data test_data
test_data test_data test_data test_data test_data test_data test_data test_data test_data test_data test_data
test_data test_data test_data test_data test_data test_data test_data test_data test_data test_data test_data'
)

The index has included both the columns. The below query proves that.

select b.name index_name,a.name column_name from sys.columns a
inner join sys.indexes b
on a.object_id=b.object_id
where b.name ='idx_tmp2'

Addition to that we verify the explain plan. It goes for the index scan.
select * from tmp where c1='10'


select * from tmp where c2='10'