Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
vijays3
Constraint Violating Yak Guru
354 Posts |
 Posted - 2012-04-12 : 17:41:21
|
| [code]Hi AllI have Clustered and non clustered indexes on my table CREATE CLUSTERED INDEX IDX ON Customers(Customer_ID)CREATE INDEX N_IDX1 ON Customers(Last_Name,First_Name)SELECT Customer_ID from Customers where Last_Name ='sahu'and First_Name= 'vijay'SELECT * FROM Customers WHERE Last_Name='sahu' AND First_Name = 'vijay'I am very confused about the execution plan which are generated by both of these.First query does : Index Seek(NonClustredIndex)Second Query Does: Clustered IndexScan(Clustered)Why such difference when there is difference in Only number of columns[/code] |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
vijays3
Constraint Violating Yak Guru
354 Posts |
Posted - 2012-04-12 : 18:02:55
|
| I updated my question : |
 |
|
|
LoztInSpace
Aged Yak Warrior
940 Posts |
Posted - 2012-04-13 : 03:03:26
|
| The first one can use only the NC index. N_IDX1 will have the ID in it anyway as a side-effect of ID being the CI key (CI key lives in all NC indexes).The second one will be all about the amount of IO required. It figures because it gets all the columns (or at least more than can be satisfied by the index alone) it has to use the tables. I'd say in this case you have so little data that it's decided the overhead of using the index outweighs the cost of scanning just the data blocks (CI). If you add more data to your table there will eventually come a point where the NC index becomes more efficient than the index scan and you'll see what you expect, i.e. you'll get the same as the first one.(disclaimer - I am not a query optimiser). |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
|
|
|
Execution Plan Behavior
So how many rows do you have in this table?Tara Kizer