Sql-server – way to make a null check on a variable in a WHERE clause only occur once

nullsql server

I have a query on a large table that looks like this:

declare @myIdParam int = 1

select * 
from myTable
where (@myIdParam is null or myTable.Id = @myIdParam)

There are several similar conditionals like this in the where clause, and there are also a lot of joins, but this is a summary.

Effectively, if @myIdParam is null, we do not want to restrict the results using this parameter.

I am not a DB pro, but from my tests it seems like this NULL check is done for every record and not optimized out in any way.

If I remove the null check and assume the parameter is not null, the query returns instantly. Otherwise, it takes up to ten seconds.

Is there a way to optimize this so the check is done only once at runtime?

Best Answer

One way is to use dynamic SQL, using a null check to optionally add that part of the where clause.

declare @myIdParam int = 1
declare @vc_dynamicsql varchar(max)

set @vc_dynamicsql = 'select * from myTable where 1=1'

if @myIdParam is not null
    set @vc_dynamicsql = @vc_dynamicsql + ' and  myTable.Id = @myIdParam'

EXECUTE sp_executesql @vc_dynamicsql

Related Solutions

SQL Server – Quickly Change NULL Column to NOT NULL

@ypercube's answer does manage this partially as a metadata only change.

Adding the constraint with NOCHECK means that no rows will need to be read to verify it, and if you are starting from a position where the column does not contain NULL values (and if you know none will be added between checking and adding the constraint) then, as the constraint prevents NULL values being created from future INSERT or UPDATE operations, this will work.

Adding the constraint can still have an impact on concurrent transactions however. The ALTER TABLE will need to acquire a Sch-M lock first. Whilst it is waiting for this all other table accesses will be blocked as described here.

Once the Sch-M lock is acquired the operation should be pretty quick however.

One problem with this is that even if you know the column in fact has no NULLs the constraint is not trusted by the query optimiser which means that the plans can be sub optimal.

CREATE TABLE T (X INT NULL)

INSERT INTO T 
SELECT ROW_NUMBER() OVER (ORDER BY @@SPID)
FROM master..spt_values

ALTER TABLE T WITH NOCHECK
  ADD  CONSTRAINT X_NOT_NULL 
    CHECK (X IS NOT NULL) ; 

SELECT *
FROM T 
WHERE X NOT IN (SELECT X FROM T)

Plan

Compare this with the simpler

ALTER TABLE T ALTER COLUMN X INT NOT NULL

SELECT *
FROM T 
WHERE X NOT IN (SELECT X FROM T)

Plan

One possible problem you might encounter with altering the column definition in this way is that it not only needs to read all the rows to verify that they meet the condition but also can end up actually performing logged updates to the rows.

A possible half way house might be to add the check constraint WITH CHECK. This will be slower than WITH NOCHECK as it needs to read all rows but it does allow the query optimiser to give the simpler plan in the query above and it should avoid the possible logged updates issue.

SQL Server – Handling Null Parameter in Stored Procedure Where Clause

SELECT *
FROM Table1
WHERE Table1.URL LIKE '%' + @Parameter1 + '%' AND Table1.ID = @Parameter2
AND 
(
    @Parameter3 is null 
    or Table1.ID2 = @Parameter3
);

Take a look at the above example. If you change your AND clause to a nested OR clause specifying your initial expression as well as @Parameter3 is null. That will then demand that the nested expression is true if @Parameter3 is NULL.

Best Answer

Related Solutions

SQL Server – Quickly Change NULL Column to NOT NULL

SQL Server – Handling Null Parameter in Stored Procedure Where Clause

Related Question