question

JulieJiji avatar image
JulieJiji asked

Clustered or non clustered index will work if we have both indexes in a table

We have a table called Table1 and it has fields id,name,place,age. Clustered index created on Id and non clustered index created on name. select * from table1 where id=3 and name='Julie' Clustered or non clustered index will work here? how it will be? select * from table1 Will it use Index in this case?
indexingindexesclustered-indexnonclustered-index
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

The site runs off of voting. Please mark all helpful answers below by clicking on the thumbs up next to those answers. If any one answer lead to a solution, mark that answer by clicking on the check mark.
0 Likes 0 ·
Wilfred van Dijk avatar image
Wilfred van Dijk answered
The answer is .... "it depends" It depends on the data which is in this table, actually the uniqueness of the data. If there are a lot of "Julie" in the database, an index on name for searching "Julie" would not be the optimized path. (That's the reason why statistics are important for the optimizer) In this case, you're doing a "select *" also, which results in all columns, so I assume it will aways use the clustered index.
2 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Tip: enter the query in SSMS and hit CTRL-L to see the actual execution plan
1 Like 1 ·
Thank you Wilfred!!
0 Likes 0 ·
Grant Fritchey avatar image
Grant Fritchey answered
Since the clustered index stores all the data in the table, and since, at least based on this simple example, it looks like you're talking about a unique identifier being the clustered index, the second index is not likely to be used at all. But, let's modify things slightly and say that the ID column is not unique. Even then, there's a good chance that the second, nonclustered, index on the Name column won't be used because it's still returning all columns from the data in the clustered index, so it doesn't really need to go to the nonclustered index for the data. But, if we had a situation where you were only retrieving a few columns, and a combination of two nonclustered indexes could satisfy the needs of the query, you can see a thing called index intersection where two indexes are combined to retrieve the data. It's not terribly common. And, usually, you're better of ensuring that you have a good, useful, clustered index to retrieve the data most of the time since the clustered index stores the data.
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.