Does BIT Datatype Equal to 1 or TRUE in SQL Server? – Interview Question of the Week #282

Question: Does BIT Datatype Equal to 1 or TRUE in SQL Server?

Does BIT Datatype Equal to 1 or TRUE in SQL Server? - Interview Question of the Week #282 bitdatatype-800x249

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:

Does BIT Datatype Equal to 1 or TRUE in SQL Server? - Interview Question of the Week #282 BITDatatype2

 

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.

Does BIT Datatype Equal to 1 or TRUE in SQL Server? - Interview Question of the Week #282 BITDatatype2

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.

Does BIT Datatype Equal to 1 or TRUE in SQL Server? - Interview Question of the Week #282 BITDatatype1

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)

, ,
Previous Post
How to Force Index on a SQL Server Query? – Interview Question of the Week #281
Next Post
What does Verify Backup When Finished do in SQL Server? – Interview Question of the Week #283

Related Posts

4 Comments. Leave new

  • Carsten Saastamoinen-Jakobsen
    June 21, 2020 12:04 pm

    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!!!!

    Reply
  • 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.

    Reply
  • Carsten Saastamoinen-Jakobsen
    June 27, 2020 2:00 am

    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?

    Reply

Leave a Reply

Menu