A question on SARGability

Question

I just need to confirm that I understand something correctly:

I recently viewed an SO question in which a user posted an answer in Linq like:

from p in db.table where p.column.AddMinutes(1) > DateTime.Now select p

To those unfamiliar with Linq, I would expect the output of that statement (not tested it in fairness) to be:

SELECT *
FROM table t
WHERE DATEADD(min, 1, t.column) >= GETDATE() 

I posted an reply to this saying that the datetime manipulation should be on the variable (in this case GETDATE()) so in fact the statement should reflect something like:

SELECT *
FROM table t
WHERE t.column >= DATEADD(min, -1, GETDATE())

In my reply, the bits i'm now unsure of, assume the following:

1. Indexes will not be used because of the manipulation of the column
2. The query plans will be different partly because of the above (not tested, assuming so)
3. Because of the above, the 1st query will actually perform worse than the 2nd.

My question:

Have I missed anything out in my reasoning? Am I correct? Lastly, does any body have any good articles on SARGability?

Answer 1

1. correct
2. not necessarily - it depends on whether a suitable index exists and whether the CBO decides to use it. For example if the table is small or the the statistics lead the optimizer to believe that the filter will be true for a large percentage of the results, it may consider the cost of a FTS to be lower
3. this is not guaranteed - it is even possible that the 2nd query will perform worse - but your reasoning is fundamentally sound. For example, in an edge case when a FTS would actually be faster, the CBO may choose an index scan instead based on its best guess of the cost - which is always only an estimate of course

A bit of googling turned up this SO question and this interesting looking article on SARGability

Answer 2

Jack's right, so I won't repeat that.

I'll just add some articles I kind of like regarding sargability of an expression:

• Sargability: Why %string% Is Slow - by (my all time favorite) Brent Ozar
• Low-Hanging Fruit of Sargable Expressions - by Dan Guzman
• SQL Server Transact-SQL WHERE - by Brad McGehee
• Sargable Predicates - by Jeff Wharton
• Indexing for ORs - by Gail Shaw

They're all great writers with lots of SQL Server experience.