9 Oct 2015

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

1 comment:

  1. Technical Support For AT&T TV U-verse Call Toll Free 1-800-261-4831
    Att Email Support
    Att TV Support
    Router Support

    ReplyDelete