BLOGS BY TOPIC
BLOGS BY AUTHOR
BLOGS BY YEAR
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
You need a minimum screen resolution of about 700 pixels width to see our blogs. This is because they contain diagrams and tables which would not be viewable easily on a mobile phone or small laptop. Please use a larger tablet, notebook or desktop computer, or change your screen resolution settings.
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.
- 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