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 https://stackoverflow.com/questions/1307990/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:

  ON Employee(EmployeeID, DepartmentID)
  INCLUDE (Lastname)

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

      ON Employee( EmployeeID, DepartmentID, LastName)


          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?

  • 4
    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, 2011 at 13:07
  • Ryk, personally I find this post helpful. Dec 7, 2012 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, 2014 at 15:55

4 Answers 4


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

  ON Employee(DepartmentID)
  INCLUDE (Lastname, EmployeeID)

An index is typically

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


  • KeyColList = Key columns = used for row restriction and processing
  • NonKeyColList = Non-key columns = used in SELECT and aggregation (e.g. SUM(col)) after selection/restriction
  • +1 - I agree (see my ans) that the sample indexes in OP are worthless for the query!
    – JNK
    May 31, 2011 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, 2011 at 13:18
  • 1
    @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, 2011 at 13:27
  • @gbn Do we really required EmployeeID in include list? As If we've a clustered index on EmployeeID Column and on top of this if we create a nonclustered index on DeptId column thus NonClustered index already has reference to clustering key which is included in NonClustered Index structure, including clustering key in INCLUDE list doesn't add any benefits. Aug 6, 2017 at 6:01
  • 1
    @ViswanathanIyer it won't be added twice though to the actual on-disk storage: SQL Server detects this. So it is not needed but it makes things clearer. However, we don't know of any clustered indexes in the question so it's safer to assume none.
    – gbn
    Aug 7, 2017 at 6:32

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)
  • 2
    +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, 2011 at 13:38
  • 1
    I absolutely agree - it's always better to be explicit, especially if it costs nothing!
    – Jim McLeod
    May 31, 2011 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, 2011 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, 2011 at 11:48
  • This is great answer which includes some of the effects described in this article: sqlperformance.com/2014/07/sql-indexes/… If your query changes then so do the requirements of your indexes. You might be better off with Jim's answer but you might fare better with @gbn answer.
    – John K. N.
    Nov 1, 2016 at 14:43

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

  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.


To further clarify why not to use:

          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.

  • @ajbeaven that is true, which is why the comment I put in the edit says you need EITHER employeeID or both columns.
    – JNK
    Jun 7, 2018 at 19:37
  • durr sorry misread :(
    – ajbeaven
    Jun 7, 2018 at 23:31

I think you might still be able to use the (employee_id, department_id) index, but you'd have to include a 'dummy' line in the where phrase, like: "employee_id = employee_id)

  • having an index on (employee_id, departemnent_id),
  • having to search / restrict only on a department_id
  • knowing it won't use the index since wrong order (or things have changed by now, and the following "trick" is no longer needed. I'm an "oldy"?).
  • Use the "old" tricK?

    select * from Employee emp
    where emp.employee_id = emp.employee_id
    and emp.department_id = 5

(So I'm not focusing on the include part here of Lastname, but on the yes/or not being used of key.)

Kind regards,


  • 2
    No, that is useless and not efficient. May 27, 2015 at 21:51
  • Specifically, it will still have to an index scan to search every employee id to find all the instances of department_id 5. If there's 1000 employees and 5 departments, SQL has to look through all 1000 employees to find all the rows for a particular department.
    – Mark Sowul
    Sep 30, 2019 at 14:26
  • Now consider the opposite case (index is on department_id, employee_id). Obviously it's easy to find a particular department now, but also note that to find a particular employee, SQL only has to scan through 5 departments to find all the rows for a particular employee.
    – Mark Sowul
    Sep 30, 2019 at 14:26

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.