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?
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.
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.
Tip: enter the query in SSMS and hit CTRL-L to see the actual execution plan
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.
16 People are following this question.
