What is the difference between composite non clustered index and covering index

Question

SQL Server 2005 includes "covering index" feature which allows us to select more than one non key column to be included to the existing non clustered index.

For example, I have the following columns:

EmployeeID, DepartmentID, DesignationID, BranchID

Here are two scenarios:

• EmployeeID is a primary key with clustered index and the remaining columns (DepartmentID, DesignationID, BranchID) are taken as non clustered index (composite index).

• EmployeeID is a primary key with clustered index and DepartmentID is non clustered index with DesignationID, BranchID are "included columns" for non clustered index.

What is the difference between the above two? If both are same what's new to introduce "Covering Index" concept?

Answer 1

The difference is that if there are two rows with the same DepartmentID in the first index they will be sorted based on their values of DesignationID and BranchID. In the second case they will not be sorted relative to each other and could appear in any order in the index.

In terms of what this means to your application:

• A query which can use an index on (DepartmentID, DesignationID) can be more efficient with the first query than the second.
• Building the first index may take slightly longer because of the extra sorting required.