Is WHERE field IS NULL quicker than >0

I have a number of Integer ID fields (SQL Server) that we are filtering on. I'm prepping to do an Insert of new rows. Is it quicker to use WHERE fieldname IS NOT NULL or could I use WHERE fieldId > 0 to get a list of existing Id values?

Solution 1:

SQL Server (as with any database) has a rather complex design of the data page. Here is a reference to more detail.

The idea, though, is that NULL flags are stored in a bitmap separate from the actual values. In order to read a value from the data page, the storage engine has to parse the NULL flag, regardless of what else it does. The value makes no sense without the NULL flag.

From this perspective, any comparison to a value requires both parsing the NULL flag and the retrieving the value. It is (theoretically) possible that the engine could only fetch the NULL flag if the value is not needed.

Your question is really: "Does SQL Server optimize IS NULL comparisons to only fetch the NULL flag from the bitmap and not the rest of the value?" I do not know the answer to this question.

However, for on-page data values, the difference between copying or not copying the extra four bytes would be really, really, really small. There are more important differences with off-page storage, so those might be optimized.

My best guess, based on my knowledge of the storage architecture, is that NULL value comparisons could have a very, very, very small optimization. I don't know if there really is a difference. In any case, the extra cycle or two of savings would probably not be noticeable.