This page has 0 threads | Add post

Blogs

Finding our blogs useful? Please consider helping us to share more!

T-SQL data types - int, float, decimal, varchar, etc. Part five of a six-part series of blogs
Whether you're creating tables in SQL Server, using SQL variables or passing parameters to stored procedures, you'll need to understand what the possible data types that you can use are. Data Types in T-SQL for SQL Server Text Data Types in T-SQL Number Data Types in T-SQL Date and Time Data Types in SQL Logical, Boolean, Yes/No or Bit Data Types in SQL (this blog) Converting between Data Types This blog is part of our SQL tutorial. Don't forget that Wise Owl are mainly a provider of SQL (and other) training courses.

T-SQL data types - int, float, decimal, varchar, etc.

Part five of a six-part series of blogs

Whether you're creating tables in SQL Server, using SQL variables or passing parameters to stored procedures, you'll need to understand what the possible data types that you can use are.

Data Types in T-SQL for SQL Server
Text Data Types in T-SQL
Number Data Types in T-SQL
Date and Time Data Types in SQL
Logical, Boolean, Yes/No or Bit Data Types in SQL (this blog)
Converting between Data Types

This blog is part of our SQL tutorial. Don't forget that Wise Owl are mainly a provider of SQL (and other) training courses.

Posted by Andy Brown on 05 October 2012

Logical, Boolean, Yes/No or Bit Data Types in SQL

Some things in life are black and white, and the bit data type is one of them: there are no shades of grey, let alone 50 (I've been wanting to sneak that reference into a blog on computer training for ages).

What a Bit Data Type Represents

Most applications have a specific data type for coping with things which can only have one of two states. Examples might be:

You're either female or you're not.
You're either married or you're not.
You're either alive or you're not (although if you're reading this, you probably are).
You're either still working for a company as an employee, or you're not.

Here's what this data type is called in a sample of other applications:

Application	What it's called
Access	Yes/No
Visual Basic/VBA	Boolean
C#	bool

In SQL, you just use the bit data type, which holds the number 0 (corresponding to False) or 1 (corresponding to True).

This doesn't save as much storage as you might think. There are (as every schoolboy knows?) 8 bits in a byte, but if you have a bit data type in a table SQL will put aside a whole byte to accommodate it. The saving comes when you have 8 different bit fields, in which case SQL will squash them all together into a single byte.

Bit Conversion

By and large, SQL will apply the following rules:

Data	Converts to
False, 0	0
True, any other number apart from 0	1

Here are some examples. Suppose we first create a table to hold a couple of purchases made:

-- create a new table to hold purchases

CREATE TABLE tblPayments(

Purchase varchar(100),

Quantity int,

Amount decimal,

IfOk bit

)

You can use any number other than 0 as the IfOk value, and it will be converted to 1:

-- add a purchase whose OK value is TRUE

INSERT INTO tblPayments (

Purchase, Quantity, Amount, IfOk

) VALUES (

'Fluffy toy', 2, 9.99,42

)

You can also use the words True and False, although you have to do so using text strings:

-- add a purchase whose OK value is FALSE

INSERT INTO tblPayments (

Purchase, Quantity, Amount, IfOk

) VALUES (

'Spaceship', 1, 100, 'FALSE'

)

After running the SQL commands above you would have two purchases in a table. You could show the OK ones as follows:

-- display only the OK purchases

SELECT

Purchase,

IfOk

FROM

tblPayments

WHERE

IfOk = 'TRUE'

This would give you:

The purchases for which IfOk equals 1.

In practice the rules for when SQL will accept True and False are so hard to remember that it's easier just to use 0 and 1. However, other programs will read bit data types in as True/False values.

Parts of this blog
Data Types in T-SQL for SQL Server Text Data Types in T-SQL Number Data Types in T-SQL Date and Time Data Types in SQL Logical, Boolean, Yes/No or Bit Data Types in SQL (this blog) Converting between Data Types

Some other pages relevant to the above blogs include:

This blog has 0 threads

Add a new post

Book any course as classroom, onsite or online training, or as one-to-one consultancy

Free resources

Paid services

Getting help

Where we are

Where you are

Online

Transparent reviews

Delivery of courses

Nicer to work with

Reliable and established

Our training courses

C#

Excel

MySQL

Power Apps

Power Automate

Power BI and DAX

Python

Report Builder

SQL

SQL Server

VBA Macros

Other training resources

Blogs

Exercises

YouTube tutorials

YouTube shorts

Test your skills

Consultancy

Courseware

Self-paced courses

Newsletters

Publications

Our training venues

London

Manchester

Across the UK

At your site

Online

Why we are different

Hundreds of published reviews

Small class sizes

Outstanding courseware

Online classroom

(Almost) no cancellations

Wise Owl trainers only

Genuine integrity

Invoices after training

30+ years of Wise Owl

Our top 100 clients

BLOGS BY TOPIC

BLOGS BY AUTHOR

Logical, Boolean, Yes/No or Bit Data Types in SQL

What a Bit Data Type Represents

Bit Conversion

Head office

London

Manchester