[SOLVED] Using nullable columns in a WHERE clause

Using nullable columns in a WHERE clause

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?

Solution

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.