I have a SP with a parameter that has NULL as default value and then I want to do a query like this:
SELECT ...
FROM ...
WHERE a.Blah = @Blah AND (a.VersionId = @VersionId OR (@VersionId IS NULL AND a.VersionId IS NULL));
The WHERE
above checks for both a non-NULL value and a NULL value for @VersionId
.
Would it be better in terms of performance to instead use an IF
statement and duplicate the query into one that searches for non-NULL and another for NULL like so? :
IF @VersionId IS NULL BEGIN
SELECT ...
FROM ...
WHERE a.Blah = @Blah AND a.VersionId IS NULL;
ELSE BEGIN
SELECT ...
FROM ...
WHERE a.Blah = @Blah AND a.VersionId = @VersionId;
END
Or the query optimizer makes it essentially the same?
UPDATE:
(Note: I am using SQL Server)
(And as far as I know, using a.VersionId = @VersionId
for both cases will not work, will it?)
Best Answer
This pattern
can be replaced with
This will let you match a NULL with a NULL and will allow the engine to use an index on
column
efficiently. For an excellent in-depth analysis of this technique, I refer you to Paul White's blog article:As there are two arguments in your particular case, you can use the same matching technique with
@Blah
– that way you will be able to rewrite the entire WHERE clause more or less concisely:This will work fast with an index on
(a.Blah, a.VersionId)
.In this case, yes. In all versions (at least) from SQL Server 2005 onward, the optimizer can recognize the pattern
col = @var OR (@var IS NULL AND col IS NULL)
and replace it with the properIS
comparison. This does rely on internal rewrite matching, so there may be more complex cases where this is not always reliable.In versions of SQL Server from 2008 SP1 CU5 inclusive, you also have the option of using the Parameter Embedding Optimization via
OPTION (RECOMPILE)
, where the runtime value of any parameter or variable is embedded in the query as a literal before compilation.So, at least to a large extent, in this case the choice is a matter of style, though the
INTERSECT
construction is undeniably compact and elegant.The following examples show the 'same' execution plan for each variation (literals versus variable references excluded):