In this section we will discuss about some other types of indexing i.e.Composite and Covering indexes.
An index that consists of more than one columns is referred as composite index. Both clustered and non-clustered indexes can be composite index. SQL 2005 and 2008 supports up to 16 columns in a
One needs to be careful while defining a composite index. The order of the columns are very important here. Lets see an example.
IF EXISTS (SELECT name FROM sys.indexes
WHERE name = N’IX_Employee_Composite’)
DROP INDEX IX_Employee_Composite ON [Employee];
CREATE INDEX IX_Employee_Composite
ON [Employee] (Emp_Code,Name);
In the above example we have defined a composite index ‘IX_Employee_Composite’ on Employee table. The columns that have been used are Emp_code,Name. Here the Emp_Code field is the leftmost in the index. The composite index plays the part if the where clause of the query matches the column(s) that are leftmost in the index.
So in the following queries, the composite index created above will be used.
SELECT * FROM EMPLOYEE WHERE Emp_Code = “A001”
SELECT * FROM EMPLOYEE WHERE Emp_Code = “A001” and Name = “ABC” and City = “DEL”
But the index will fail in the following queries
SELECT * FROM EMPLOYEE WHERE Name = “ABC” and EMP_Code = “A001” and City = “DEL”
SELECT * FROM EMPLOYEE WHERE Name = “ABC”
So the thumb rule is to order the columns in the key to enhance selectivity, with the most selective columns to the leftmost of the key otherwise the query optimizer will ignore the index at all.
Another approach is to split the composite index into multiple single column index. SQL Server has the ability to join two or more single indexes to make them use. However multiple single indexes are not always better than single composite indexes.