THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - 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 AndyLeonard.blog.

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:

[Value]
High
Low
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?

Yes!

I can do it with a NULL:

[Value]
  1
  0
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

Comments

 

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

(required) 
(required) 
Submit

This Blog

Syndication

News

My Latest Book:

Community Awards



Friend of Red Gate

Contact Me

Archives

Privacy Statement