Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Is there any hard and fast rule to decide what columns and in which order it should be put in Included in non clustered index. I was just reading this post Why use the INCLUDE clause when creating an index? and I found that for the following query :

SELECT EmployeeID, DepartmentID, LastName
FROM Employee
WHERE DepartmentID = 5

The poster suggested to make index like this:

CREATE NONCLUSTERED INDEX NC_EmpDep 
  ON Employee(EmployeeID, DepartmentID)
  INCLUDE (Lastname)

here comes my question why can't we make index like this

CREATE NONCLUSTERED INDEX NC_EmpDep 
      ON Employee( EmployeeID, DepartmentID, LastName)

or

    CREATE NONCLUSTERED INDEX NC_EmpDep 
          ON Employee( EmployeeID, LastName)
INCLUDE (DepartmentID)

and what thing leads the poster to decide to keep the LastName column included. Why not other columns? and how to decide in what order we should keep the columns there?

share|improve this question

migrated from stackoverflow.com Feb 13 '12 at 22:04

This question came from our site for professional and enthusiast programmers.

1  
INCLUDE should normally have the fields you will need AFTER a record has been found, saving you a round trip back to get more data. The order of the fields in the INCLUDE is not important. –  Jimbo May 31 '11 at 13:07
    
Ryk, personally I find this post helpful. –  Jason Young Dec 7 '12 at 14:27
    
I find this question helpful as well. Let's focus on good questions and good answers instead of stalking individuals.... –  Volvox Jun 24 at 15:55

3 Answers 3

up vote 16 down vote accepted

That index suggestion by marc_s is wrong. I've added a comment. (And it was my answer accepted too!)

The index for this query would be

CREATE NONCLUSTERED INDEX NC_EmpDep 
  ON Employee(DepartmentID)
  INCLUDE (Lastname, EmployeeID)

An index is typically

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|improve this answer
    
+1 - I agree (see my ans) that the sample indexes in OP are worthless for the query! –  JNK May 31 '11 at 13:10
    
Great! just one thing more what will decide the order of KeyColList and NonKeyColList. Can you just explain with my example? Suppose now my query is SELECT EmployeeID, DepartmentID, LastName FROM EmployeeWHERE DepartmentID = 5, StateID=4 How hsould be the index now? –  Rocky Singh May 31 '11 at 13:18
    
@Rocky - NonKeyColList order doesn't matter. KeyColList order should be in order of frequency you expect them to be used in queries. See my notes on my answer below, but it's like Last Name, First Name, Middile Initial in a phone book. You need the first field in order to find the second field. –  JNK May 31 '11 at 13:27

JNK and gbn have given great answers, but it's also worth considering the big picture - not just focusing on a single query. Although this particular query might benefit from an index (#1):

Employee(DepartmentID) INCLUDE (Lastname, EmployeeID)

This index does not help at all if the query changes slightly, such as:

SELECT EmployeeID, DepartmentID, LastName
FROM Employee
WHERE DepartmentID = 5 AND LastName = 'Smith'

This would need the index (#2):

Employee(DepartmentID, LastName) INCLUDE (EmployeeID)

Imagine you had 1,000 employees in Department 5. Using index #1, to find all the Smiths, you'd need to seek through all 1,000 rows in Department 5, as the included columns are not part of the key. Using index #2, you could seek directly to Department 5, LastName Smith.

Index #2 is thus more useful at servicing a wider range of queries - but the cost is a more bloated index key, which will make the non-leaf pages of the index larger. Every system will be different, so there's no rule-of-thumb here.


As a side note, it's worth pointing out that if EmployeeID was the clustering key for this table - assuming a clustered index - then you don't need to include EmployeeID - it's present in all non-clustered indexes, meaning index #2 could just be

Employee(DepartmentID, LastName)
share|improve this answer
1  
+1 for more useful info. For your last point, I tested this and explicit use of EmployeeID in the INCLUDE is actually ignored (based on size of index) if EmployeeID is the clustered index. It's more obvious though I think and there is no space downside. –  gbn May 31 '11 at 13:38
    
I absolutely agree - it's always better to be explicit, especially if it costs nothing! –  Jim McLeod May 31 '11 at 13:43
1  
Just in case... I mean I've tested clustered key in the INCLUDE (not EmployeeID explicitly) and it adds no space. In the key columns it does. –  gbn Jun 1 '11 at 4:57
    
@gbn Yes, the cluster key only needs to reside in the leaf-level of the index, which is where the INCLUDE columns reside. Moving it into the index key would mean it would exist in the non-leaf pages as well. This would result in a little bit of bloat, but not a terrible amount (on the intermediate level pages, you'd add another 4 bytes per leaf-level page, assuming an Integer). –  Jim McLeod Jun 1 '11 at 11:48

I'm not sure how you got that first one. For me, for that query, I would use:

CREATE NONCLUSTERED INDEX NC_EmpDep 
  ON Employee(DepartmentID)
  INCLUDE (EmployeeID, Lastname)

There's not a "Hard and fast rule" for pretty much anything in SQL.

But, for your example, the only field the index will use is DepartmentID because it's in the WHERE clause.

The other fields just need to be easily accessible from there. You select based on DepartmentID then the INCLUDE has those fields at the leaf node of the index.

You don't want to use your other examples because they wouldn't work for this index.

Think of an index like a phone book. Most phone books are ordered by Last Name, First Name, Middle Initial. If you know someone's first name, but not their last name, the phone book does you no good since you can't search for first name based on the order of the phone book's index.

The INCLUDE fields are like the phone number, address, etc. other information for each entry in the book.

EDIT:

To further clarify why not to use:

CREATE NONCLUSTERED INDEX NC_EmpDep 
          ON Employee( EmployeeID, LastName)
INCLUDE (DepartmentID)

This index is only useful if you have either EmployeeID or BOTH EmployeeID and LastName in your WHERE clause. This is pretty much the OPPOSITE of what you need for this query.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.