SQL server-Focus where column= @Param OR @Param is null problematic?

Objective

In this section, we will continue to explain the problems in SQL Dynamic queries as we finish our SQL dynamic queries.

SQL Dynamic Query condition filtering filtering

When we create a stored procedure to call a stored procedure, filter if the filter condition has a value, no value returns the row record, similar to the following query:

WHEREORISNULL)

What is the problem with this query? Is there a problem with performance, which we need to delve into in this section.

Next we create the following test table and insert the test data as follows:

CREATETABLE Test    (      INTNOTNULL ,      INTNOTNULL    INSERT  Test        SELECT  number ,                low        FROM    master..spt_values        WHERE   TYPE'p'  CREATEINDEXON Test(Somecol2)GO

For dynamic SQL condition filter filtering we use where 1 = to stitch. Next we use generic SQL statements and dynamic queries and compare their IO, as follows:

SET STATISTICSIo onGODECLARE@colINTSELECT@col =1 SELECTSomeCol2 fromTestWHERE 1=1 and([email protected]OR@col is NULL) GODECLARE@colINTSELECT@col =1 DECLARE@SQLNVARCHAR(4000)SET@SQL =' SELECT SomeCol2From TestWHERE 1 =1 ' IF@col is  not NULL     SET@SQL = @SQL +' and [email protected] '    EXECsp_executesql @SQL, N' @InnerParamcol INT ', @colSET STATISTICSIoOFFGO

We can see that the dynamic SQL query logical reads are read only 2 times, while the general SQL statement query logic reads 7 times, and we see that the SQL Dynamic query plan performs an index lookup, whereas a generic SQL statement is an index scan.

It seems that the execution of general SQL statements do not go index lookup, will lead to performance problems, at the beginning we talked about the filter condition has value is filtered, no value query all data, then we can use IsNull to query, the following we used IsNull to change the general sentence filter conditions, to see if we will go index search?

SETSTATISTICSONDECLAREINTSELECT1SELECT SomeCol2FROM dbo.TestWHERE11AND  SomeCol2 = ISNULL(@col,SomeCol2)

We see that the results are still going through the index scan without any change. Is there no way out? Let's change the general SQL statement query method as follows:

DECLAREINTSELECT1IFISNULL    SELECT  SomeCol2    FROM    Test    WHERE   11ELSE    SELECT  SomeCol2    FROM    dbo.Test    WHERE   11            AND SomeCol2 = @colGO

As above can only be barely solved the problem, because only for one parameter, if there are more than one parameter to do if ... ELSE: That would be silly. To solve this problem in essence, we need to recompile with optional options. As follows:

SET STATISTICSIo onGODECLARE@colINTSELECT@col =1 SELECTSomeCol2 fromDbo. TestWHERE 1=1 and(SomeCol2 = @colOR@col is NULL)OPTION(RECOMPILE) GODECLARE@colINTSELECT@col =1 DECLARE@SQLNVARCHAR(4000)SET@SQL =' SELECT SomeCol2From dbo. TestWHERE 1 =1 ' IF@col is  not NULL     SET@SQL = @SQL +' and SomeCol2 = @InnerParamcol '            EXECsp_executesql @SQL, N' @InnerParamcol INT ', @colSET STATISTICSIoOFFGO

Summarize

When filter data is filtered using conditional filtering, all row records are returned if the condition has a value. If you execute a generic SQL statement and dynamic SQL, then dynamic SQL will go through the index lookup, and the generic SQL statement will result in an index scan, which requires option (RECOMPILE) to go through the index lookup.

SQL server-Focus where [email protected] OR @Param is null problematic?