Clustered and Non clustered composite index

45 pts.
Tags:
Clustered Index
Database
Non-Clustered Index
I have a database that has a clustered composite index and 2 non clustered indexes on each of the columns used in the composite index. Doesn't it just need the clustered index?

Answer Wiki

Thanks. We'll let you know when a new response is added.

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,
birthDate datetime
);

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.

Discuss This Question:  

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following