Less Than Dot is a community of passionate IT professionals and enthusiasts dedicated to sharing technical knowledge, experience, and assistance. Inside you will find reference materials, interesting technical discussions, and expert tips and commentary. Once you register for an account you will have immediate access to the forums and all past articles and commentaries.

Your profile

Tools

Authors

Main Categories

Search

XML Feeds

RSS 2.0: Posts, Comments
Atom: Posts, Comments

What is RSS?

Google Ads

« Do you use disable index or drop index when running your ETL processes in SQL Server

MongoDB vs. SQL Server - INSERT comparison part deux »

comments

Do you use Column=@Param OR @Param IS NULL in your WHERE clause? Don't, it doesn't perform

by SQLDenis on Apr 15, 2010 in categories Data Modelling & Design, Database Programming, Microsoft SQL Server

Instapaper

You see this kind of question all the time in newsgroups/forums, someone wants to return all the rows if nothing is passed in or just the rows that match the variable when something is passed in. Usually someone will reply with a suggestion to do something like this

WHERE (SomeColumn=@col OR @col IS NULL)

The problem with that approach is that it doesn't perform well, let's take a look, first create this table

tsql

Line number Off | Hide | Select all

USE tempdb
GO
CREATE TABLE Test(SomeCol1 INT NOT NULL, Somecol2 INT NOT NULL)
INSERT Test
SELECT number,low FROM master..spt_values
WHERE TYPE = 'p'
CREATE INDEX ix_test ON Test(Somecol2)
GO

Here is the query that uses the method described before, I am using AND 1=1 so that this query will match the one I will show later

tsql

Line number Off | Hide | Select all

DECLARE @col INT
SELECT @col = 1
SELECT SomeCol2
FROM Test
WHERE 1 =1
AND (SomeCol2=@col OR @col IS NULL)

Here is the query using dynamic SQL

tsql

Line number Off | Hide | Select all

GO
DECLARE @col INT
SELECT @col = 1
DECLARE @SQL NVARCHAR(4000)
SET @SQL = 'SELECT SomeCol2
FROM Test
WHERE 1 =1'
IF @col IS NOT NULL
SET @SQL = @SQL + ' AND SomeCol2=@InnerParamcol '
EXEC sp_executesql @SQL,N'@InnerParamcol INT',@col

Now let's run these queries and take a look at the reads

tsql

Line number Off | Hide | Select all

SET STATISTICS IO ON
GO
DECLARE @col INT
SELECT @col = 1
SELECT SomeCol2
FROM Test
WHERE 1 =1
AND (SomeCol2=@col OR @col IS NULL)
GO
DECLARE @col INT
SELECT @col = 1
DECLARE @SQL NVARCHAR(4000)
SET @SQL = 'SELECT SomeCol2
FROM Test
WHERE 1 =1'
IF @col IS NOT NULL
SET @SQL = @SQL + ' AND SomeCol2=@InnerParamcol '
EXEC sp_executesql @SQL,N'@InnerParamcol INT',@col
SET STATISTICS IO OFF
GO

(8 row(s) affected)
Table 'Test'. Scan count 1, logical reads 6, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

(8 row(s) affected)
Table 'Test'. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

As you can see the dynamic SQL query only uses 2 reads where the other solution uses 6 reads.

Here is an image of the execution plan for both queries.

The execution plan show that the dynamic SQL is using a seek where the other query is using a scan

As you can see, there is a place for dynamic SQL and if you use it correctly you will also get plan reuse, take a look at the post Changing exec to sp_executesql doesn't provide any benefit if you are not using parameters correctly to find out how to use dynamic SQL correctly

[EDIT]

Someone on twitter suggested to try this query

tsql

Line number Off | Hide | Select all

DECLARE @col INT
SELECT @col = 1
SELECT SomeCol2
FROM Test
WHERE 1 =1
AND SomeCol2 = isnull(@col,SomeCol2)

That query also does an index scan.

Here is the execution plan in text for both of the queries that cause the scan

|--Index Scan(OBJECT:([tempdb].[dbo].[Test].[ix_test]),
WHERE:([tempdb].[dbo].[Test].[Somecol2]=isnull([@col],[tempdb].[dbo].[Test].[Somecol2])))

|--Index Scan(OBJECT:([tempdb].[dbo].[Test].[ix_test]),
WHERE:([tempdb].[dbo].[Test].[Somecol2]=[@col] OR [@col] IS NULL))

There was also a comment about recompiles, when you use sp_executesql you should not get recompiles when changing the value that you are passing in. I ran this query

tsql

Line number Off | Hide | Select all

