August 23, 2004 at 12:40 pm
I was recently reviewing the Books-on-Line relative to a project I am working on that deals with bit mapping.
In my research I came across the definition of a scalar bit data type. It says:
--------------------------------------------------------------------------
Integer data type 1, 0, or NULL.
Columns of type bit cannot have indexes on them.
Microsoft® SQL Server™ optimizes the storage used for bit columns. If there are 8 or fewer bit columns in a table, the columns are stored as 1 byte. If there are from 9 through 16 bit columns, they are stored as 2 bytes, and so on.
--------------------------------------------------------------------------
This brings up a peculiar point. 8 bits data types can fit in a byte. That mean each bit data type is using a physical bit to store its value. Since a physical bit can be one of two states (on or off, yes, or no, left polarized or right polarized ) then how can a bit data type be NULL.
Obviously, a second bit is required because with the addition of NULL, that is three states a bit data type can assume. How does SQL Server physically handle a NULL bit data type?
Thanks in advance,
Scott
August 23, 2004 at 12:59 pm
Actually it can be a NULL. Consider with a char datatype object or any fixed length object. The length of the data is still stored even if the value is NULL. What is usually stored is all 0's which is the case of a bit. But in the row there is a null bit mask that tells which columns contain a null and which of those are really a value instead. That is how SQL does the logic of NULL on all columns fixed width and variable, variable length just doesn't take up any physical storage when null.
Hope that helps.
August 23, 2004 at 1:34 pm
Thanks.
Makes sence.
What I'm working on is creating my own bit map in a tiny, small, int... column. I do this with bit-wise operations. Once the bits are set I can still index the bit map because SQL Server thinks it an indexable datatype. In the where clause of a DML statement I use more bit-wise operations to determine the filter and since the data is indexed I will do a covered index scan instead of doing a scan of the entire table.
Maybe these bits represent "Is Member of Group 1", "Is Member of Group 2", "Is Member of Group 3", "Is Member of Group 4", etc. Now I can categorize sets of people as members of a single group or/and as members of multiple groups for analytical processing.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply