I'm just wondering what is it faster in sql
can have a column of Date and to check it for null or to have a Date and a bit and to check the bit for 1/0
is the bit going to be faster ?
+1
A:
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.
Dani
2010-08-18 08:07:16
SQL Server will only load whole 8KB pages into and out of memory.
Martin Smith
2010-08-18 11:49:58
+2
A:
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.
barrylloyd
2010-08-18 08:07:43
+2
A:
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.
questzen
2010-08-18 08:13:30
The question is about SQL server. Nulls are indexed in SQL Server unless you choose to set up a filtered index to ignore them. Nulls are also represented in storage using a NULL BITMAP so I'm not sure there will be any difference.
Martin Smith
2010-08-18 11:52:12
Query optimizer determines the execution plan to be used based on several strategies. Dynamic sampling is one of them. If it decides that a direct table access (full table scan) fetch is efficient than index based fetch, it uses it. This is very true if the volume of data is small, but if there is a huge volume of data, the stats would differ.
questzen
2010-08-18 09:56:30
It's a tough one to measure, but if we consider that both scenarios will be fetching the data for the filter from the same table, most of the computations will execute in the same way. I can test this also, bear with me.
Codesleuth
2010-08-18 10:20:01
+7
A:
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.
Martin Smith
2010-08-18 11:46:20