O'Reilly logo

Joe Celko's Thinking in Sets: Auxiliary, Temporal, and Virtual Tables in SQL by Joe Celko

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

11.3. Do Not Use Bit Or Boolean Flags In Sql

The BIT and BIT VARYING data type were deprecated in the SQL:2003 Standards, but they have survived some products, along with BYTE data types. While BOOLEAN had not yet been deprecated as of this writing, it also has problems with the rules about NULLS and the three-valued logic of SQL. The NULL cannot be treated as an UNKNOWN because one of the basic rules of NULLS is that they propagate. The resulting four-valued logic is inconsistent:

UNKNOWN AND TRUE = UNKNOWN
UNKNOWN AND FALSE = FALSE

NULL AND FALSE = NULL
NULL AND TRUE  = NULL

But there are other problems with flags.

11.3.1. Flags Are at the Wrong Level

In SQL, a row in a properly designed table should represent a single complete fact, expressed ...

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required