Dynamic WHERE and ORDER BY Without Dynamic SQL

sql 0 comments suggest edit

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.

Found a typo or error? Suggest an edit! If accepted, your contribution is listed automatically here.

Comments

avatar

11 responses

  1. Avatar for Marty Thompson
    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.

  2. Avatar for Ryan Rinaldi
    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! :)

  3. Avatar for Jeremy
    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.

  4. Avatar for Haacked
    Haacked January 26th, 2006

    Yeah, an index scan isn't as bad as a full table scan.

  5. Avatar for Marty Thompson
    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.

  6. Avatar for Haacked
    Haacked January 26th, 2006

    Thanks for the clarification!

  7. Avatar for Jon Galloway
    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

  8. Avatar for HJ
    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 !

  9. Avatar for Tim Odell
    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.

  10. Avatar for Manuel
    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/...

  11. Avatar for Read More
    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