Recently at the office, we ended up discussing the BIT data type (http://msdn.microsoft.com/en-us/library/ms177603.aspx). While we all understand that BIT is supposed to represents a Boolean value, we were not quite convinced. Boolean values are two-state – they can only be TRUE or FALSE (or 1 and 0). However, we have seen BIT to be NULL a couple of times. Therefore, the questions that we had in our minds were:

1. Is BIT really a two-state data type? Or is it tri-state (for those not familiar with the world of electronics, the tri-state is a state that’s undefined – it’s neither ON nor OFF)
2. Do we need to use only a 0 or a 1 to initialize the BIT data type?
3. Can we use ‘TRUE’ and ‘FALSE’ with the BIT datatype?

Finally, we decided to perform a little experiment. We prepared a small script covering the possible scenarios and checked their outputs. The script is available for your reference below:

--Declare the variable
DECLARE @bit BIT

--Check for default value
SELECT @bit AS DefaultValue

--Set to a positive value, other than 0 or 1
SET @bit = 99;
SELECT @bit AS [PositiveValue];

--Set to a negative value
SET @bit = -99;
SELECT @bit AS [NegativeValue];

--Set to a decimal value > 0 and < 1
SET @bit = 0.25;
SELECT @bit AS [DecimalValue025];

--Set to a decimal value > 0 and < 1
SET @bit = 0.50;
SELECT @bit AS [DecimalValue050];

--Set to a decimal value > 0 and < 1
SET @bit = 0.75;
SELECT @bit AS [DecimalValue075];

--Set to a string value - TRUE
SET @bit = 'TRUE';
SELECT @bit AS [StringTRUE],
       CASE
           WHEN @bit = 'TRUE'
           THEN 'Yes'
           ELSE 'No'
       END AS  [StringComparison];

--Set to a string value - FALSE
SET @bit = 'FALSE';
SELECT @bit AS [StringFALSE],
       CASE
           WHEN @bit = 'TRUE'
           THEN 'Yes'
           ELSE 'No'
       END AS  [StringComparison];

The results that came up resolved all our questions.

Conclusion

The above experiment helped us conclude that:

1. BIT values are not completely Boolean, they are tri-state with NULL, 0 and 1 being possible values
2. If left unassigned, the value is NULL
3. If assigned with a value anything other than 0 or NULL, the value is taken as 1
4. You can use a ‘TRUE’ and ‘FALSE’ string values with the BIT data type
5. Because the default value of BIT is NULL, always assign your BIT variables! (for that matter, always assign a default value to any variable!)

I trust you found the above experiment interesting. I would welcome your ideas for future experiments.

Until we meet next time,

Be courteous. Drive responsibly.