Get instant live expert help with SQL
“My expert helped me in less than 20 minutes, saving me what would have been 5 hours of work!”

Enter your problem description Our Experts are available now

Your message must be at least 40 characters
Select your SQL Environment:
Your message must be at least 40 characters
By submitting this form you agree to Got It's privacy policy.

Boolan Data Type in SQL Server – Querychat

The boolean data type is an important data type within SQL. It allows us to create a field or column that can store only two predefined values: true or false. This article elaborates on how SQL server boolean data type is defined and set in SQL Server.

SQL Server stores boolean values in the BIT data type. A boolean variable or a table column cell can have two possible values: 1 and 0. Boolean value 1 can be represented as ‘true’, while value 0 can be represented as ‘false’. In case there is no value defined for mentioned boolean objects, we can always set it to NULL.

Examples

Example #1: Create a variable of bit data type

The following demonstration creates a boolean variable called isActive, sets it to true, prints it out, changes the value of it to false, and prints out the final value of it.

Step 1. Declare a variable named isActive, set it’s value to true, and print it out.

DECLARE @isActive bit;

SET @isActive=1;

print @isActive;

Result: 

1

Setting the variable value to true can be done in two ways:

SET @isActive=1;

Or,

SET @isActive='true';

The result in both cases will be the same (1).

Step 2. Change the variable’s value to false and print it out.

DECLARE @isActive bit;

SET @isActive=0;

print @isActive;

Result: 

0

Setting the variable value to true can be done in two ways:

SET @isActive=0;

Or, 

SET @isActive='false';

The result in both cases will be the same (0).

Example #2: Create a table column of bit data type

In this example, we will create a table that has the same structure as the table presented below. Once created, we will populate it with data that, among other values, contain boolean values as well. The boolean table column is called “IsActive” and will be populated with “True” or “False” boolean values. The empty value will be represented with a NULL value.

ID Name Age DepartmentID DateEnrolled FeeAmount IsActive
1 Kyle Davis 20 1 2015-09-10 5400 True
2 Nick Jones 19 1 2015-09-20 12350 True
3 Sarah Rogers 22 2 2015-09-10 10000 False
4 Deborah Wade 24 3 2017-08-09 3570

Table 1. Students

Let’s follow the steps below to create the Students table and populate it with some data, row by row. For the IsActive column, we will insert it with all possible boolean values: 0, 1, and NULL. Finally, we will select the data to see how the boolean values are being stored within the table.

Step 1. Create the Students table by using the script below.

CREATE TABLE Students (

id SMALLINT NOT NULL,

name VARCHAR (200) NOT NULL,

age SMALLINT,

departmentId SMALLINT,

dateEnrolled DATE,

feeAmount INT,

isActive BIT

)

Step 2. Populate the table with the data using the script below that inserts one row in a table. Notice that we insert the isActive column with 1.

INSERT INTO Students (

id,

name,

age,

departmentId,

dateEnrolled,

feeAmount,

isActive

)

VALUES (

1,

'Kyle Davis',

20,

1,

'2015-09-10',

5400,

1

)

Step 3. Insert the second row. This row has ‘True’ for its isActive column, but we can also insert it with ‘true’. 

The following scripts show that we can define the ‘True’ values in our SQL script, either as 1 or ‘true’.

A boolean value defined as ‘true’ A boolean value defined as 1
INSERT INTO Students (

id,

name,

age,

departmentId,

dateEnrolled,

feeAmount,

isActive

)

VALUES (

2,

'Nick Jones',

19,

1,

'2015-09-20',

12350,

'true'

)
INSERT INTO Students (

id,

name,

age,

departmentId,

dateEnrolled,

feeAmount,

isActive

)

VALUES (

2,

'Nick Jones',

19,

1,

'2015-09-20',

12350,

1

)

Step 4. Insert the third row. We can define the ‘False’ values in our SQL script, either as 0 or ‘false’.

A boolean value defined as ‘false’ A boolean value defined as 0
INSERT INTO Students (

id,

name,

age,

departmentId,

dateEnrolled,

feeAmount,

isActive

)

VALUES (

3,

'Sarah Rogers',

22,

2,

'2015-09-10',

10000,

'false'

)
INSERT INTO Students (

id,

name,

age,

departmentId,

dateEnrolled,

feeAmount,

isActive

)

VALUES (

3,

'Sarah Rogers',

22,

2,

'2015-09-10',

10000,

0

)

Step 5. Insert the last row. We define the empty boolean values in our SQL script as NULL, not as space (‘’ or ‘’). 

A boolean value defined as NULL

INSERT INTO Students (

id,

name,

age,

departmentId,

dateEnrolled,

feeAmount,

isActive

)

VALUES (

4,

'Deborah Wade',

24,

3,

'2017-08-09',

3570,

NULL

)

Step 6. Select the data from the Students table and review how the boolean table column values are represented.

SELECT * FROM Students

Result:

Figure 1. Preview of the data in the Students table

As we can see, the values within the isActive column are represented as boolean 1 and 0 values. Also, empty values are represented as NULL values, as defined in SQL insert queries.

Instant livechat to an Expert!

Most of the time, the problem you will need to solve will be more complex than a simple method. If you want to save hours of research and frustration, try our live SQLExpert service! 

Our SQLQuerychat Experts are available 24/7 to answer any questions you may have. We guarantee a connection within 30 seconds and a customized solution within 20 minutes.

Leave a Reply

avatar
  Subscribe  
Notify of
Trusted by people who work at
Amazon.com, Inc
Facebook, Inc
Accenture PLC
Siemens AG
Macy's
The Allstate Corporation
United Parcel Service
Dell Inc