Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question

2 Answers 2

up vote 6 down vote accepted
  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

share|improve this answer

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

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

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

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.