Conor vs. more SARGable predicates

I got another question in the mail this week from another reader:


Hi Conor Thanks for an interesting blog. Maybe you have the answer to this question: Why I SQL Server having performance problems with these types of expressions: —————————————————————————————— DECLARE TABLE dbo.Archive( LastName varchar(50), Reference INT, SocialSecurityNumer VARCHAR(10) ) —————– DECLARE dbo.MySearch @Reference INT, @SSN VARCHAR(10) AS BEGIN SELECT * FROM dbo.Archive WHERE @SocialSecurityNumber IS NULL OR SocialSecurityNumber = @SSN AND @Reference IS NULL OR Reference = @Reference END —————————————————————————————— Or similarily WHERE SocialSecurityNumber = ISNULL(@SSN, @somethingelse) —————————————————————————————— So far I’ve been adapting to this reality. Erland Sommerskogs paper on this is great, and I’ve used all the tricks in book to make the performance acceptable. But why can it be so difficult to make get good execution plans with the above scenarios?


There are actually a lot of questions in here.  Let me try to tease out a few that I can answer.

1. Should I use table variables?

2. Why does OR block predicate sargability?

3. What’s up with ISNULL?


1. Should I use table variables?


Table variables are not fully supported by the optimizer’s reasoning model.  As such, they can cause performance problems when used in large queries with plan choices.  Generally, I tell customers to be very, very careful with them if they know what they are doing.  If they don’t know what they are doing, I would avoid them.  MSDN is doing some maintenance this morning, but Books Online has a Note that describes that these should not be used for anything “big” due to the lack of support here.  In the future, I am hopeful this situation can be improved.  Temp tables are the alternative to consider.


2. Why does OR block predicate sargability?


The logic that matches indexes needs to define a range.  OR (disjunctions) generally do not qualify as a single range.  There are a few things we can do in the optimizer, but overall you need to consider how to write your queries to get things to be SARGABLE.  Alternatively, you can add option (recompile) if this works for your application.  The optimizer will aggressively prune out OR branches for parameters (in recent releases) with that combination and this can make some predicates sargable with the query written with OR.


3. What’s up with ISNULL?


IsNull is a programming construct that is NOT the same as IS NULL.  IS NULL is fully understood by the optimize and is used in a number of spots.  ISNULL(), however, was really intended as a presentation concept that goes in the final SELECT list.  It should probably not be used in the WHERE clause because a number of functions, even built-in, are not supported in the logic that matches indexes.


Generally, I advise that you conceptually separate the “query” concepts from the “T-SQL” concepts (and most functions fall into the T-SQL bucket).  This will help you find areas where SQL Server may not generate an optimal plan.  Often, thinking about the problem in this manner will also help make sure that you isolate your business logic from your query logic a bit more cleanly as well.


I hope this helps you avoid problems when using the product in the future.




Comments (7)

  1. Michael says:

    I just hope there were some parenthesis in the WHERE cause that didn't get to you, or wouldn't the results probably differ significantly from what I assume they wanted. i.e:

    SELECT * FROM dbo.Archive WHERE (@SocialSecurityNumber IS NULL OR SocialSecurityNumber = @SSN) AND (@Reference IS NULL OR Reference = @Reference)

  2. Michael says:

    You say ISNULL is understood by the optimizer, does it understand COALESCE as well?

  3. Kalen Delaney says:


    I understood Conor to say that ISNULL (the function) is not understood, so it would stand to reason that COALESCE also is NOT understood. Conor said IS NULL (the comparison operator) is what is understood.

  4. Andy Irving says:

    aren't ISNULL and COALESCE handled differently internally? COALESCE gets expanded to a CASE statement, i believe.

  5. Henrik Fyhn says:

    Hi Conor

    Could you elaborate on your comment #2? Just to be clear on what problem we're talking about, pls look at Erland Sommarskog's very good article at

    Why is it so that you cannot get a good plan (in this context index seek) when you run into SARG'able expressions in combinations with the OR operator? I know you talk about ranges, but to me it sounds weird that it's so hard to come up with a plan  that works for all the different combinations. I know the principle of parameter sniffing, but still, why is the product having this limitation? Is it too complex to implement, has it not been prioritized, does the entire execution engine has to be rewritten to accomplish this? What is the problem?

    I have been rewriting a lot of queries relating to this problem.  I dislike the option(recompile) because it has a CPU and duration cost.  I dislike "forking" queries into different child stored procedures because it's messy. It would be much cooler if the optimize could be a bit smarter.

    Whenever I talk with portal developers who are designing a search page, I think "here we go again". I immediately thing "FAST". On one side it could be a selling point to introduce FAST to the customer, but on the other side I think these problems, ref Erlands site, should be within range for the optimizer in SQL Server. At least as we soon go into year 2011.

  6. Conor says:

    expressions combined with ANDs can be independently applied to indexes.  col > 1 AND col2 < 20 – each of those can be index matches and then the results combined.

    OR expressions does not have this property when you start combining arbitrary expression trees (ORs over ANDs).  While there are some cases where OR conditions can be applied to an index, in general they do not match indexes as easily.  Algorithmically, it is not possible to do all cases in reasonable amounts of compilation time.  As most people structure their code to get better query plans, this is one of those areas where you can structure your code to get better query plan performance.

    Could SQL Server do more?  Sure, but it already does a fair amount in this space compared to naive implementations and ultimately you get better performance gains by writing your queries to get into the good algorithmic code paths.  Ultimately, customers have been demanding other features more than working on this specific part of the code (there are a lot of feature requests on our product).

    I appreciate your feedback and we'll obviously consider ways to improve our index selection code the next time that we add functionality to the index matching code.  

  7. Mike says:

    I searched Books Online for the Note you describe, but couldn't find it:

    "Books Online has a Note that describes that these should not be used for anything “big” due to the lack of support here."

    Unless this is the note you're referring to:

    "Queries that modify table variables do not generate parallel query execution plans. Performance can be affected when very large table variables, or table variables in complex queries, are modified. In these situations, consider using temporary tables instead. For more information, see CREATE TABLE (Transact-SQL). Queries that read table variables without modifying them can still be parallelized."

    Two questions:  (1) Is this the link to the Note you describe (…/ms175010.aspx), which is the note I posted above?  (2) Can you please put a useful definition on the words "big" or "very large table variables" as used above?