DECLARE @col INT
SELECT @col = 1
DECLARE @SQL NVARCHAR(4000)
SET @SQL = 'SELECT SomeCol2
FROM Test
WHERE 1 =1'
IF @col IS NOT NULL
SET @SQL = @SQL + ' AND SomeCol2=@InnerParamcol '
EXEC SP_EXECUTESQL @SQL,N'@InnerParamcol INT',@col
Go
DECLARE @col INT
SELECT @col = 2
DECLARE @SQL NVARCHAR(4000)
SET @SQL = 'SELECT SomeCol2
FROM Test
WHERE 1 =1'
IF @col IS NOT NULL
SET @SQL = @SQL + ' AND SomeCol2=@InnerParamcol '
EXEC SP_EXECUTESQL @SQL,N'@InnerParamcol INT',@col
Go
DECLARE @col INT
SELECT @col = 3
DECLARE @SQL NVARCHAR(4000)
SET @SQL = 'SELECT SomeCol2
FROM Test
WHERE 1 =1'
IF @col IS NOT NULL
SET @SQL = @SQL + ' AND SomeCol2=@InnerParamcol '
EXEC SP_EXECUTESQL @SQL,N'@InnerParamcol INT',@col
Go

And then I ran a trace checking for SQL:StmtRecompile

Here is the output from that trace

[/EDIT]

*** Remember, if you have a SQL related question, try our Microsoft SQL Server Programming forum or our Microsoft SQL Server Admin forum

About the Author

Denis has been working with SQL Server since version 6.5. Although he worked as an ASP/JSP/ColdFusion developer before the dot com bust, he has been working exclusively as a database developer/architect since 2002. In addition to English, Denis is also fluent in Croatian and Dutch, but he can curse in many other languages and dialects (just ask the SQL optimizer) He lives in Princeton, NJ with his wife and three kids.

Social Sitings

Article Views: 45813 views

24 comments

Comment from: Ted Krueger (onpnt) [Member]

This is excellent advice! Personally can't stand it when I see the OR...OR....OR in statements.

04/15/10 @ 07:22

Comment from: Naomi Nosonovsky [Member]

Yes, also it is explained in here Catch All Queries

and in here
Dynamic Search query - great article by Elrand Sommarskog

Great suggestion you can find in this MSDN thread
How to optimize SQL for integer parameters

04/15/10 @ 08:06

Comment from: ca8msm [Member]

So what is the recommended alternative approach (without using dynamic sql)?

04/15/10 @ 12:27

Comment from: SQLDenis [Member]

The dynamic SQL with using sp_executesql and parameters (to prevent SQL injection) seems the cleanest/easiest to me

Otherwise you need to have a bunch of IF conditions, if you have 2 parameters it is pretty easy, if you have 8 or so it becomes a big pain in the neck

04/15/10 @ 12:32

Comment from: chopstik [Member]

Thanks, Denis. I've seen some examples of this and may have to point it out...

04/15/10 @ 19:52

Comment from: David Forck (thirster42) [Member]

can you provide an example of the dynamic query if you have, lets say 6, parameters?

04/16/10 @ 07:46

Comment from: SQLDenis [Member]

Here is an example with 2 parameters, 6 would be the same, just add if conditions and the parameters at the end

DECLARE @col INT
SELECT @col = 1

DECLARE @col2 INT
SELECT @col2 = 1

DECLARE @SQL NVARCHAR(4000)
SET @SQL = 'SELECT SomeCol2
FROM Test
WHERE 1 =1'

IF @col IS NOT NULL
SET @SQL = @SQL + ' AND SomeCol2=@InnerParamcol '

IF @col2 IS NOT NULL
SET @SQL = @SQL + ' AND SomeCol1=@InnerParamcol2 '

EXEC SP_EXECUTESQL @SQL,N'@InnerParamcol INT,@InnerParamcol2 INT',@col,@Col2

-------------------------------------------------------

You can also dump the parameters in a variable like this

DECLARE @col INT
SELECT @col = 1

DECLARE @col2 INT
SELECT @col2 = 1

DECLARE @SQL NVARCHAR(4000)
SET @SQL = 'SELECT SomeCol2
FROM Test
WHERE 1 =1'

IF @col IS NOT NULL
SET @SQL = @SQL + ' AND SomeCol2=@InnerParamcol '

IF @col2 IS NOT NULL
SET @SQL = @SQL + ' AND SomeCol1=@InnerParamcol2 '

--param definition
DECLARE @ParamDefinition NVARCHAR(500)
SELECT @ParamDefinition = N'@InnerParamcol INT,@InnerParamcol2 INT'

EXEC SP_EXECUTESQL @SQL,@ParamDefinition,@col,@Col2

