A while back I wrote about using null as a third value for a bit data type in SQL Server. The problem is not that storage doesn’t work. The problem is that often those bit columns are used to identify state, and allowing null values does not enable indexing, resulting in slower query performance in some cases.
One such case is that situation where the default value should be true. If the column allows null, and you wish to see all the true values you have to convert the null values to true in your where clause, which doesn’t use an index. Another option is to test for IS NULL OR = true. Now you have two tests that have to be processed, and only one takes advantage of an index.
The best option is to have a default value of True for the column, or to always set the value whenever data is created. Then your query can simply test for the value = true.
For additional performance, you can have an index with a filter (a where clause in the index). The index can implement the filter for the value = true. If you have a lot of data that is not true, having a filtered index of this sort optimizes the performance of the index because it only maintains rows that are true.
The point is that allowing null is always something to avoid if the column may need to be indexed. This is even more true when using bit data types. Take the time to determine if a default value is reasonable and use that instead of null whenever possible.
Cheers,
Ben