I have had a couple of questions about Assignment 6, so here is an explanation of the answer. Just to refresh our memory, here was the assignment:
Imagine you have the following query that is performing poorly:
SELECT e.EmployeeID, e.ManagerID, c.FirstName, c.LastName, e.Title
FROM HumanResources.Employee AS e
INNER JOIN Person.Contact AS c
ON e.ContactID = c.ContactID
WHERE e.EmployeeID = 5
By looking at the graphical execution plan, you notice that SQL Server 2008 is doing a table scan on the HumanResources.Employee table.
1. What might you do to correct this?
2. If you want to create a "covering", non-clustered index for the query above, how would you do that?
The query uses the sample AdventureWorks database, although you will not get a table scan with the indexes that AdventureWorks has out of the box.
Please submit one sql script that answers both questions above. Put your ideas for part one in the script as T-SQL comments, and then create a T-SQL script to create an index for part two.
The question told you that the query is doing a table scan on the HumanResources.Employee table, which means that there probably is no clustered index on the HumanResources.Employee table, and possibly no indexes at all on the table. From the question, you can tell that I am only concerned with the HumanResources.Employee table. In order to have a “covering index”, you have to include ALL of the columns referenced in the query.
In this case (for the Employee table), we have EmployeeID in the WHERE clause (which is the most important column). We have ContactID used in the join condition, and then EmployeeID, ManagerID, and Title in the SELECT list. This means that you should create an index with EmployeeID, ContactID, ManagerID, and Title, probably in that order. This index would completely cover the query (for the Employee table).
If we had a clustered index on the table (probably on EmployeeID), you would not have to include that column in the non-clustered “covering index.”