04/16/10 @ 08:10

Comment from: Ivan [Visitor]

With the static approach you should add OPTION(RECOMPILE) at the end. This will equalize the speed to the dynamic approach.

Note:
This works for SQL Server 2008 RTM and SQL Server 2008 SP1 CU5 and on; it does not work for SQL Server 2008 SP1, nor does it do anything on SQL 2005!

04/17/10 @ 17:20

Comment from: Juliana [Visitor]

my question is about concurrency with dymanic queries, since procedures with dynamic queric inside are recompiled. Besides the reads are lesser the users are locked by other and the procedures take a long time. So, what aproach is better in this case?

04/17/10 @ 21:06

Comment from: SQLDenis [Member]

Juliana, Recompiles don't happen when using sp_executesql

start up profiler and check for SQL:StmtRecompile

I modified my blog post, see the added stuff

04/18/10 @ 05:29

Comment from: niikola [Member]

As usual - it depends...

I can show you bunch of situations when (= or is null) will have exactly the same execution plan as execution of dynamic queries.

It depends on mandatory search conditions (if any) and their combinations. And also depends if you already have some of highly selective condition (covered by index) inside query.

Dynamic queries have no advantage for columns not covered by indexes (in fact they have a small disadvantage as they need more execution plans)

As you already know SQL server will ignore index if it is not selective enough or if it is more costly to use it than to perform scan (full or range).

Making dynamic query for gender (as an extreme case) is pointless - except you have filtered indexes for males and females. The same is valid for all fields with low number of different values - you don't need dynamic query as it will not perform any better.

The problems with SQL Server are - you have to know your data and you have to know there's no universal solution. I'm using both approaches - dynamic and Equal/IsNull - and very often mix of them.

Using WITH RECOMPILE option could be good solution for static one - but only and only if you don't use that query very often.

04/19/10 @ 04:46

Comment from: SQLDenis [Member]

niikola, yes of course :-)

That is why I showed an example where an index seek would happen, if you have a multi column index and you search on the last column in that index then no, it doesn't matter since you will always get a scan

And BTW I did see an index once on a bit column, it was on gender :-)

04/19/10 @ 04:52

Comment from: niikola [Member]

That's why I like filtered indexes so much - primary because of gender column :-)

04/19/10 @ 04:57

Comment from: Jeff [Visitor]

Have you considered changing your proc to have 2 different select statements, one for each case, rather than use Dynamic SQL?

ie. instead of:
SELECT SomeCol2
FROM Test
WHERE 1 =1
AND (SomeCol2=@col OR @col IS NULL)

use:
if @Col is null
select SomeCol2 from Test where 1=1
else
select SomeCol2 from Test where 1=1 and SomeCol2=@col

04/19/10 @ 07:29

Comment from: SQLDenis [Member]

Jeff, yes for 1 parameter.variable I would do it like that. How would you do it for 2 or even 6 parameters? It becomes a pain and then if you want to add a column in the SELECT statement you have to add it in a lot of places. with dynamic SQL and 2 params, it would look like this

DECLARE @col INT
SELECT @col = 1

DECLARE @col2 INT
SELECT @col2 = 1

DECLARE @SQL NVARCHAR(4000)
SET @SQL = 'SELECT SomeCol2
FROM Test
WHERE 1 =1'

IF @col IS NOT NULL
SET @SQL = @SQL + ' AND SomeCol2=@InnerParamcol '

IF @col2 IS NOT NULL
SET @SQL = @SQL + ' AND SomeCol1=@InnerParamcol2 '

EXEC SP_EXECUTESQL @SQL,N'@InnerParamcol INT,@InnerParamcol2 INT',@col,@Col2

04/19/10 @ 07:47

Comment from: Terry [Visitor]

The twittered suggestion:



   1. DECLARE @col INT

   2. SELECT @col = 1

   3.  

   4. SELECT SomeCol2

   5. FROM Test

   6. WHERE 1 =1

   7. AND  SomeCol2 = isnull(@col,SomeCol2)

Does have a hidden gotcha in it. This works fine if SomeCol2 does not have any NULLS in it. If it has a NULL though, then this will not return those rows even if you want it to.

A question for others though. How would you be able to write dynamic queries that work within SQL functions?

Have a good day.

04/19/10 @ 08:07

Comment from: SQLDenis [Member]

Terry, you could do dynamic SQL in functions with a loopback query but I would not recommend it since it opens up a connection for every time the function is called

Here is an example, so basically you open up another connection to the SQL box and then do your dynamic SQL but I am not sure that you can even use sp_executesql like that

