INCLUDE clause WITH NON CLUSTERED INDEX

 

 

This article is dedicated to one of my colleague, who want to know about the INCLUDE clause of NON CLUSTERED Index. I try to cover all the important points related to it in short.

In SQL Server 2005, the functionality of non-clustered indexes is extended by adding non-key columns to the leaf level of the non-clustered index.

By including non key columns we create non clustered index that covers more query and increase performance.

The features of Adding non key columns are mentioned bellow:

1.    Index has the limitation of 16 key columns and maximum index key size of 900 bytes. But if we include non key columns the index not care about the index key size and 123 includes non key columns is allowed.
So a non clustered index contains:

16 key Columns + 123 Includes non Key columns.

2.    Columns cannot be repeated in the include list and Dropping of non-key columns is possible after dropping the non-key index first.  

 To DROP Non-KEY Columns à DROP the NON-KEY INDEX First

3.    The database Engine not considers any non-key columns to calculate the number of index key columns or the size of the index.

Size of Index Not Related to Non-Key Columns

4.    The data types of non-key columns can be anything evens the data type not supported by the non clustered Index Key-columns.

5.    You should keep those columns in "Include" clause which generally comes under "SELECT" clause and not being used much in "WHERE", "GROUPBY" or "ON" clause of "JOIN".

 

The syntax of creating NON CLUSTERED INDEX with INCLUDE clause is mentioned bellow.

 

 

CREATE NONCLUSTERED INDEX [Ind-1] ON [dbo].[tab_exmaple]

(

      [sroll] ASC

)

INCLUDE ( [sSec],[sName])

 

 

Architecture of INCLUDE on NON CLUSTERED Index:

 

The architecture of "Included Column" concerns, whatever columns you have defined under "Include" clause under the index, those will be stored on the Leaf pages, and it won't get stored on the Root page or Intermediate page of the index.

Hope the article is quite interesting and thanking you to provide your valuable time on it.

 

 

 

Posted by: MR. JOYDEEP DAS