http://computertriksno1.blogspot.in/

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

9 Oct 2015 3 comments

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
Share this article :

+ comments + 3 comments

31 March 2016 at 00:38

You can watch on YouTube => Click Here

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

29 April 2016 at 15:32

You can watch on YouTube => Click Here

!!!Demo!!! Hey there, first of all thank you so much for this post and honestly I was searching for the same information from last few days. Keep posting and keep sharing.
Cheap Software for Students !!!Demo!!!

Post a Comment

 
Support : GDDon | Creating Website | Gddon |
Copyright © 2013. Computer Tricks and Tips for System - All Rights Reserved
Template Created by Creating Website Modify by GDDon.Com
Proudly powered by Blogger