Composite indexes – Order of the columns is vital for optimizer
A composite index is an index that is made up of more than one column. When you create a composite index, the order of the columns of the key is important. Try to order the columns in the key to enhance selectivity, with the most selective columns to the leftmost of the key. If not and put a non-selective column as the first part of the key, you risk having the Query Optimizer not use the index at all. In generally, a column should be at least 95% unique in order for it to be considered selective.
An index is automatically created for column(s) in your table that you specify as a PRIMARY KEY or as UNIQUE. If two or more columns are involved, and a composite index is created, you should choose how the columns will be ordered in the composite index, instead of accepting the default choices offered by SQL Server. This is because you always want to use the most selective columns at the left of the key to ensure that the composite index is selective enough to be used by the Query Optimizer. If you don't do this, then the default order of the columns in the composite index may not be very selective, and the index may not be used by Query Optimizer.
A composite index is generally only useful to a query if the WHERE clause of the query matches the column(s) that are leftmost in the index. So if you create a composite index, such as "City, State", then a query such as "WHERE City = 'Bangalore'" will use the index,
but the query "WHERE STATE = 'Karnataka'" will not use the index.
Let's consider an example. Let's assume you have a Customers table as described below.
CREATE TABLE Customer
Customer_ID INT NOT NULL IDENTITY(1,1)
,Last_Name VARCHAR(20) NOT NULL
,First_Name VARCHAR(20) NOT NULL
,Email_Address VARCHAR(50) NULL
It has a composite index on the Last_Name, First_Name columns as shown below.
CREATE INDEX ix_Customer_Name ON Customer(Last_Name, First_Name)
To find a specific row, we could execute the following query.
WHERE Last_Name = 'Balu' AND First_Name = 'Nandha'
It should be pretty obvious that the ix_Customer_Name index would work well to satisfy this query. A quick look at the execution plan confirms our expectations
Now, let's broaden our search a bit to retrieve all customers whose last name is 'Balu'. The following query may be executed for this.
WHERE Last_Name = 'Balu'
Looking at the query execution plan, we can see that SQL Server did indeed use the ix_Customer_Name composite index; it performed an index seek to find the rows that satisfied the query, and then it used a Key Lookup to retrieve the non-indexed column information.
Now, let's see what happens if we need to find all people who have a first name of Nandha.
Let's execute the following query.
WHERE First_Name = 'Nandha'
This yields the following query execution plan
Notice that this time, SQL Server used a Clustered Index Scan to resolve the query, It did not use our ix_Customer_Name index
Follow these guidelines for ordering keys in composite indexes:
•Create the index so the keys used in WHERE clauses make up a leading portion.
•If some keys are used in WHERE clauses more frequently, then be sure to create the index so that the more frequently selected keys make up a leading portion to allow the statements that use only these keys to use the index.
•If all keys are used in WHERE clauses equally often, then ordering these keys from most selective to least selective in the CREATE INDEX statement best improves query performance.
•If all keys are used in the WHERE clauses equally often but the data is physically ordered on one of the keys, then place that key first in the composite index.
Even if the WHERE clause in a query does not specify the first column of an available index (which normally disqualifies the index from being used), if the index is a composite index and contains all of the columns referenced in the query, the query optimizer can still use the index, because the index is a covering index.