Sargability question – order of variables

Posted on

Question :

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.

Answer :

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

Leave a Reply

Your email address will not be published. Required fields are marked *