Adi Cohn-120898 (12/17/2012)
I'm sorry but I don't see what it has to do with lookups. Notice that I had 2 queries. Both of them had the same criteria. The only difference between them was that one of them used variables that got there values at the same scope as the query and the other one used the values directly. The one that used it directly did use seek operator and the one that used variables used a table scan (of course both of them returned the same records). If this would have been to avoid lookups, then both queries should have acted the same.Also if I modify the queries and use dates that don't exist in the table (again for both queries) I still get index seek for the query that is not using the variables and table scan for query that is using the variables. This is because what I've explained before. The query plan is generated before runtime. The variables get there values during runtime, so when the server creates the query plan it has no idea about the values that will be used in the query so it estimates that 20% will be returned
Adi
Partially true. In the fixed-values version, the optimizer knows before-hand that the number of rows that will be returned is small enough to allow for a seek+key lookup.
Expand the date range in that version out to a year, so that the number of key lookups is more expensive than a scan, and you'll get a clustered index scan on it.
Again, it has nothing to do with the SARGability of it. It has to do with SQL Server's query optimizer deciding, based on the data presented to it, what method of resolving the query is the least expensive.
I tested various ranges, and I get a seek+lookup at 1 month, but a scan at 5 weeks. Took it narrower, and 31 days gets a seek (on your fixed-values version) while 32 gets a scan. Exact results will vary depending on the data in your version of the table, since generating the data off of checksums on NewID() is effectively random.
The variables version, SQL Server is smart enough to know that the values may change from run to run, so it builds an execution plan that can expand to cover a larger number of rows, where the key lookups might be too expensive. The fixed values version, it doesn't do that.
Nothing to do with whether the argument itself is SARGable.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon