The include feature of SQL Server Index

1. Why we need the index 'include' feature?

For SQLServer , the length of all the index key have a limit length as 900 byte.

when you create a index whose keys' total length may exceced 900 byte , such as below

CREATE TABLE GPCUSTEXT(
CUSTNO nvarchar(20),
FIELD_VALUE nvarchar(2000) ,
CREATED_ON datetime ,
CREATED_BY int ,
MODIFIED_ON datetime ,
MODIFIED_BY int ,
EXTCUST_ID int NOT NULL,
METAFIELD_ID int,
CONSTRAINT PK_GPCUSTEXT PRIMARY KEY CLUSTERED (EXTCUST_ID)
)

go

create index GPCUSTEXT_CUSTNO_METAFIELD_ID on gpcomp1.GPCUSTEXT(CUSTNO,METAFIELD_ID,FIELD_VALUE)

you will got as warning saying the data length may exceed the 900 byte at that situation sqlserver cannot put all key data into the index tree.

or you want to have a LOB column in the index , such as below

CREATE TABLE GPPROB(
PROBLEM_ID int NOT NULL,
CUSTNO nvarchar(20) NULL,
SALESID nvarchar(45) NULL,
PCODE nvarchar(10) NULL,
STATUS nvarchar(100) NULL,
PTEXT nvarchar(max) NULL,
CONSTRAINT PK_GPPROB PRIMARY KEY CLUSTERED (PROBLEM_ID)
)

go

create index GPPROB_CUSTNO_PTEXT on GPPROB(CUSTNO,PTEXT)

you will got an error saying the PTEXT is invalid for index as it is a LOB.

So SQLServer introduced the 'include' feature to go around above situation.

Also if we updated the 'include' column, the index no need to re-order due to the 'include' has nothing with the index tree leaf order.

Vice versa, the SQLServer will not consider the 'include' column when judge which index need to use.

then execute a query sql as below

select custno,ptext from gpcomp1.GPPROB

Due to the 'include' column , the index 'GPPROB_CUSTNO_PTEXT' have all data needed for the query sql,

so then sqlserver will only scan the index 'GPPROB_CUSTNO_PTEXT' to get all data.  It will not access the table.

We only have to consider the 'include' column when the key length is exceed 900 byte or want to include a LOB column to improve the performance.