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:
- 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)
- Do we need to use only a 0 or a 1 to initialize the BIT data type?
- 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.
The above experiment helped us conclude that:
- BIT values are not completely Boolean, they are tri-state with NULL, 0 and 1 being possible values
- If left unassigned, the value is NULL
- If assigned with a value anything other than 0 or NULL, the value is taken as 1
- You can use a ‘TRUE’ and ‘FALSE’ string values with the BIT data type
- 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.