SQL Server – Experiments with BIT data type – Is it really Boolean? Can values other than 0 and 1 be assigned?


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.

Results from my experiments with the BIT datatype. (Image (c)SQLTwins, nakulvachhrajani.com)
Experimenting with the BIT datatype in SQL Server

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.

2 thoughts on “SQL Server – Experiments with BIT data type – Is it really Boolean? Can values other than 0 and 1 be assigned?

  1. Ramireddy

    Recently in our project we were suggested not to use BIT datatype as ORACLE don’t have bit datatype as we target both ORACLE and SQL Server as back-end.

    Like

    Reply

Let me know what you think about this post by leaving your feedback here!

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.