Ok, so in my last post, it turns out there are better ways to do some of what I was talking about. Unfortunately though, I have gotten a new "fan" who thinks I'm an idiot. I'm sure he's right about the fact that using ISNULL doesn't use an index. Good for you Thomas, I learned something new, thank you. But seriously, that blog entry was just about the worst presentation of correcting someone I have EVER seen. I mean even at the end of the entry he points out that I've written for a book, implying it's probably a crappy book because I posted something not so great. Are you out of your mind?!?! For one thing, the book is on VB, not SQL. And if you want to get personal, who in their right mind would make an entire category of their blog called "Stupid Ideas & Empty Heads"? Someone's got some issues! ;)
Do I come across as a "know it all expert of everything" or something? I use my blog to post things I learn from day to day in hoping of helping other people and also use it to learn things myself. Isn't teaching a learning experience too? I don't know everything. I'm somehow guessing you don't either, Thomas! ;)
Did you notice how Luciano actually corrected me on the same thing you did, except he did it in a more presentable manner? I actually wanted to listen to him and learn from him. Is this how you "teach" as well?
Seriously, you need you settle down. There's a lot more to coding that being right. Did you ask me if the table I was dealing with was a big one? Did you ask what sort of hardware it's running on? Maybe saving 0.0001 seconds off of query isn't important to me because my users won't notice it. Or did you ask how many users will be accessing this query simultaneously? Performance is a very important factor to writing software, but it's not the only one and there's many different ways to do things. If there weren't, there would be no creativity and we'd all be paid really low salaries to sit behind a desk and type exactly what we've been taught is the right way to do things all day.
So to summarize, Thomas is absolutely right about the technical details of his point, just ridiculously flew off the handle making me (and probably some other people) not even want to listen to him. By the way, Thomas, speaking of details, you spelled my name wrong.
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...
As
pointed out by
Mike Krisher! How exciting is that? ;) Seriously though, Mike has some good points about it being more our city's leaders than the actual people (if there's some accuracy in it at all of course). I will admit I've met some pretty dumb people in this town, but I've also met a ton of really smart people! To my defense, I've only lived in this town since September. Although, I grew up not 25 miles from here! ;)