views:

344

answers:

6

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?

A: 
IF @id IS NOT NULL

should be replaced with

IF ISNull(@id, -1) <> -1
Raj
@Raj Isn't that exactly the same as "If @id is not null"?what's the point of changing it?
dan
That code actually won't even compile, because @id is a GUID, not an INT.
RickNZ
oops, I thought id was an integer, sorry.
Raj
+1  A: 

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

Your code seems correct to me.

astander
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
A: 

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

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

ram
Better to have this as a comment to @Raj's answer...
RickNZ
but unfortunately am new and do not have enough points (or reputations) to comment :( http://stackoverflow.com/faq
ram
+1  A: 

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.

Mark Canlas
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
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
A: 

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.

Damir Sudarevic
+1  A: 

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)
)
Cade Roux
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