Our sites are getting pounded pretty hard so we're taking a look into optimizing some of our existing queries.
While looking into this we ran across several queries whose execution plan was about 4-5 times faster when a simple reference of the clustered index is in the query... for example
If this was the old query:
SELECT ...
FROM myTable
WHERE categoryID = @category
the following query would be 4 times faster according to the execution plan in SSMS:
SELECT ...
FROM myTable
WHERE categoryID = @category
AND lotID = lotID
We can't seem to make sense of how this would make the query faster. The clustered index is on lotID but since its doing a comparison against itself how is this helping?
seems pretty obvious to me
your first query is not covered by the clustered index while the second is since lotID is not in the WHERE clause of the first query
You might want to read SQL Server covering indexes to see how that all works
you also need to understand that a clustered index IS the data, all the data for a table is in the clustered index. when you create a non clustered index on table that has a clustered index then the non clustered index will have a pointer to the clustered index (since that is where the rest of the data is) unless you can satisfy your query completely by the non clustered index and in that case only the non clustered index will be used...I will stop rambling now
EDIT
I read AND lotID = @lotID NOT AND lotID = lotID
sometimes you can fake out a clustered index by doing where lotID >0 (picking the lowest number you have) and you will get a seek
So if your smallest lotID = 1 and you add AND lotID > 0
you could also see a seek instead of a scan, I demonstrate WHERE IndexValue > '' in this post Is an index seek always better or faster than an index scan?