SQL Server Performance Forum – Threads Archive

index tunning

hi every body
Just let me clear one dought. i have a table by name employee and having column a , b and c.
i have a clustured index on a column and non clustered long key index on (b,c). not please let me know in this query is it using the non clustered index or not. select * from employee where a = ‘john’ and b = ‘safd’ and c = ‘sadfsa’
please reply shiv gupta

---

we can only make an educated guess. You can find out for sure by viewing the execution plan, from query analyser or just do SET SHOWPLAN_TEXT ON
GO
select * from employee where a = ‘john’ and b = ‘safd’ and c = ‘sadfsa’
GO
SET SHOWPLAN_TEXT OFF
GO

---

If it were to use the NonClustered Index it would then have to take the results and lookup the rows in the clustered index. So my guess is that when you do as Chappy indicated you’ll like see that it will use the ClusteredIndex and then do a Filter (where) operation for fields b and c. But when in doubt, have at it with the showplan. It’s a beautiful thing.

---

thanx chappy , druer i gone through the show plan , it’s showing me about the clustered index seek ,nothing message about non clustered , does this means that it’s not using non clustered index. with regards
shiv gupta

---

Yes, that means that it’s not using non clustered.
What about non cluster on a,b,c?
Luis Martin
Moderator
SQL-Server-Performance.com One of the symptoms of an approaching nervous breakdown is the belief that one’s work is terribly important
Bertrand Russell
All postings are provided “AS IS” with no warranties for accuracy.

---

It is a good thing that it is using the clustered index. It immediately finds your rows based on field A, then just filters out any that don’t match B and C and voila you have the entire row of data since it is stored in the Clustered Index. If it were to use the Non-Clustered index it would have to turn around and go back to the clustered index and pull the actual data for the select clause.

---

hi every body as martin asked what happen of all columns have non clustered index , it’s going to use the non clustered index.
Thanx druer, i got your point.

---

This is better in case conditions on b and c are not significantly more selective. Without knowing data distribution you can’t know for sure.

---

hi mmarovic sorry it’s not cleared to me what you meant by data distribution here. Please make it more clear regards
shiv

---

Think of it like this: The index is like an ORDER BY clause in a query. Now think of how the columns are used in this ORDER BY clause, and how you would look up values in the rowset from that query. If you’re looking mostly at the second or third column, then you should probably use that as the first column.

---

Sorry for the late answer, somehow I’ve lost this thread. Now, the answer:
If 200 rows match the condition a = ‘john’ but there is only one match for: b = ‘safd’ and c = ‘sadfsa’ then non-clustered index would be more efficient.

---

]]>