INCLUDED COLUMNS or Covering Index in SQL Server

INCLUDED Columns in SQL Server

To overcome the limitations of non-clustered indexes from SQL Server 2005 onwards they introduced a concept called Included Columns

Limitations of non-clustered indexes are
Only 16 columns can be included in the index.
Maximum index key cannot exceed 900 bytes.

Covering Index which is a non-clustered index that contains all columns that are appear in the select statement, below example shows how covering index works. We need to remember covering index is not always gives good performance advantage.


Examples

create table BookCLTest (BookID Int, BookName varchar(500), BookDescription varchar(500))

create index idxBookName on BookCLTest(BookName, BookDescription)

After executing the created index command you will see the below error because it has limitation of 900 bytes

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

insert into bookClTest values (1, replicate('A',500), replicate('B',500))

If you insert any record with maximum size it wont allow you to insert and you will get the below error:

Msg 1946, Level 16, State 3, Line 1

Operation failed. The index entry of length 1000 bytes for the index 'idxBookName' exceeds the maximum length of 900 bytes.

Alter table BookCLTest add RackLocation varchar(max)

create index idxbooklocation on BookCLTest(RackLocation)

You cannot create non clustered index on large datatypes like text, image, varchar(max) etc., columns

From SQL Server 2005 version onwards we can overcome this problem of Index size and Column limitations using INCLUDED Columns also we can include large data types.

Benefits of Included columns

1. We can create index with more than 900 bytes size.
2. We can include large data types like varchar(max), nvarchar(max) etc.,
3. Savings Disk IO

How it saves Disk IO?

When a non-clustered column is required in the select list, the actual table row must be accessed to retrieve the column; this is called a bookmark lookup. The bookmark lookup is most expensive operation when there is large table. If we include the non-clustered column in the included list then IO performance will increase.

We will remove the existing index in our example
sp_helpindex BookCLTest

drop index idxBookName on BookCLTest

Included Columns example

create index idxBookName on BookCLTest(BookID) include (BookDescription, RackLocation)

In the above example we have included one varchar column and large type column.

Considerations
1. Column names cannot be repeat in the included columns
2. The same column name which used to create non-clustered index cannot be include in the included column list
3. All columns can be included except text, image.


Script to view table that has included columns

select object_name(sic.object_id), * from sys.index_columns sic, sys.syscolumns sc, sys.tables st where sic.object_id = sc.id and sic.column_id = sc.colid and st.object_id = sic.object_id and st.object_id = sc.id and

sic.is_included_column = 1 

What is Key column and Non-key column

 

The column which is specified to create a clustered or non clustered index is key column.

 

The columns which are added to INCLUDE clause are non-key columns

 

Note - A column cannot be a key and non-key column.

 

The primary difference between key and non key columns are where the data of the column fit in the B-Tree structure, All clustered index and non-clustered key columns are stored in every level of

B-tree ie., either leaf level or intermediate levels. Whereas non key column stored only at leaf level.