Question: Does BIT Datatype Equal to 1 or TRUE in SQL Server?
Answer: Very interesting question. Let us answer this question with the help of a demonstration.
Here is the sample script:
DECLARE @myBIT BIT SET @myBIT = 1 SELECT @myBIT Val
When you run the code above it gives us the following answer:
From the example, it is clear that BIT is storing the 1.
Now let us run the following code where we will try to set the value of the BIT variable to true.
DECLARE @myBIT BIT SET @myBIT = 'true' SELECT @myBIT Val
Well, lots of people will think that the above code should give us error but in reality, it returns the following value.
That means any variable which is of datatype BIT also access the string ‘true’ or ‘false’ as the value and returns us 1. However, if you try to set that to any value it will give us an error. Let us check that.
DECLARE @myBIT BIT SET @myBIT = 'nice' SELECT @myBIT Val
When you run the above script it will give us the following error:
Msg 245, Level 16, State 1, Line 2
Conversion failed when converting the varchar value ‘nice’ to data type bit.
Noe before we conclude this blog post, let us do another test.
DECLARE @myBITtrue BIT DECLARE @myBITone BIT SET @myBITtrue = 'true' SET @myBITone = 1 SELECT @myBITtrue myBITtrue, @myBITone myBITone
The query above returns the following results.
Based on the result is very clear that we can easily assign 1 or true value to BIT datatype and it will work just fine. Here is one more fun one.
If you run the following code, it will still work.
DECLARE @myBIT BIT SET @myBIT = '1' SELECT @myBIT Val
The above code tells us that string value 1 is automatically covered to the bit value 1.
Well, that’s it. Please let me know if you find this blog interesting or not in the comments section. During Comprehensive Database Performance Health Check, when we are waiting for the different teammates to join for the meeting, we often discuss similar simple concept which keeps us engaged.
Reference: Pinal Dave (https://blog.sqlauthority.com)