CODEWITHSUNDEEP
sqlsql-serverindexing
hwilson1
hwilson1

Reputation: 499

Scan where I would expect to see seek

I have a table, Table, with two columns, PKColumn1 and Column2 in a SQL Server database. The table has a clustered index, clustered on PKColumn1.

If I use the following query against this table, I would expect the execution plan to show a Clustered Index Seek.

SELECT PKColumn1
FROM Table
WHERE PKColumn1 = 1

which it duly does.

If I use the following query against this table, I would also expect the execution plan to show a Clustered Index Seek.

DECLARE @PKColumn1 INT = 1;
SELECT PKColumn1
FROM Table
WHERE (PKColumn1 = @PKColumn1 OR @PKColumn1 IS NULL)

however I now see from the execution plan that the table was scanned.

Why is this?

Upvotes: 3

Views: 37

Answers (1)

M.Ali
M.Ali

Reputation: 69524

In your second query the problem is in your where clause:

WHERE (PKColumn1 = @PKColumn1 OR @PKColumn1 IS NULL).

SQL Server does not do any short circuiting (like in c# ||), which means even if the expression @PKColumn1 IS NULL evaluates to true, there is no guarantee that sql server will not evaluate the second expression PKColumn1 = @PKColumn1.

Solution:

The best way to handle such optional parameters is to use dynamic SQL and built the query on the fly. Something like.... 

DECLARE @PKColumn1 INT = 1
       ,@Sql NVARCHAR(MAX);

SET @Sql = N' SELECT PKColumn1
              FROM Table
              WHERE  1 = 1 '
         + CASE WHEN @PKColumn1 IS NOT NULL THEN
           N' AND PKColumn1 = @PKColumn1 ' ELSE N'' END

Exec sp_executesql @Sql
                  ,N'@PKColumn1 INT' 
                  ,@PKColumn1

Using sp_executesql will cache the parameterised execution plan. Which is usually more of an issue when you have more than 2 optional parameters.

Upvotes: 3

Related Questions