Is it faster to check that a Date is (not) NULL or compare a bit to 1/0?

I'm just wondering what is faster in SQL (specifically SQL Server).

I cou开发者_C百科ld have a nullable column of type Date and compare that to NULL, or I could have a non-nullable Date column and a separate bit column, and compare the bit column to 1/0.

Is the comparison to the bit column going to be faster?

---

In order to check that a column IS NULL SQL Server would actually just check a bit anyway. There is a NULL BITMAP stored for each row indicating whether each column contains a NULL or not.

---

I just did a simple test for this:

DECLARE @d DATETIME
        ,@b BIT = 0

SELECT 1
WHERE @d IS NULL

SELECT 2
WHERE @b = 0

The actual execution plan results show the computation as exactly the same cost relative to the batch.

Maybe someone can tear this apart, but to me it seems there's no difference.

---

MORE TESTS

SET DATEFORMAT ymd;

CREATE TABLE #datenulltest
(
    dteDate datetime NULL
)

CREATE TABLE #datebittest
(
    dteDate datetime NOT NULL,
    bitNull bit DEFAULT (1)
)

INSERT INTO #datenulltest ( dteDate )
SELECT  CASE WHEN CONVERT(bit, number % 2) = 1 THEN '2010-08-18' ELSE NULL END
FROM    master..spt_values

INSERT INTO #datebittest ( dteDate, bitNull )
SELECT  '2010-08-18', CASE WHEN CONVERT(bit, number % 2) = 1 THEN 0 ELSE 1 END
FROM    master..spt_values


SELECT  1
FROM    #datenulltest
WHERE   dteDate IS NULL

SELECT  2
FROM    #datebittest
WHERE   bitNull = CONVERT(bit, 1)


DROP TABLE #datenulltest
DROP TABLE #datebittest

dteDate IS NULL result:

bitNull = 1 result:

OK, so this extended test comes up with the same responses again.
We could do this all day - it would take some very complex query to find out which is faster on average.

---

All other things being equal, I would say the Bit would be faster because it is a "smaller" data type. However, if performance is very important here (and I assume it is because of the question) then you should always do testing, as there may be other factors such as indexes, caching that affect this.

It sounds like you are trying to decide on a datatype for field which will record whether an event X has happened or not. So, either a timestamp (when X happened) or just a Bit (1 if X happened, otherwise 0). In this case I would be tempted to go for the Date as it gives you more information (not only whether X happened, but also exactly when) which will most likely be useful in the future for reporting purposes. Only go against this if the minor performance gain really is more important.

---

Short answer, If you have only 1s and 0s something like bit-map index 1,0 is uber fast. Nulls are not indexed on certain sqlengines so 'is null' and 'not null' are slow. However, do think of the entity semantics before dishing this out. It is always better to have a semantic table definition, if you know what I mean.

The speed comes from ability to use indices and not from data size in this case.

Edit
Please refer to Martin Smith's answer. That makes more sense for sqlserver, I got carried away by oracle DB, my mistake here.

---

The bit will be faster as loading th bit to memory will load only 1 byte and loading the date will take 8 bytes. The comparison itself will take the same time, but the loading from the disk will take more time. Unless you use a very old server or need to load more then 10^8 rows you won't notice anything.