Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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 could 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?

like image 643
Omu Avatar asked Aug 18 '10 08:08

Omu


People also ask

Can we compare date with NULL values in SQL?

If something is NULL it is undefined in T-SQL, so you cannot compare with it.

Is NULL for bit in SQL?

IS NULL and IS NOT NULL are predicates that can be used in conditional expressions. That means that they don't return a value themselves. Therefore, they can't be "cast" to a bit , or treated as a boolean.

Can you compare to NULL?

You can't compare with NULL. You need is. null to test if something is a reference to the NULL object.

Can we compare NULL with NULL?

SQL Server IS NULL / IS NOT NULL Because the NULL value cannot be equal or unequal to any value, you cannot perform any comparison on this value by using operators such as '=' or '<>'.


1 Answers

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.

like image 93
Martin Smith Avatar answered Nov 12 '22 15:11

Martin Smith