SQL And NULL Values in where clause

Check out the full reference on Books Online - by default ANSI_NULLS is on meaning you'd need to use the approach you have done. Otherwise, you could switch that setting OFF at the start of the query to switch the behaviour round.

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 nonnull values in column_name.
...
When SET ANSI_NULLS is ON, all comparisons against a null value evaluate to UNKNOWN. When SET ANSI_NULLS is OFF, comparisons of all data against a null value evaluate to TRUE if the data value is NULL.

Here's a simple example to demonstrate the behaviour with regard to comparisons against NULL:

-- This will print TRUESET ANSI_NULLS OFF;IF NULL <> 'A'    PRINT 'TRUE'ELSE    PRINT 'FALSE'-- This will print FALSESET ANSI_NULLS ON;IF NULL <> 'A'    PRINT 'TRUE'ELSE    PRINT 'FALSE'

In general, you have to remember that NULL generally means UNKNOWN. That means if you say CategoryID <> '00000000-0000-0000-0000-000000000000' you have to assume that the query will only return values that it KNOWS will meet your criteria. Since there is a NULL (UNKNOWN) result, it does not actually know if that record meets your criteria and therefore will not be returned in the dataset.

Basically, a NULL is the absence of any value. So trying to compare the NULL in CategoryId to a varchar value in the query will always result in a false evaluation.

You might want to try using the COALESCE function, something like:

SELECT     ModelId, CategoryID FROM       Products WHERE      (ModelId = '010-00749-01')  AND        ( COALESCE( CategoryID, '' ) <> '00000000-0000-0000-0000-000000000000' ) 

EDIT

As noted by AdaTheDev the COALESCE function will negate any indices that may exist on the CategoryID column, which can affect the query plan and performance.