Dynamic WHERE and ORDER BY Without Dynamic SQL

My friend Jeremy (no blog) pointed me to these two useful articles on how to perform dynamic WHERE clauses and ORDER BY clauses without using dynamic SQL. These were written long ago, but I had never thought to use COALESCE in this way. Very cool!

I will post them here so I can find them later.

UPDATE: This technique may not be as performant as hoped for. Marty in the comments noted that he saw table scans in using COALESCE in this way. Jeremy showed me an example that demonstrated that the execution plan changed from an index seek to an index scan when using COALESCE. As always, test, test, test before rolling this out.

Comments

11 responses

Marty Thompson • January 26th, 2006
We have been doing that at my company until recently. A problem we discovered is that using COALESCE or ISNULL will cause a full-table scan.

Ryan Rinaldi • January 26th, 2006
The COALESCE trick works wonders until a column supports NULLs and you didn't pass in a criteria for that column. i.e. the following clause:

WHERE FirstName = COALESCE(@firstName,FirstName)

would become

WHERE FirstName = FirstName

and wherever FirstName is NULL would be filtered out of the result set. Which may or may not be the behavior you want. 9 times out of 10 it isn't the behavior I want. You could try wrapping the left side in a coalesce also and adding another option to the right side...

WHERE COALESCE(FirstName, '') = COALESCE(@firstName, FirstName, '')

Just goes to show you that every solution just creates more problems! :)

Jeremy • January 26th, 2006
I just ran a test of my own, and it *does* appear that there is a difference between a WHERE clause using the COALESCE function vs. one that does a straight equals comparison.

For example, the execution plan for the following code shows that it is doing an index seek:

declare @userid int

SET @userid = 100848

select * from media where userid = @userid

However, the plan for the following code block show that it is doing a clustered index *scan* on the primary key field (mediaid):

declare @userid int

SET @userid = 100848

select * from media where userid = coalesce(@userid,userid)

So I expect that it will be somewhat slower than a straight equals comparison on a very large table. So I guess it won't be the ideal solution in all scenarios, but I think it's still quite handy for things like dynamic reporting on relatively small tables.

Haacked • January 26th, 2006
Yeah, an index scan isn't as bad as a full table scan.

Marty Thompson • January 26th, 2006
Yeah, sorry about that, I spoke incorrectly. It was an index scan that we were having problems with as well, and it was enough of a performance hit for our purposes that we had to do away with it.

We did have a problem with full table scans, but that's because I'm an idiot with indexes.

Haacked • January 26th, 2006
Thanks for the clarification!

Jon Galloway • February 6th, 2006
This article has a very exhaustive rundown on all the options and performance implications on just about every possible approach to dynamic WHERE and ORDER BY queries:

http://www.sommarskog.se/dyn-search.html

HJ • May 26th, 2007
The problem is that SQL sucks and the people that are set to handle SQL completely refuse to add anything useful to the standard. (Celko?)
What we need is for SQL to add dynamic where, dynamic order by in an easy and optmized way, we also need arrays, vectors whatever you want to call it.
It's time to mondernize SQL !

Tim Odell • December 12th, 2008
This looked like a promising straight forward solution. It seems odd there is not one best practice for dynamic sorting and ordering. Each solution: dynamic sql, case statements, or this solution all have issues.

Manuel • November 15th, 2010
This other approach does dynamic order by very well, it's more flexible since it allows several fields combinations and sort orders and doesn't have casting issues:
john-sheehan.com/...

Read More • June 7th, 2011
Thanks to Jon for the link he posted. It was an excellent source of many, many, many, and many more approaches to dynamic WHERE and ORDER BY queries. LOL I also have had problems with the full table scans. Am I doing something wrong or should I just stick with index scans? Thanks for all the help guys!
- Reid Moore