Thursday, 3 November 2011

T-SQL: Why have NULL bit fields?

I was asked the question the other day, why SQL Server allows NULLs for the BIT datatype. What you need to bear in mind is what NULL represents - a value which is unknown - and, so there are 3 states which you need to cater for. True, False or Unknown.

As is my wont, I like to explain things with analogies and this is no different. Consider a table holding job application details for a job board website.

CREATE TABLE dbo.tblJobApplications(
CandidateID INT,
DateApplied DATE,
Successful BIT)

-- Application in progress
INSERT INTO dbo.tblJobApplicationsSELECT 1, 1, GETDATE(), NULL

-- Application unsuccessful
INSERT INTO dbo.tblJobApplicationsSELECT 2, 1, GETDATE(), 0

-- Application successful
INSERT INTO dbo.tblJobApplicationsSELECT 3, 1, GETDATE(), 1

The key field to note here is of course the BIT field which indicates the success or failure of the applicaiton. Obviously, when a candidate applies to a job, the success of the application isn't known - the candidate has been accepted, nor rejected. Its only at the end of the lifecycle of the application that this field can take on a meaningful value.

Hopefully, this contrived example helps explain just when you might require a NULL bit field.


  1. Nulls are evil. Didn't your mother teach to stay away from them? :)

    In your example you are describing a value that has 3 possible states - "In progress", "Accepted" or "Rejected". There is nothing unknown.

  2. The field isn't Status though, its Successful which is either true or false or (as yet) undecided.

  3. That is my point - we have missed a domain concept, namely that a job application progresses through various states. Once we have the correct domain concepts then the need for null disappears.

  4. As i mentioned in my post, this is a contrived example and this isn't a discussion about good database design nor whether NULLs are good (or evil). It was merely illustrating a scenario when a NULL bit field could be relevant.


/* add this crazy stuff in so i can use syntax highlighter