Sql-server – Hard and Fast rule for include columns in index

indexsql serversql-server-2005sql-server-2008

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:

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?

Best Answer

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
Related Question