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.

image

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.

Subscribe to my posts via E-mail: Subscribe here | Read my posts via your favourite RSS reader: Click Here!

Advertisements

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 )

Twitter picture

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

Facebook photo

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

Google+ photo

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

Connecting to %s