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)
4 Comments. Leave new
DECLARE @myBIT BIT;
SET @myBIT = 0;
SELECT @myBIT AS myBIT;
SET @myBIT = 1;
SELECT @myBIT AS myBIT;
SET @myBIT = -888656678;
SELECT @myBIT AS myBIT;
SET @myBIT = 656678;
SELECT @myBIT AS myBIT;
All other numbers than 0 gives 1!!!!
The BIT data type has had lots of problems. At one point it was a true BIT data type, allowing only the values {1,0} . Later it was made into a numeric data type, and as such, it had to allow the values {0,1, NULL}. Older SQL Server programmers simply assumed that a BIT variable would not be NULL, so they never added a NOT NULL constraint in the DDL. Finding this when you upgraded from one release of SQL Server to the next one was an unwelcome surprise. But perhaps more fundamentally, you should not write with BIT flags in SQL; this is a declarative language and not assembler.
That is the golden statement, sir. It is declarative language and not assembler.
But in a declarative language, it is possible to have an information, that only have to different values – and this is not assembler. And declare such an information as CHAR(1), CHAR(3), SMALLINT, … is not more or less assembler than declare it as BIT. And declare it as BIT is not assembler. Why is it assembler?