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.

  1. Data Types in T-SQL for SQL Server
  2. Text Data Types in T-SQL
  3. Number Data Types in T-SQL
  4. Date and Time Data Types in SQL
  5. Logical, Boolean, Yes/No or Bit Data Types in SQL (this blog)
  6. 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:

Table of purchases

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.

This blog has 0 threads Add post