Using nullable columns in a WHERE clause

Question

Assume a table definition in SQL Server as follows:

CREATE TABLE MyTable (
  Id   UNIQUEIDENTIFIER NULL,
  Info VARCHAR(MAX)
)

And a query:

DECLARE @id UNIQUEIDENTIFIER
DECLARE @info VARCHAR(MAX)
IF @id IS NOT NULL
BEGIN
  SELECT @info = Info
    FROM MyTable
    WHERE Id = @id
END

In that case, the Visual Studio static code analyzer produces the following error:

Warning : SR0007 : Microsoft.Performance : Nullable columns can cause final results to be evaluated as NULL for the predicate.

I don't see the problem here. The error is related to performance; MSDN says I should use ISNULL() -- but an equals comparison against NULL is always false, right? Am I missing something, or is the warning just wrong?

[Ignore this warning. The recommendation is bad for performance](http://stackoverflow.com/questions/7471740/does-wrapping-nullable-columns-in-isnull-cause-table-scans) — Martin Smith, Jul 02 '13 at 11:58

score 4 · Accepted Answer · answered Dec 05 '09 at 13:03

4

I think it's referring to the WHERE clause. It's saying that both your parameter and your column can be NULL, in which case your WHERE clause no longer evaluates to true/false. By funneling your nullable column into one that always has a value defined (via ISNULL), you're in better shape, logic-wise.

Here's the Microsoft documentation on that error.

On the aside, NULLs supposedly make queries a skosh slower.

answered Dec 05 '09 at 13:03

Mark Canlas

9,385
5
41
63

Aha! It's where they are *both* NULL that it's trying to complain about--except if I rule that out with an IF or more details in the WHERE clause, the analyzer doesn't pick it up. I'm not sure what you mean about NULLs making queries slower. Do you mean a nullable column is slower than a non-nullable column? Or are you talking about ISNULL? – RickNZ Dec 05 '09 at 13:17
I'm alleging that a nullable column is slower to fetch/handle than a non-nullable column. But I have nothing to back that up with so take that with a nullable grain of salt. =D – Mark Canlas Dec 05 '09 at 13:19

score 1 · Answer 2 · answered Dec 05 '09 at 12:59

1

I think the analyzer might just not be taking into account your IF statement.

Your code seems correct to me.

answered Dec 05 '09 at 12:59

Adriaan Stander

162,879
31
289
284

The message complains about a nullable column, not a nullable parameter. Also, if I change the WHERE clause to be: `Id IS NOT NULL AND Id = @id` or `@id IS NOT NULL AND Id = @id`, the message persists. – RickNZ Dec 05 '09 at 13:08

score 1 · Answer 3 · answered Dec 05 '09 at 13:37

Null comparison depends on setup.

When SET ANSI_NULLS is ON, all comparisons against a null value evaluate to UNKNOWN

When SET ANSI_NULLS is ON, a SELECT statement that uses WHERE column_name = NULL returns zero rows even if there are null values in column_name. A SELECT statement that uses WHERE column_name <> NULL returns zero rows even if there are no nnull values in column_name.

When SET ANSI_NULLS is OFF, the Equals (=) and Not Equal To (<>) comparison operators do not follow the ISO standard.

This is from here.

score 1 · Answer 4 · answered Dec 05 '09 at 17:06

1

I think it's a spurious warning - can you suppress it on a case-by-case basis, or that particular warning completely?

What happens when you do this?:

CREATE TABLE MyTable (
  Id   UNIQUEIDENTIFIER NOT NULL,
  Info VARCHAR(MAX)
)

answered Dec 05 '09 at 17:06

Cade Roux

88,164
40
182
265

Changing it to NOT NULL eliminates the warning. I will probably end up suppressing the message; I just want to make sure I fully understand why it's happening first, and to report it if it's actually a bug. – RickNZ Dec 06 '09 at 03:02

score -2 · Answer 5 · answered Dec 05 '09 at 12:47

-2

IF @id IS NOT NULL

should be replaced with

IF ISNull(@id, -1) <> -1

answered Dec 05 '09 at 12:47

Raj

1,742
1
12
17

@Raj Isn't that exactly the same as "If @id is not null"? what's the point of changing it? – dan Dec 05 '09 at 12:52
That code actually won't even compile, because @id is a GUID, not an INT. – RickNZ Dec 05 '09 at 13:03
oops, I thought id was an integer, sorry. – Raj Dec 05 '09 at 13:22

score -3 · Answer 6 · answered Dec 05 '09 at 13:03

-3

@ Raj: "IF ISNull(@id, -1) <> -1"

I would not do it as it actually replaces the table entry

answered Dec 05 '09 at 13:03

ram

11,468
16
63
89

1

but unfortunately am new and do not have enough points (or reputations) to comment :( http://stackoverflow.com/faq – ram Dec 05 '09 at 18:12

Using nullable columns in a WHERE clause

6 Answers6