THE SQL Server Blog Spot on the Web

Welcome to - The SQL Server blog spot on the web Sign in | |
in Search

Andy Leonard

Andy Leonard is an author and engineer who enjoys building and automating data integration solutions. Andy is co-host of the Data Driven podcast. Andy is no longer updating this blog. His current blog is

Database Design: 3-State Bits

I can hear you thinking, “Andy, you’re off your old rocker. There are only two states for bit values!” And you are correct. Almost.

What if I’m storing a truly binary value – a value that possesses only two possible states – but there’s the possibility of a third state to indicate that I do not know which state applies. Consider this single-column example table:

Don’t Know or Don’t Care

The two discrete states are High and Low. The 3rd state can be labeled “unknown.” Can I represent these three states using a bit data type?


I can do it with a NULL:


NULL means “the data is missing.” But in this context, I can define NULL to mean “unknown.” One caveat is that I cannot define to NULL to mean both “missing” and “unknown.” I have to pick one and only one meaning for NULL.


Published Wednesday, May 6, 2015 7:45 PM by andyleonard

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS



RichB said:

Not something I've looked into, but what happens with null in bitwise operations?

Also, I see the 2016 announcement suggests dynamic obfuscation, which I really hope means what it sounds like it means... but have you heard how that might work with bits?


May 7, 2015 5:08 AM

Leave a Comment


This Blog



My Latest Book:

Community Awards

Friend of Red Gate

Contact Me


Privacy Statement