patternsqlCritical
Hard and Fast rule for include columns in index
Viewed 0 times
fastcolumnsruleincludehardforandindex
Problem
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 :
The poster suggested to make index like this:
here comes my question why can't we make index like this
or
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?
and I found that for the following query :
SELECT EmployeeID, DepartmentID, LastName
FROM Employee
WHERE DepartmentID = 5The 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?
Solution
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
An index is typically
Where:
WHERE, JOIN, ORDER BY, GROUP BY etc
The index for this query would be
CREATE NONCLUSTERED INDEX NC_EmpDep
ON Employee(DepartmentID)
INCLUDE (Lastname, EmployeeID)An index is typically
CREATE INDEX ON (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
Code Snippets
CREATE NONCLUSTERED INDEX NC_EmpDep
ON Employee(DepartmentID)
INCLUDE (Lastname, EmployeeID)CREATE INDEX <name> ON <table> (KeyColList) INCLUDE (NonKeyColList)Context
StackExchange Database Administrators Q#12922, answer score: 51
Revisions (0)
No revisions yet.