Erik Porter's Blog

Life and Development at Microsoft and Other Technology Discussions

News

    Searchable Nullable Optional Fields in SQL

    Here's just a quick tip I came across (this is actually a good read on Whidbey Data Beta 2 features), which is pretty handy (and not so obvious) even if that's not what the article is really about.  If you have a field in a table that you need to search on, but the field allows nulls AND you want to be able to ignore the field completely, then check out this example:

    CREATE PROCEDURE dbo.FindCustomers

    (
         @FirstName varchar(50),
         @LastName varchar(50),
         @Email varchar(100),
         @Age tinyint
    )

    AS

    SELECT
         CustomerID,
         FirstName,
         LastName,
         Email,
         Age
    FROM
         Customers
    WHERE
         FirstName = ISNULL(@FirstName, FirstName) AND
         LastName = ISNULL(@LastName, LastName) AND
         Email = ISNULL(@Email, Email) AND
         Age = ISNULL(@Age, Age)
    ORDER BY
         LastName,
         FirstName

    Here, if we pass in an Age of 25, we get back all the Customers that are 25 years old.  However, if we pass in NULL, signifying that we don't want to search on that field at all, only the rows that do not have a NULL value for Age get returned, which is not what we want.  We want ALL rows to be returned regardless of Age.  Highlighted in bold is the change you'd need to fix it to work the way you want it to:

    CREATE PROCEDURE dbo.FindCustomers

    (
         @FirstName varchar(50),
         @LastName varchar(50),
         @Email varchar(100),
         @Age tinyint
    )

    AS

    SELECT
         CustomerID,
         FirstName,
         LastName,
         Email,
         Age
    FROM
         Customers
    WHERE
         FirstName = ISNULL(@FirstName, FirstName) AND
         LastName = ISNULL(@LastName, LastName) AND
         Email = ISNULL(@Email, Email) AND
         (Age = ISNULL(@Age, Age) OR (Age IS NULL AND @Age IS NULL))
    ORDER BY
         LastName,
         FirstName

    The problem (you probably already noticed) was that NULL can not be compared using the equal operator, only with the IS operator.  Age = NULL will always return False.  The above bolded changes takes care of this problem.  This is one of those simple things I just never thought of until I came across it in the above article...silly me...I'll never forget it now.  Hope this helps a few folks as well!  :)

    UPDATE: Read the comments below for more...

    Posted: Jan 21 2005, 12:58 PM by HumanCompiler | with 10 comment(s)
    Filed under: ,

    Comments

    Thomas Tomiczek said:

    Well, now we just need someone to tell the VB team and you how stupid this is, SQL wise. Unless they did some really major rework for the SQL Server query analyzer, and even then this is just plain stupid.

    See:

    ::Email = ISNULL(@Email, Email)

    Can NOT be evaluated without calculating ISNULL(@Email, Email). At least to my knowledge. Which means that...

    ...you have a table scan. Period. Performance in the dumps. The problem is that the second parameter is reflexive towards the content of the row, which means a table scan.

    Again, unless SQL Server's query analyzer has special code for this, which I doubt. And then you kill the other databases.

    Someone correct me here, but did this SP (and the original code from the VB net team article) not belong into the category you get fired for for incompetence? Forcing a table scan and killing performance, making all and any index totally useless?

    This is a very known problem that you can not handle this type of query in a SP without a LONG LONG LONG if statement (or by executing dynamic sql out of the query). In this case we talk of 8 different cases (three parameters - 2^3 = 8).

    That Microsoft starts writing code like this actually shows that the whidbey data tools are toys. This actually sounds like I should mention it in my blog - in the "Stupid ideas and Empoty Heads" category. Damn.

    Thanks for piounting this out, though - I did not realize hos ridiculous code MS is putting out the first time I went through the blog article you mentioned.
    # January 21, 2005 4:09 PM

    haacked@gmail.com (Haacked) said:

    Might want to look at COALESCE instead of ISNULL in some cases.
    http://odetocode.com/Blogs/scott/archive/2004/11/30/679.aspx
    # January 21, 2005 4:39 PM

    Luciano Evaristo Guerche said:

    What about the following?

    CREATE PROCEDURE dbo.FindCustomers
    (
    @FirstName varchar(50),
    @LastName varchar(50),
    @Email varchar(100),
    @Age tinyint
    )
    AS
    SELECT
    CustomerID,
    FirstName,
    LastName,
    Email,
    Age
    FROM
    Customers
    WHERE
    (@FirstName IS NULL OR FirstName = @FirstName) AND
    (@LastName IS NULL OR LastName = @LastName) AND
    (@Email IS NULL OR Email = @Email) AND
    ((@Age IS NULL AND Age IS NULL) OR Age = @Age)
    ORDER BY
    LastName,
    FirstName
    # January 21, 2005 4:53 PM

    Erik Porter said:

    Thomas,

    I will agree with you that splitting it out into tons of if statements would be the most performant way, however, there are a lot of factors that should determine when and when not to do that. I'm not sure what you're talking about with the table scans, isn't that what non clustered indexes were invented for?

    Phil (Haacked),

    True, I should've mentioned COALESCE, I've used it before. That link brings up something interesting I didn't know about COALESCE...thanks! It shouldn't be an issue for me since I always match the lengths of my parameters for SP's with the field's length, but it's definitely a good thing to keep in mind!

    Luciano,

    Yes, that could also work for the other parameters, but I believe that's pretty much what ISNULL would be doing anyway...I'm curious if there's any performance difference though...I'd have to play with it...thanks for that!
    # January 21, 2005 5:06 PM

    Thomas Tomiczek said:

    ::I'm not sure what you're talking about with
    ::the table scans, isn't that what non
    ::clustered indexes were invented for?

    Detailed explanation in my blog. http://spaces.msn.com/members/t.tomiczek
    # January 21, 2005 5:39 PM

    Thomas Tomiczek said:

    Ups, sorry.

    This is the right link:

    http://spaces.msn.com/members/ttomiczek/
    # January 21, 2005 5:40 PM

    TrackBack said:

    # January 21, 2005 6:42 PM

    Jon Galloway said:

    My favorite part:

    "Thanks for piounting this out, though - I did not realize hos ridiculous code MS is putting out the first time I went through the blog article you mentioned."

    If you're going to take the time to cut someone down, you might want check your spelling.

    It's also worth considering that a table scan against a 100 row table would not necessarily cause fatalities. Granted, table scans are to be avoided as a general practice, but depending on the amount of data and application requirements, this may be inconsequential.

    We must hope that Thomas Tomiczek and Frans Bouma never get in an argument, or the world's done for. ;-)
    # January 22, 2005 6:06 AM

    Thomas Tomiczek said:

    Well, John, this is all fine,

    But it is the ressponsibility of the author to point out what he actually does with his code, instead of falsely presenting this as god's solution for dynamic queries.

    If he would have behaved like a responsible person, which means pointing out that he knows nothing about SQL and that this solution ruins index usage and is thus not suitable for usage on a larger table, I would not have said a word.

    But he did not. He presented a trap that a lot of not too smart developers may then fall into, because he presented this as a solution.

    Which is irresponsible behavior.
    # January 22, 2005 9:48 AM

    Travis Laborde said:

    Erik, I hope you don't let a few arrogant mean spirited people stop you from posting. I've enjoyed your posts before, and at the same time, I've seen Thomas make a jerk of himself before too :) And Jon was right too... if you get Frans here you might just want to run for cover. These guys very seldom have anything nice to say.

    The other side of the coin of course is that he does make some valid points. It's just too bad that he can't make those points without being so mean. Personally I'd have much preferred to see it posted this way:

    Nice post, Erik! This is an example of one of those situations in database programming that has caused many people to go crazy looking for the best solution. You can use code like this, but for the simplicity of that code, you lose performance. Or, you can write lots and lots of more code, gaining performance, but also gaining a lot of work for yourself to maintain it. Or, you can use an ORM type tool, which tries to marry those two worlds. Perhaps you could look at xxx, yyy, and zzz.

    Now, didn't that essentially say the same thing Thomas wanted to say? Or at least, the only part of what he said that would have had any value? Sure Thomas, you hate Microsoft. We can see that. Please try to grow up.
    # January 22, 2005 3:57 PM