allowing NULL in a bit field

[Strogian]

What is the difference in storage between a bit field that allows NULL and a bit field that does not? Or a field in general? Is it easier for a system to allow NULL (i.e. the storage is allocated no matter what, but if you don't allow NULL it forces you to enter a value), or not allow NULL (i.e. the storage is not allocated unless you specifically allow NULL) ?

[rock]

The difference kind of comes down to reference types versus value types. A value type or primitive data type (int, double, etc) cannot be null - they all have a default value (usually zero). A reference type typically doesn't have a default value and unasigned, it'll be null.

Basically, value types should be used whenever possible because they're allocated on the stack, are fast to access, and disappear when they go out of scope. On the other hand, reference types are allocated on the heap, are usually slower, and have to managed properly (manually or by garbage collection).

Before I babble on, is this along the lines of what you were looking for?

[Strogian]

oh, you know what? I just realized I didn't use the word 'database' once in my question.

I'm not sure if this changes things, but I'm talking about a Microsoft SQL Server (e.g. created in Enterprise Manager) database here. That means I'm talking about a field with a 'bit' type, not a 'bit field'. So maybe that will make things a little more specific. ;)

Yeah, I'm sort of surprised anyone answered that question at all, seeing how much information I left out of it. :D

[rock]

I was just talking general OO programmming. If this is for databases, I'll have to bow out of this one now :)

[Strogian]

holy crap, msdn is awesome

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/createdb/cm_8_des_02_92k3.asp

If there are fixed-length columns in the table, a portion of the row, known as the null bitmap, is reserved to manage column nullability. Calculate its size:

Null Bitmap (Null_Bitmap) = 2 + (( Num_Cols + 7) / 8 )

Only the integer portion of the above expression should be used; discard any remainder.

So it looks like it's all stored in a straightforward way. And since everything gets rounded up to the nearest byte, it probably doesn't take any more storage to allow it for one row. (although they're not precise on whether it even matters is I'm "allowing nulls" or not)

EDIT: and http://www.winnetmag.com/SQLServer/Article/ArticleID/4885/4885.html is good too, now that I know what to look for (i.e. "null bitmap" :D)

Let's look at some changes in the structure of the individual data rows. A few more bytes of overhead occur for rows with all fixed-length columns. If no variable-length fields occur in a row, SQL Server needs room for the data, plus 6 overhead bytes in the row, plus 1 or more bytes for the bitmap to keep track of which columns are null. For SQL Server 7.0 to mark null values as null, SQL Server stores a bitmap in each row, with one bit for each column in the table. The size of the bitmap in bytes is the number of columns divided by eight rounded up to the nearest integer. If a table has eight or fewer columns in the table, SQL Server needs 7 bytes (6 overhead bytes plus the single null bitmap byte) for the row overhead. To compute the number of rows that will fit on a page, you must take into account the 2 bytes in the row offset array for each row. To compute how many rows will fit on a page, you need to divide 8096 by the size of the fixed-length fields plus nine.

SQL Server 7.0's tracking of nulls in a special bitmap in each row gives you several advantages over SQL Server 6.5. One advantage is that type bit columns can now be null-able. Also, columns that allow nulls are no longer considered variable-length fields. Unless the column is of a variable-length datatype, a column that is null will use the full amount of space; this way, a column of type int not null will always use exactly 4 bytes, whether it contains a null or not. Because SQL Server 6.5 treated nulls as zero-length strings, you could not specify a true zero-length field, such as a character string with no characters (an empty string). In SQL Server 7.0, because nulls have their own means of storage, you can have true empty strings. A varchar(20) field, for example, can now have anywhere from 0 characters to 20 characters.

If your rows have any variable-length columns, you must take a few more things into account when determining the row length. In addition to the length of the variable-length columns, SQL Server needs 4 bytes for the first variable-length column and 2 bytes for each additional variable-length column.