CREATE FUNCTION fnBla(@id int)
RETURNS int
AS
BEGIN
DECLARE @SQL varchar(500)
SELECT @SQL = 'osql -S' +@@servername +' -E -q "exec tempdb..prLog ''fnBla''"'
EXEC master..xp_cmdshell @SQL
RETURN @id
END

04/19/10 @ 08:26

Comment from: Naomi Nosonovsky [Member]

See also this thread MSDN Thread on the same topic.

Here is another blog on this topic Dynamic SQL

04/25/10 @ 00:56

Comment from: Andy [Visitor]

When you parameter is Null, but the param value is still passed to the SP_EXECUTESQL, it says tehre are too many arguments specified.

In the dynamicly built sql with parameter , how can the value list also be dynamically built?

For exameple if I have multiple parameter built in the following way.
IF @CC IS NOT NULL AND NOT @CC = 3
BEGIN
SET @SQL = @SQL + 'AND
-- Look for parameter value 1 on ReportLink table
( @CC1 = 1 AND (ReportLink.OrderingProvider IS NULL OR ReportLink.OrderingProvider =1 ))
-- Look for parameter value 2 on ReportLink table
OR( @CC1 = 2 AND ReportLink.OrderingProvider = 0)'
SET @PARAMS = @PARAMS + ',' + N'@CC1 INT'
SET @VALUES = @VALUES + ',' + N'@CC'
END

06/01/10 @ 20:39

Comment from: Naomi Nosonovsky [Member]

How about

where Field = case when @Param IS NULL then Field else @Param end

in terms of performance (it will not work for Field IS NULL).

06/07/10 @ 07:05

Comment from: Erik [Member]

Ooohhhh... I just thought up an interesting possibility for how to handle this:

SELECT *
FROM SomeTable
WHERE
SomeCol BETWEEN Coalesce(@somecol, 'a') AND Coalesce(@somecol, 'zzzzzzzz')
AND AnotherCol BETWEEN Coalesce(@anothercol, 0) AND Coalesce(@anothercol, 2147483647)

What do you think?

I just realized this is only viable for columns that have an index and that index is used. Otherwise it probably will force a scan...

10/18/10 @ 10:19

Comment from: Naomi Nosonovsky [Member]

For integer columns it's good (and recommended approach - see, for example, Relevant MSDN forum's thread

but I don't think you can do a-z for characters. What if you store ** or something else there?

11/05/10 @ 12:29

Comment from: Brad Schulz [Visitor]

· http://bradsruminations.blogspot.com

Just a followup...

Ivan already mentioned this in his comment over a year ago in April2010, but I'm surprised nobody remarked on it, because it is important:

Starting with SQL2008 SP1 CU5 (10.0.2746) or SQL2008R2 CU1 (10.50.1702), this kind of query IS optimizable, provided you have an OPTION (RECOMPILE) in the query.

So, for example, in using NorthWind...

DECLARE @customerid nchar(5)
,@orderdate datetime
,@employeeid int
,@orderid int
SET @customerid=N'CENTC'

SELECT OrderID,OrderDate,CustomerID,EmployeeID
FROM Orders
WHERE (CustomerID=@customerid or @customerid IS NULL)
AND (OrderID=@orderid or @orderid IS NULL)
AND (EmployeeID=@employeeid or @employeeid IS NULL)
AND (OrderDate=@orderdate or @orderdate IS NULL)
OPTION (RECOMPILE)

If you execute this (in the appropriate SQL2008 Version) and show the Actual Execution Plan, you will see that it DOES use the CustomerID nonclustered index to directly SEEK for the CustomerID in question... the other predicates on OrderID, EmployeeID, and OrderDate are thrown out the window by the Query Optimizer.

If you execute the same query in SQL2005, it will SCAN the index and perform a filter on the other predicates.

--Brad

06/09/11 @ 13:34

Comment from: Rishabh K [Visitor]

I think optimizer going for index scan is due to the fact that it doesn't know about the parameters at the query compilation so it is guessing(may be 30 % in this case).
Option Recompile will work here and using dynamic sql is like using force optimization ON.

03/15/12 @ 06:59

Comment feed for this post

LessThanDot

Your profile

Tools

Tag cloud

Authors

Main Categories

Search

XML Feeds

Google Ads

Do you use Column=@Param OR @Param IS NULL in your WHERE clause? Don't, it doesn't perform

About the Author

24 comments

Leave a comment

LessThanDot

LTD Social Sitings

Your profile

Tools

Tag cloud

Authors

Main Categories

Search

XML Feeds

Google Ads

Do you use Column=@Param OR @Param IS NULL in your WHERE clause? Don't, it doesn't perform

About the Author

24 comments

Leave a comment