I recently came across some SQL code that I was surprised to find. Sometimes I forget about some of the basic things I have learned over the years, and simply assume others know them as well. Well, that’s not necessarily true. There are always new people coming into the profession of software development, and they have their journey ahead of them. So, while this may be old hat to many of you, there are some who will read todays editorial and take heart.
Today I want to address the abuse of the first of two different SQL features. There first is the use of NULL, and the second is the storage of strings. Let’s look at null today. Tomorrow we can take a look at strings.
I found a query that wanted to find a record in a table by an integer value which allowed null. A parameter was used from a stored procedure, also allowing null. I’m going to call the field SomeNullableInt, and the parameter @SomeNullableInt. Here was how the query was written.
SELECT …
FROM SomeTable
WHERE ISNULL(SomeNullableInt, 0) =
CASE WHEN ISNULL(@SomeNullableInt, 0) = 0 THEN -1
ELSE @SomeNullableInt END
There are two things I don’t like about the query. The first is, if the parameter @SomeNullableInt IS NOT NULL, then you don’t really have to test and address it’s possible null value. In fact, this query sets it to something when it really isn’t necessary. Here’s why. You can’t compare NULL. You can test for IS NULL. But, you can’t use the Operation NULL = NULL. For this reason, there is no need to test for NULL in either the table column or the parameter. Here is how the query should be written.
SELECT …
FROM SomeTable
WHERE SomeNullableInt = @SomeNullableInt
If a row with SomeNullableInt is null, it won’t be returned, which is the intention of the query.
If the user specifies NULL as the value for @SomeNullableInt, then it will return no rows, which is, again, the intention of the query. All of the ISNULL magic provides no value. Worse yet, because there is an ISNULL(SomeNullableInt, 0) conversion on the table, it must perform the ISNULL function on each row in the table. Because it has to convert the table value, the query MUST execute a table scan. No index on this column can be used. If the table has many rows, this will degrade in performance over time. And, the extra load this query produces has no value.
The key here is to be aware that if you modify anything from a table used as part of your join or where clauses, it requires a table scan, which is a non-optimal process, to resolve your logic. You can manipulate variables, but avoid at all costs, the manipulation of any data being retrieved from a table, when you are using that value in a join or filter.
Cheers,
Ben