CODEWITHSUNDEEP
sql-servert-sql
pawan-----SQLServer
pawan-----SQLServer

Reputation: 73

Include clause for index creation

May I know how the include clause improves performance in a covering index?

CREATE NONCLUSTERED INDEX includeIndex
  ON mytable(COL1)
  INCLUDE(COL2,COL3,COL3)

and what is the difference between 

CREATE NONCLUSTERED INDEX includeIndex ON mytable(COL1) INCLUDE(COL2,COL3,COL3)

and

CREATE NONCLUSTERED INDEX nonincludeIndex ON mytable(COL1,COL2,COL3,COL3)

Thanks

Upvotes: 2

Views: 381

Answers (2)

TomTom
TomTom

Reputation: 62093

If your query does only query fields in the index (includes included fields) then after finding which rows to return the sql processor fdoes not have to load the actual data page to get the data. That simple. It can answer the query from the index only.

Upvotes: 1

Kinexus
Kinexus

Reputation: 12904

You can extend the functionality of nonclustered indexes by adding nonkey columns to the leaf level of the nonclustered index. By including nonkey columns, you can create nonclustered indexes that cover more queries. This is because the nonkey columns have the following benefits:

They can be data types not allowed as index key columns.

They are not considered by the Database Engine when calculating the number of index key columns or index key size.

An index with included nonkey columns can significantly improve query performance when all columns in the query are included in the index either as key or nonkey columns. Performance gains are achieved because the query optimizer can locate all the column values within the index; table or clustered index data is not accessed resulting in fewer disk I/O operations.

http://msdn.microsoft.com/en-us/library/ms190806.aspx

Upvotes: 2

Related Questions