August 29, 2009 at 8:50 am
select name , city from tables where id = 2
select * from tables where id = 2
Two indexes are created on table as following :
create index IC_Col1 on table(id) include (name, city)
create index IC_Col2 on table(id)
1. which of above index is used for
select name , city from tables where id = 2
2. create index IC_Col2 on table(id) can be used for both query ?
select name , city from tables where id = 2
select * from tables where id = 2
Is it require both index , or can create index IC_Col1 on table(id) include (name, city) server for both query statement?
August 29, 2009 at 9:14 am
Paresh Prajapati (8/29/2009)
Two indexes are created on table as following :create index IC_Col1 on table(id) include (name, city)
create index IC_Col2 on table(id)
1. which of above index is used for
select name , city from tables where id = 2
Almost certainly the first one, because it's a covering index. The query can be satisfied completely from the index without needing lookups to the cluster/heap.
2. create index IC_Col2 on table(id) can be used for both query ?
select name , city from tables where id = 2
select * from tables where id = 2
Yes. Both will be executed the same way, use the index to locate the rows, then lookup to the cluster/heap to find the remaining columns
Is it require both index , or can create index IC_Col1 on table(id) include (name, city) server for both query statement?
It's not required, the one on just ID is redundant if there's one on ID include (name, city). The query that does select * will have to do a lookup no matter which index exists, unless there are only 3 columns in the table (id, name, city)
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
August 29, 2009 at 1:40 pm
You can view the execution plan yourself in SSMS to see which indexes are used for the queries.
August 29, 2009 at 7:17 pm
... including columns indexes, would you believe my wife wrote a functional version of that - never-heard-about-at-that-time - technology during the late 80's running in a old-n-good Wang/VS system?
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply