June 22, 2009 at 2:07 pm
Hi:
What’s the difference between having an index with multiple columns and having many indexes?
For example:
create nonclustered index idx_multy
on table_1(phone,billingname,city)
or
create nonclustered index idx_single_phone
on table_1(phone)
go
create nonclustered index idx_single_billingname
on table_1(billingname)
go
create nonclustered index idx_single_city
on table_1(city)
What’s the idea of having multiple columns in the same index and what are the benefits if any?
June 22, 2009 at 2:17 pm
In most cases, SQL will only use a single index per table to satisfy a query. So, let's say that there's a query with a where clause like this
WHERE phone = @Phone AND billingname = @NAme
If you've got a composite index (the one on 3 columns) SQL can do a seek on both columns and find the matching rows immediately. If it has two separate indexes, one on phone one on billingname then it will use one of them to do a single match, then have to go to the table to find the values of the other columns, then do a filter to remove rows that don't match.
This blog post may help a bit.
http://sqlinthewild.co.za/index.php/2009/01/19/index-columns-selectivity-and-equality-predicates/
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 22, 2009 at 2:47 pm
Great, thanks!
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply