SQL Server 2005 Clustered Index Query Speed

Question

Our sites are very tense, so we are exploring some of our existing queries.

Studying this, we came across several queries, the execution plan of which was about 4-5 times faster when a simple help of the cluster index is in the query ... for example

If this was an old request:

SELECT ... FROM myTable WHERE categoryID = @category 

the following request will be 4 times faster according to the execution plan in SSMS:

 SELECT ... FROM myTable WHERE categoryID = @category AND lotID = lotID 

We cannot understand how this will speed up the request. The clustered index is on lotID, but since it does a comparison with itself, how does it help?

Answer 1

seems to me quite obvious

your first request is not covered by the clustered index, and the second because lotID is not in the WHERE clause of the first request

You might want to read SQL Server covering indexes to see how it works.

you also need to understand that a clustered index is data, all the data for the table is in the clustered index. when you create a non-clustered index in a table with a clustered index, the non-clustered index will have a pointer to the clustered index (since this is where the rest of the data is), if you cannot fully satisfy your query with the non-clustered index, in which case only nonclustered index ... now i will stop working

EDIT

I read AND lotID = @lotID NOT AND lotID = lotID

sometimes you can fake a clustered index by doing lotID> 0 (choose the lowest number you have) and you will get a search

So if your smallest lotID = 1 and you add AND lotID> 0

you can also see a search instead of a scan, I demonstrate WHERE IndexValue> '' in this post Is index search always better or faster than index scan?