560 attributed reviews in the last 3 years
Refreshingly small course sizes
Outstandingly good courseware
Whizzy online classrooms
Wise Owls only (no freelancers)
Almost no cancellations
We have genuine integrity
We invoice after training
Review 30+ years of Wise Owl
View our top 100 clients
Search our website
We also send out useful tips in a monthly email newsletter ...
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.
This blog is part of our SQL tutorial. Don't forget that Wise Owl are mainly a provider of SQL (and other) training courses. |
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).
Most applications have a specific data type for coping with things which can only have one of two states. Examples might be:
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.
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 |
---|
|
Some other pages relevant to the above blogs include:
Kingsmoor House
Railway Street
GLOSSOP
SK13 2AA
Landmark Offices
99 Bishopsgate
LONDON
EC2M 3XD
Holiday Inn
25 Aytoun Street
MANCHESTER
M1 3AE
© Wise Owl Business Solutions Ltd 2023. All Rights Reserved.