SQL Server Performance

Using NULL to short-circuit

Discussion in 'T-SQL Performance Tuning for Developers' started by titojermaine, May 9, 2003.

  1. titojermaine New Member

    I need to create a sproc that allows for filtering on specific columns that can change based on the users desires. I do have a known and finite set of columns to use. Suppose that for each column there is a parameter that accepts a value or defaults to null. Is the following query WHERE clause below efficient?

    SELECT column1, column2, column3
    FROM tablename
    WHERE (@param1 IS NULL OR column1 = @param1) AND
    (@param2 IS NULL OR column2 = @param2) AND
    (@param3 IS NULL OR column3 = @param3)

    The idea is that the ORs will short-circuit before testing the column values. Also, will SQL Server's optimizer know that the parameters do not change value, so if they are null to start with, there is no reason to even evaluate the expressions? If not, is there some way to give a hint so that it will?

  2. Negative New Member

Share This Page