How does SQL Server treat Included columns in a nonclustered index?

Question

I have a question:

Definition of non-clustered index says that included columns in index are not counted by database engine in sense of index size or maximum number of columns.

So what is really the way they work?

How they help to SQL Server when they are not acting in index size?

Answer 1

The important thing to note is that included columns are not counted by the database engine when determining the size or number of columns in the index key (the value used to actually look up data in the index structure). They still add to the size of the index itself.

Index keys are only allowed to be 900 bytes in size across all columns that make up the key (there can be only 16 columns that make up the index key).

Adding included columns doesn't count towards the 900 byte/16 column limits, but can make the index more useful by covering more queries.

Answer 2

Good explanations from the other people here.

For me, included index columns are rather easy to remember and use with this simple rule: Filters, ie. WHERE x = y etc.., are your keys, the decision whether to use the index or not is based on those. SELECT a, b, x are the values you're actually returning, those are the things you want to include in your index so SQL Server doesn't have to go searching through the clustered index / heap to find them.

Example:

CREATE NONCLUSTERED INDEX TABLEX_A_IDX ON TABLEX (A) INCLUDE (B, C)    

SELECT A, B, C -- KEY + INCLUDED columns
FROM TABLEX WHERE A = 'ASD' -- KEY columns

Granted, this wasn't exactly your question, but it might help just the same.