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