0

I need input on the below queries in terms of which one is better from performance perspective.

Query#1:

DECLARE @Var1 NVARCHAR(15) = NULL; -- This variable can have NULL or specific value

SELECT Col1, Col2, ... FROM dbo.Table1 WHERE Col1 = ISNULL(@Var1, Col1);

Query#2:

DECLARE @Var1 NVARCHAR(15) = NULL; -- This variable can have NULL or specific value

SELECT Col1, Col2, ... FROM dbo.Table1 WHERE (@Var1 IS NULL OR Col1 = @Var1);
1
  • Can Col1 be NULL?
    – Philᵀᴹ
    Aug 24, 2019 at 21:49

1 Answer 1

2

The short answer is that they're both equally bad. Neither one is SARGable. Wrapping columns in functions, or applying optional parameters can harm performance, prevent the efficient use of indexes, etc.

The one upside of the second query is that you can apply a RECOMPILE hint to get around some performance issues:

SELECT Col1, Col2, ... 
FROM dbo.Table1 
WHERE (@Var1 IS NULL OR Col1 = @Var1)
OPTION(RECOMPILE);

But you may introduce overhead with plan creation if the actual query is more complex and has a larger execution plan, or if it executes frequently (think hundreds or thousands of times a minute).

The typical solution is to use dynamic SQL:

DECLARE @Var1 NVARCHAR(15) = NULL; -- This variable can have NULL or specific value
DECLARE @SQL NVARCHAR(MAX) = N''

SET @SQL = @SQL + N'SELECT Col1, Col2, ... FROM dbo.Table1 WHERE 1 = 1' 
IF @Var1 IS NOT NULL
    BEGIN
        SET @SQL += ' AND Col1 = @iVar1'
    END;

EXEC sys.sp_executesql @SQL, N'@iVar1 NVARCHAR(15)', @iVar1 = @Var1;

This is written safely to prevent SQL injection.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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