Question on using indices

[Wrathchild]

Hi, I'm scripting about 100 tables for a database and had a question on proper index usage. If I have several queries that have three fields in various places (WHERE clause, JOIN clause etc) then is there a difference between setting up the index with the fields in one line or multiple lines?

Example:
create nonclustered index IX_tablename on dbo.tablename (employee_id,claim_number,case_worker)

VS.

create nonclustered index IX_tablename1 on dbo.tablename (employee_id)
create nonclustered index IX_tablename2 on dbo.tablename (claim_number)
create nonclustered index IX_tablename3 on dbo.tablename (case_worker)

My main question being if I set them up together as in the first example, do I only benefit if all 3 fields are used together in the query? I'm not looking for maximum efficiency here, the db is already pretty hacked, but I would like to understand doing it one way vs. the other. I like setting them all together as it's cleaner and fewer objects.
thx!

 

[mrdenny]

Yes, 99% of the time SQL Server will only use a single index per table per query. When you've got a column in the WHERE clause and another in the JOIN clause they'll need to be in the same index.

An index can be used if only the first or first and second columns are used in the query just the same if all three are in the query.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / SQL 2005 BI / SQL 2008 DBA / SQL 2008 DBD / SQL 2008 BI / MWSS 3.0: Configuration / MOSS 2007: Configuration)
MCITP (SQL 2005 DBA / SQL 2008 DBA / SQL 2005 DBD / SQL 2008 DBD / SQL 2005 BI / SQL 2008 BI)
MCM (SQL 2008)
MVP

My Blog

 

[gmmastros]

If you have a table with the only index on the 3 columns,

[tt][blue]create nonclustered index IX_tablename on dbo.tablename (employee_id,claim_number,case_worker)[/blue][/tt]

Then some queries may be slow, like this one.

Select Column From TableName Where Claim_Number = 1

When thinking about indexes, it often helps to remember that the data for the columns is actually in the index and the data is sorted based on the column order you specify. In the example above, the data in the index is sorted first on employee_id, then on claim_number, and finally on case_worker.

Your example is VERY much like a phone book, where the data is sorted first by last_name, then by first_name, and finally by phone_number. If I handed you a standard phone book and said, give me the phone numbers for everyone named George... you would have to start at the first entry and examine each entry to find those that match (this is the equivalent of a table/index scan).

If you had 2 books, one that was sorted by last_name, first_name, phone_number and another sorted by first_name, last_name, phone_number and I told you... give me all the phone numbers for everyone named George, what would you do? You would first determine that the book you should use is the one that is sorted by first_name. Then you would quickly skip through to find the first George entry, skip through again to find the last, and then you would be done.

If an index has more columns than you need in it, SQL Server may still decide to use it, as long as it determines that it is useful. For example, if I told you to find all entries in a phone book with last name = "Obama", does it matter that there is a secondary sort order on first name? No, you would still get the same results.

Bottom line... having multi-column indexes are often times beneficial to the execution of a query. In your situation, removing the 3 single-column indexes may be a mistake because it may slow down other queries.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom