An Introduction To SQL Boolean Data Type in SQL Server

Summary: In this article, you will learn, SQL Boolean Data Type, suppose that when need to store a boolean value in your SQL database? Does the data type even exist? Let’s understand the SQL Boolean Data Type carefully this article and find out.

What is a Boolean?

The SQL Boolean data type is a data type that has one of two possible values such as true and false. Furthermore, which is intended to represent the two truth values of logic & Boolean algebra. Thus, the Boolean data type is primarily related to conditional statements, that allow different actions by changing control flow depending on whether a programmer-specified Boolean condition evaluates to true or false.

This is usually held on as 1 (true) or 0 (false). It’s named once George Boole WHO 1st defined an algebraic system of logic in the nineteenth century. SQL Boolean values are common in programming languages however do they exist in.

Is There a Boolean in SQL Server?

The given below table shows that whether or not there is a boolean data type in each SQL vendor:
SQL Boolean Data Type: Is There a Boolean in SQL Server

SQL Datatype Bit (Used For Boolean Like Values)

SQL Server: Only Three Values

The datatype bit in SQL Server can only store three values like 0, 1, and null.
Therefore, if a variable whose data type is bit is assigned a value that is different from 0(zero) and is not null, then the variable’s value will be set to 1. This is an example in the following simple SQL batch:

declare @A bit;
set @A =   0    ; print(isNull(str(@A), 'null')); --    0
set @A =   0.4  ; print(isNull(str(@A), 'null')); --    1
set @A =   0.6  ; print(isNull(str(@A), 'null')); --    1
set @A =   1    ; print(isNull(str(@A), 'null')); --    1
set @A =  -1    ; print(isNull(str(@A), 'null')); --    1
set @A =   2    ; print(isNull(str(@A), 'null')); --    1
set @A =  -2    ; print(isNull(str(@A), 'null')); --    1
set @A = 123.456; print(isNull(str(@A), 'null')); --    1
set @A =  null  ; print(isNull(str(@A), 'null')); -- null

declare @A bit;

set @A = 0 ; print(isNull(str(@A), 'null')); -- 0

set @A = 0.4 ; print(isNull(str(@A), 'null')); -- 1

set @A = 0.6 ; print(isNull(str(@A), 'null')); -- 1

set @A = 1 ; print(isNull(str(@A), 'null')); -- 1

set @A = -1 ; print(isNull(str(@A), 'null')); -- 1

set @A = 2 ; print(isNull(str(@A), 'null')); -- 1

set @A = -2 ; print(isNull(str(@A), 'null')); -- 1

set @A = 123.456; print(isNull(str(@A), 'null')); -- 1

set @A = null ; print(isNull(str(@A), 'null')); -- null

Although the name of this datatype is a bit, therefore, it needs at least two(2) bits to be able to store these three(3) values.

No Boolean Data Type

SQL Server doesn’t have a real boolean datatype (which is named bool or boolean). when developers want to store boolean-like values in an SQL server, then resort to the bit datatype because of the three values it can store.
Because SQL Server doesn’t have a »real« boolean data type, it also doesn’t have the constant values true and false.

The Bit Data Type Used in An If Statement

Note: SQL Server doesn’t allow an expression that evaluates to a bit data type in an if statement, An expression of non-boolean type specified in a context where a condition is expected.
Thus, the expression has to expressly be compared to 1 or 0 or is null, as shown in the given below example:

declare
@do_it bit;
set @do_it = 0;
if      @do_it = 1 print('Going to do it'                      )
else if @do_it = 0 print('Not going to do it'                  )
else               print('I am not sure if I am going to do it')

declare

@do_it bit;

set @do_it = 0;

if @do_it = 1 print('Going to do it' )

else if @do_it = 0 print('Not going to do it' )

else print('I am not sure if I am going to do it')

SQL Server Boolean

As discussed above that there is no boolean data type in SQL Server. so, therefore, a common option is to use the BIT data type.
You have already known that a BIT data type is used to store bit values from 1 to 64. So, a BIT field can be used for booleans, providing 1 for TRUE & 0 for FALSE.

CREATE TABLE TestBoolean (
  SomeText VARCHAR(20),
  Is_Checked BIT
);

CREATE TABLE TestBoolean (

SomeText VARCHAR(20),

Is_Checked BIT

);

Here, means that you can able to insert either a 1 (for TRUE) or 0 (for FALSE) into this column. Furthermore, there is no need to add a check constraint because BIT values only accept 1 or 0.

INSERT INTO TestBoolean (SomeText, Is_Checked) VALUES ('x', 1);
INSERT INTO TestBoolean (SomeText, Is_Checked) VALUES ('y', 0);

1 2	INSERT INTO TestBoolean (SomeText, Is_Checked) VALUES ('x', 1); INSERT INTO TestBoolean (SomeText, Is_Checked) VALUES ('y', 0);

Therefore, When you want to select these values, they are shown as 1 or 0.

SELECT SomeText, Is_Checked
FROM TestBoolean;

1 2	SELECT SomeText, Is_Checked FROM TestBoolean;

SQL Server Boolean

PostgreSQL Boolean

Also, PostgreSQL does have a boolean data type.
Here, in the case of PostgreSQL you can store either true or false during this column, which can be represented by many various/different values:

TRUE: It is represented by TRUE, ‘t’, ‘true’, ‘y’, ‘yes’, ‘on’, ‘1’
FALSE: It is represented by FALSE, ‘f’, ‘false’, ‘n’, ‘no’, ‘off’, ‘0’

The keywords TRUE and FALSE are most well-liked. An illustration of a boolean data type in PostgreSQL is:

CREATE TABLE TestBoolean (
  SomeText TEXT,
  Is_Checked BOOLEAN
);

CREATE TABLE TestBoolean (

SomeText TEXT,

Is_Checked BOOLEAN

);

Afterthat, You can insert a boolean value using the SQL Server INSERT statement:

INSERT INTO TestBoolean (SomeText, Is_Checked) VALUES ('x', TRUE);
INSERT INTO TestBoolean (SomeText, Is_Checked) VALUES ('y', FALSE);

1 2	INSERT INTO TestBoolean (SomeText, Is_Checked) VALUES ('x', TRUE); INSERT INTO TestBoolean (SomeText, Is_Checked) VALUES ('y', FALSE);

Furthermore, after insert the value, When you select a boolean value, then it is displayed as either ‘t’ or ‘f’.

SELECT SomeText, Is_Checked
FROM TestBoolean;

1 2	SELECT SomeText, Is_Checked FROM TestBoolean;

SQL Boolean Data Type: MySQL Boolean

Furthermore, also in the case of MySQL does have a boolean data type. However, it’s simply a synonym for TINYINT which is a numeric field.
A common variation is to use a BIT field.
As you know that A BIT data type is used to store bit values from 1 to 64. Thus we can say that, a BIT(1) field can be used for booleans, providing 1 for TRUE and 0 for FALSE. as Just like in SQL Server.

CREATE TABLE TestBoolean (
  SomeText VARCHAR(20),
  Is_Checked BIT(1)
);

CREATE TABLE TestBoolean (

SomeText VARCHAR(20),

Is_Checked BIT(1)

);

Therefore, you can insert either a 1 (for TRUE) or 0 (for FALSE) into this column. furthermore, There is no need to add a check constraint because BIT values only accept 1(one) or 0(zero).

INSERT INTO TestBoolean (SomeText, Is_Checked) VALUES ('x', 1);
INSERT INTO TestBoolean (SomeText, Is_Checked) VALUES ('y', 0);

1 2	INSERT INTO TestBoolean (SomeText, Is_Checked) VALUES ('x', 1); INSERT INTO TestBoolean (SomeText, Is_Checked) VALUES ('y', 0);

Thus, afterthat When you select these values, then they are shown just as 1 or 0.

MySQL Boolean

SQL Boolean Data Type: Oracle Boolean

Is there a SQL boolean data type in Oracle?
Answer: No, there isn’t.
Therefore, in the case of Oracle, you can’t declare a column with the SQL BOOLEAN data type.
However, there are many alternatives, which I’ve detailed given below.
The counseled means of storing booleans in Oracle SQL is to use a NUMBER(1) field. thus, it can store 1 as true & 0 as false.

CREATE TABLE TestBoolean (
  SomeText VARCHAR2(20),
  Is_Checked NUMBER(1)
);

CREATE TABLE TestBoolean (

SomeText VARCHAR2(20),

Is_Checked NUMBER(1)

);

Furthermore, here, you can add a check constraint on the column to ensure other values can not be entered.

CREATE TABLE TestBoolean (
  SomeText VARCHAR2(20),
  Is_Checked NUMBER(1),
  CONSTRAINT ck_TestBoolean_ischk CHECK (is_checked IN (1,0))
);

CREATE TABLE TestBoolean (

SomeText VARCHAR2(20),

Is_Checked NUMBER(1),

CONSTRAINT ck_TestBoolean_ischk CHECK (is_checked IN (1,0))

);

Here, you are able to insert either a 1 (for TRUE) or 0 (for FALSE) into this column:

INSERT INTO TestBoolean (SomeText, Is_Checked) VALUES ('x', 1);
INSERT INTO TestBoolean (SomeText, Is_Checked) VALUES ('y', 0);

1 2	INSERT INTO TestBoolean (SomeText, Is_Checked) VALUES ('x', 1); INSERT INTO TestBoolean (SomeText, Is_Checked) VALUES ('y', 0);

Afterthat, When you want to select these values, then they are shown just as 1 or 0.

SELECT SomeText, Is_Checked
FROM TestBoolean;

1 2	SELECT SomeText, Is_Checked FROM TestBoolean;

Hence, you are able to convert these values into other values to display in an application, when if you don’t want to display 1 or 0.

Conclusion: SQL Boolean Data Type

In this article, you have learned SQL Boolean Data Type, and which language support Boolean data type such as Oracle, PostgreSQL, MySQL, SQL Server etc. I hope you will enjoy it!

An Introduction To SQL Boolean Data Type in SQL Server

What is a Boolean?

Is There a Boolean in SQL Server?

SQL Datatype Bit (Used For Boolean Like Values)

SQL Server: Only Three Values

No Boolean Data Type

The Bit Data Type Used in An If Statement

SQL Server Boolean

PostgreSQL Boolean

SQL Boolean Data Type: MySQL Boolean

SQL Boolean Data Type: Oracle Boolean

Conclusion: SQL Boolean Data Type

Related Posts

About Author

admin