1

I have a stored procedure that has a few lines:

AND ( @StartDate IS NULL OR @StartDate <= r.ReferralDate )
AND ( @EndDate IS NULL OR @EndDate >= r.ReferralDate )

Should it be re-written as:

AND ( r.ReferralDate >= @StartDate or @Startdate IS NULL  )
AND ( r.ReferralDate <= @EndDate or @EndDate IS NULL )

I tried it both ways and looking at the execution plans. There is a small difference in estimated number of rows, but other than that I do not see a change, so I assumed that the order in the statement did not matter, but was hoping someone could verify.

  • If you add a MCVE we can go deeper, but in this case the order won't affect the sargability - as you seem to already have known. If you're curious about other differences between the two, you'll need to provide a more complete example and may want to more accurately describe what your question is. – LowlyDBA Jul 1 at 20:02
5

The short answer is that the order you put these in makes no difference to SARGability. If you want this to be as efficient as possible, you can add a RECOMPILE hint, or use dynamic SQL to generate the appropriate WHERE clause.

Since you seem to understand the concepts involved, I won't beat any dead horses here, I'll just point you to a great source on the subject: Dynamic Search Conditions in T‑SQL

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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