It depends. Generally, you do not need a second, non-clustered index on the “first” column of a multi-column clustered index, unless that index is also being used to apply a “unique” constraint on that column. An index on the second column of a two-column clustered index may well make a big difference in performance in your system, and sometimes a separate index on the first column may improve some queries as well.
It often helps to think of a multi-column index as being a “one column” index on a string formed by concatenating the individual column values of the records.
For example, consider the following table:
table myNames (
firstName varchar(64) not null,
lastName varchar(64) not null,
Let’s assume we have a clustered index on (lastName, firstName).
If I want to find the birthdate(s) for a particular combination of lastName + firstName, the index works very well, but what if I want to find all the birthDates (and lastNames) for everyone named “Steve”? If I use a query like:
select * from myNames where firstName = ‘Steve’
the index isn’t going to help at all – this is similar to asking “where my clustered index is like ‘%Steve'”, which must look at every index record.
So, clearly, if we ever ask for information by firstName, a separate index on that column will help a lot.
Is a separate index on the first column that useful? That isn’t quite as obvious. Any query that includes a last name value could possibly use the clustered index. However, consider the following query:
select * from myNames where lastName like ‘Job%’ and firstName like ‘Steve%’.
This query MAY be able to run more efficiently on two separate indexes on the columns. For one thing, depending on the size and type of the data in the individual columns, a separate index on lastName may be physically much smaller than the multi-column index, and thus a range scan on the index may be much faster than a similar range scan on the multi-column index.
In general, a separate index on the first column of a clustered two-column index is not going to be needed, but *may* provide some better performance under some circumstances.