Today I wanted to look at the problem of using fixed string length data types, as a follow on to some SQL mistakes folks make. However, there was some confusion (and inaccuracies) in my example from yesterday, that I want to take a step back and clarify. I had to create a bogus query example in order to protect confidentiality, and I didn’t get it quite right. So, I’ll return to that today with some better examples and clarification, and then look at fixed length data types on Monday. Here is a better example of how the bad query was written to handle null values as an input parameter, or in the data of a table.
SELECT …
FROM SomeTable
WHERE ISNULL(SomeNullableInt, 0) =
CASE WHEN ISNULL(@SomeNullableInt, 0) = 0 THEN ISNULL(SomeNullableInt, 0)
ELSE @SomeNullableInt END
I don’t like this query primarily for the fact that it cannot use an index on the column SomeNullableInt for the table SomeTable. Since we may be searching on this value, it should have an index for performance reasons. As soon as you write “WHERE ISNULL(SomeNullableInt, 0) =”, the query analyzer will not use an index on this column. Worse yet, it will perform a table scan on the table.
A second thing I don’t like is that ISNULL([], 0) does not perform as well as [] IS NULL. So, just test of IS NULL if you care about it.
The reason they have this query is that they are trying to implement two different query methods. The first returns a specific record if the parameter has a value. The second returns all records, irrespective of the contents of SomeNullableInt being null or not, if the parameter is null.
Here is an easy query that returns both use cases without any null data conversion.
SELECT …
FROM SomeTable
WHERE SomeNullableInt = @SomeNullableInt
UNION ALL
SELECT …
FROM SomeTable
WHERE @SomeNullableInt IS NULL
By using UNION ALL there is no overhead for merging the data from both sets, which you won’t have anyway, because only one query returns data. This syntax separates the intention of each query, making it easier to read for someone who didn’t write the query. If you don’t like to do the UNION ALL technique, which really should have little overhead, you can put the different queries inside branching logic, and still have the separation of the queries, and the intent of each.
IF @SomeNullableInt IS NOT NULL
SELECT …
FROM SomeTable
WHERE SomeNullableInt = @SomeNullableInt
ELSE
SELECT …
FROM SomeTable
Probably the best solution would be to use OR in your where clause. This is a pretty basic problem we solve all the time. Developers should be able to understand the use of the OR operator.
SELECT …
FROM SomeTable
WHERE SomeNullableInt = @SomeNullableInt
OR @SomeNullableInt IS NULL
All three of these options will use an index on SomeNullableInt if a parameter is provided for @SomeNullableInt. If no parameter is provided, then you are performing a table scan anyway, and the use of an index is irrelevant. The biggest value you are going to get is the clear communication of what you are trying to accomplish. The syntax is much simpler, and performance when searching a single value will be greatly enhanced.
Cheers,
Ben