Is there any rule to decide what columns should be put in Included in non clustered index and in which order

Why use the INCLUDE clause when creating an index? Is there any rule to decide what columns should be put in Included in non clustered index and in which order.

I got that for the following query :

SELECT PrimaryInformation_Id, Company_Id, LastName
FROM Employee_PrimaryInformation
WHERE Company_Id = 5

I am suggested to make index like this:

CREATE NONCLUSTERED INDEX NC_EmpPrim
  ON Employee_PrimaryInformation(PrimaryInformation_Id, Company_Id)
  INCLUDE (LastName)

Yes, i know. what you think why can't we make index like this'

CREATE NONCLUSTERED INDEX NC_EmpPrim
      ON Employee_PrimaryInformation(PrimaryInformation_Id, Company_Id, LastName)

OR

CREATE NONCLUSTERED INDEX NC_EmpPrim
      ON Employee_PrimaryInformation(Company_Id, LastName)
INCLUDE (PrimaryInformation_Id)

For your example I've added a comment.'

CREATE INDEX <name> ON <table> (KeyColList) INCLUDE (NonKeyColList)

Where:

• KeyColList = Key columns = used for row restriction and processing 
• WHERE, JOIN, ORDER BY, GROUP BY etc
• NonKeyColList = Non-key columns = used in SELECT and aggregation (e.g. SUM(col)) after selection/restriction

Thank you for reading the article about Is there any rule to decide what columns should be put in Included in non clustered index and in which order on the blog NEW TECH If you want to disseminate this article on please list the link as the source, and if this article was helpful please bookmark this page in your web browser by pressing Ctrl + D on your keyboard keys.

New articles :

Like this article? Please share :

FacebookGoogle+