Data Types: BIT
All credit is given to Arnie Rowland.
The BIT data type seems so simple, doesn’t it?
It is a number, and a bit can only be 0 (zero) or 1 (one), right? — Or so it seems.
But… let’s dig a little deeper and see what interesting surprises may be hidden in the BIT data type.
As with most things in life that on the surface appear to be simple, the BIT data type is more complex that it seems.
Long ago, in a programming class, or a book or article you were reading, you may recall coming across the explanation of NaN (Not a Number). The NaN concept could be useful at times, letting you know that while a code object (variable, parameter, column) may have been defined as a numeric data type, it may actually contain a value that is not easily expressed in a true numeric form. (Look it up if you need a refresher about NaN; for example, FLOAT() data types may at times be NaN.)
With a BIT data type, it can be helpful to consider the concept of NaZ. I know, you probably haven’t come across NaZ before, but hang in and I think that this will help you truly understand the BIT data type. So, NaZ is ‘Not a Zero’. By definition, a BIT data type is either zero, or not a zero. There is a small amount of ‘intelligence’ coded into the BIT data type. You can assign any numeric value to a BIT data type, and it will auto-magically transform into either a 0 (zero) or 1 (one). ANY numeric value that is NaZ will be assigned the value of 1 (one).
DECLARE @BitTest bit;
SET @BitTest = 0.000000000000000000000000001
SELECT @BitTest AS [BitValue]
BitValue
1
What can complicate this is that sometimes even a string value that looks like a number will implicitly and auto-magically transform into a 1 (one).
For example:
DECLARE @BitTest bit;
SET @BitTest = ‘25’
SELECT @BitTest AS [BitValue]
BitValue
1
BUT, and here’s the rub, If the string value contains what could be considered ‘punctuation marks’, then all bets are off. With this example:
DECLARE @BitTest bit;
SET @BitTest = ‘2.5’
SELECT @BitTest AS [BitValue]
We see this ERROR message returned.
Get Joey Zhou’s stories in your inbox
Join Medium for free to get updates from this writer.
Msg 245, Level 16, State 1, Line 2
Conversion failed when converting the varchar value ‘2.5’ to data type bit.
IF the string value is a ‘whole’ number, even with a plus/minus sign, it transforms; but if the string contains punctuation marks (periods, commas, dollar signs, etc., then it will FAIL.
Oh, and there is one other important factor to keep in mind. A BIT data type can also be NULL, and that’s not a 0 (zero) or a 1 (one). However, if you keep clear about the NaZ concept, then IF testing BIT data types is quite predictable. In an IF test, a BIT is either a 0 (zero), or it is NaZ — 1 (one). That simple NaZ concept is key to effectively using BIT data types as control flags. Since a NULL BIT data value is UNKNOWN, it will FAIL the IF test since the IF test is looking for values that are truly ‘NaZ’. An ‘Unknown’ value, by definition, CANNOT be a ‘Not A Zero’ value simply because it is impossible to know for certain.
DECLARE @BitTest bit;
SET @BitTest = NULL
IF @BitTest = ‘True’
SELECT concat( ‘@BitTest is TRUE and is equat to: ‘, @BitTest ) AS [BitTest];
ELSE
SELECT concat( ‘@BitTest is FALSE and is equal to: ‘, @BitTest ) AS [BitTest];
BitTest
@BitTest is FALSE and is equal to:
Oh, did I mention that a BIT data type will ALSO IF test to ‘True’ or ‘False’?
In an IF test, a BIT data type will evaluate as equal to the strings ‘TRUE’ or ‘FALSE’. It will auto-magically determine that any NaZ value is TRUE, and everything else is FALSE. This can make code more readable and the intent more clear.
DECLARE @ShouldIDoThis bit;
SET @ShouldIDoThis = 0
IF @ShouldIDoThis = ‘True’
SELECT concat( ‘@ShouldIDoThis’ is TRUE and is equat to: ‘, @ShouldIDoThis) AS [Should I Do This];
ELSE
SELECT concat( ‘@ShouldIDoThis’ is FALSE and is equal to: ‘, @ShouldIDoThis) AS [Should I Do This];
Should I Do This
ShouldIDoThis is FALSE and is equal to: 0
Think about FLOW CONTROL situations where you need to know if the ‘control flag’ is NOT FALSE. Only numeric values will IF test to be TRUE, and everything else, including NULL, will be FALSE. This can simplify FLOW CONTROL IF tests, perhaps eliminating conversions, and definitely making code easier to understand.
Throughout T-SQL code, the ERROR object provides an error number when something goes wrong. It may help to think of it as ‘IsError’ returning ‘True’ (non-zero) or ‘False’ (zero). The same concept can be applied to the RETURN value from a Stored Procedure. Though I will often prefer to think of the RETURN value as ‘SUCCESS’ — meaning no error, or ‘PROBLEM’ . Just like handling ERROR conditions, I may code alternative actions when a procedure call returns ‘PROBLEM’.
Remember, you can assign ANY numeric value to a BIT data type, and in an IF test, it will auto-magically evaluate any non-zero value as TRUE, and anything else, including NULL, as FALSE).
Hopefully this has intrigued you, and you are imagining how to explore, test, and better utilize BIT data types.
