Whether you're creating tables, passing data to stored procedures or working with variables, you'll need to know what sorts of data SQL Server can work with.
You'll also need to know how to convert between data types, which is covered at the end of this tutorial.
The Mickey Mouse Guide to SQL Data Types
If you want a quick reference, you can't go far wrong with choosing between these data types (the rest of this blogs gives the full Monty on what's going on):
What you're storing | Use | Notes |
|---|
Any string of text | varchar(MAX) | The string of text does have a theoretical maximum limit of 2,147,483,647 characters. That's a LOT of text! |
Any whole number | int | Again, this has a theoretical maximum value of 2,147,483,647. This is a large number. |
Any other number | float | This can hold virtuallly any number you can conceive of, from the tiniest fraction to the number of atoms in the Universe. |
Any date | date | Holds any date between 1st January in 1AD to 31st December 9999! |
Any logical (yes/no) value | bit | A bit is either on or off (0 corresponds to False, and 1 to True). |
A calculation | N/A | Computed columns are covered in this separate tutorial. |
For those who want to know more than this, the rest of this blog gives a fuller picture (it's still not the whole story though).
The two things you need to decide for text are:
Does it need to be held in Unicode format; and
How long is it?
Unicode Format
The latest standard of the Unicode fomrat is UTF-18; T-SQL uses the older UC-S-2 standard. The point behind Unicode is to provide a fuller set of characters. You make a data type Unicode in SQL by preceding it by an n (standing, apparently, for national character). For example:
Normal data type | Unicode equivalent |
|---|
char(10) | nchar(10) |
varchar(MAX) | nvarchar(MAX) |
For normal text SQL puts aside 1 byte of storage space for each character stored; for Unicode text this figure rises to 2 bytes of storage space for each character stored.
If you're using normal text in a single language, the chances are that you won't need to hold text as Unicode characters, and can save some space.
How Long is your Text?
If you will always be holding a similar length of text in a column, parameter or variable, give it a fixed text size; otherwise, give it a variable length. Here are a couple of examples:
Data stored | Notes on length of data | Best data type |
|---|
UK post codes | UK post codes are either 7 or 8 characters long. | char(8) |
UK surnames | Surnames can be double-, triple- or even quadruple-barrelled, but it's hard to imagine one longer than 100 characters. | varchar(100) |
Thus use the char data type to hold fixed-length text, and varchar to hold variable-length text.
There are also old-fashioned text and ntext data types, which Microsoft are phasing out - so avoid using these in new systems.
Putting this all Together - Text Data Types
Here then is a list of the main data text types that you can use in T-SQL:
Data type | What it will hold |
|---|
char, nchar | A single character, or a single Unicode character. |
char(n), nchar(n) | A fixed-length string of n characters (where n can be any number between 1 and 8,000 for normal text or between 1 and 4,000 for Unicode text). |
varchar(n), nvarchar(n) | A stirng of up to n characters, where n can be any number between 1 and 8,000 for normal text or between 1 and 4,000 for Unicode text. |
varchar(MAX), nvarchar(MAX) | A string which takes up to 2,147,483,647 bytes of space (in practice, about a billion Unicode characters or 2 billion normal ones). |
SQL has different types according to whether you're storing an integer or any other number.
An integer is a whole number (so the number of people in your office is an integer, but the exact height of the tallest person in metres isn't).
Integer Data Types
There is a wide range of integer data types in SQL, as follows:
Data type | What it will hold | Bytes used |
|---|
bigint | Any integer with absolute value less than 9,223,372,036,854,775,807. | 8 |
int | Any integer with absolute value less than 2,147,483,647. | 4 |
smallint | Any integer with absolute value less than 32,767. | 2 |
tinyint | Any integer between 0 and 255. | 1 |
The absolute value of a number is its positive part (so, for example, the absolute value of -7 is 7).
If you're storing a number which isn't always a whole number you can choose between decimal, numeric, money, smallmoney, real and float, as shown below.
Decimal and Numeric Data Types (Precise Numbers)
A decimal has a maximum precision of 38 digits, divided between those before the decimal pont and those after it in a manner chosen by you. This is best understood by example:
Data type | What it would contain | Example |
|---|
decimal(10,2) | 8 digits before the decimal place and 2 after | 31,415,926.54 |
decimal(4,1) | 3 digits before the decimal place and 1 after | 314.2 |
decimal(5,5) | 5 digits, all after the decimal place | .31416 |
decimal(4,0) | 4 digits before the decimal place (ie an integer) | 3,142 |
The default specification for a decimal data type is decimal(18,0) - if you miss out the two arguments, this is what will be used. This seems a strange default, as it is virtually equivalent to a bigint.
A numeric data type is to all intents and purposes identical to a decimal one. The decimal data type is, however, more modern. If you're wondering why there are 2 data types which do the same thing, you're not the only one!
The important point behind decimal data types is that they will always yield exactly the right answer with multiplication, addition and subtraction. For example, 3.14 + 3.14 = 6.28. This precision is not true of float and real numbers.
The decimal data type uses the following number of bytes:
Number of digits (first argument) | Number of bytes used |
|---|
1-9 | 5 |
10-19 | 9 |
20-28 | 13 |
29-38 | 17 |
You can use the decimal data type to store currency amounts (although you could also use money or smallmoney as shown below to do the same thing).
Float and Real Data Types
There is a crucial difference between decimal/numeric and float/real data types:
float and real data types do not hold the exact number.
Normally the difference between the correct answer and the one returned by SQL is so tiny as to be irrelevant, but it's worth noting that there will be one.
For this reason you should avoid ever testing whether one float number equals another (instead you could test whether the difference between them is smaller than some pre-set tolerance).
You can vary the degree of precision that is used with float by using an argument, although in practice there are only two possible values:
Value of argument | Example | Number of bits used |
|---|
Between 1 and 24 | float(17) | 24 |
Between 25 and 53 | float(38) | 53 |
If you miss out the argument, float(53) will be used. This will allow you to store any number, from the astronomically large to the nanotechnologically small (the actual limits are from 10 to the 308 to 10 to the -308, but these numbers are respectively so large and small as to be to all intents and purposes infinite/infinitesimal).
Another pointless duplication: real is equivalent to float(24). The easiest way to cope with all these rules is just to use float where you want to store very large or very small numbers..
Money and Smallmoney Data Types
To complete the numeric data type picture, SQL also allows you to use money and smallmoney data types to hold currency values. I'm not sure I can see the point of them, but for the sake of completeness ...
The size limits for the two data types is as follows:
Data type | Size limit |
|---|
money | Absolute value less than 922,337,203,685,477.5807 (just over 900 million million). |
smallmoney | Absolute value less than 214,748.3647 (just over 200,000). |
The data types take up 8 and 4 bytes respectively, and are used to measure currency amounts. They are (as Microsoft put it on their website) "accurate to a ten-thousandth of the monetary units that they represent".
The point behind money and smallmoney seems to be that they allow culture-specific information (so that you can use, say, dollar signs in numerical amounts). There are two problems with this. Firstly, you can with the decimal data type too, as the folloiwng SQL shows:
CREATE TABLE tblPayments(
Purchase varchar(100),
Amount decimal
)
INSERT INTO tblPayments (
Purchase,
Amount
) VALUES (
'Fluffy toy',
$99.99
)
SELECT * FROM tblPayments
This would show the following results:
The dollar sign is accepted, but not included in the number.
The other problem is this: since when did SQL Server care about the user interface side of computing (how things look on screen)? Surely it's your application which would take care of this?
I don't understand why you would use money in preference - say - to decimal(19,4), which would seem to me (and to every other source I've found) to do exactly the same thing. My advice would be to ignore money and smallmoney.
I have no intention of repeating my colleague Andrew's excellent and in-depth earlier blog on this - so here's a link to it!
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 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:
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:
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:
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.
I think it's a fair statement to say that SQL is fussy about its data types. In SQL, you definitely can't put a round peg in a square hole, or mix apples and pears (although you can still mix your metaphors, as this sentence shows).
When you Need to Convert Data Types
Nearly always, is the short answer. A longer answer is that you need to convert data types when either SQL will try to do it for you (and get it wrong), or when SQL will generate an error otherwise. Here's an example of each.
In the following code, we've created a purchase of 2 toys costing 9 units each. The average value of each item should be 4.5:
CREATE TABLE tblPayments(
Purchase varchar(100),
Quantity int,
Amount int
)
INSERT INTO tblPayments (
Purchase, Quantity, Amount
) VALUES (
'Fluffy toy', 2, 9
)
SELECT
Purchase,
Amount / Quantity AS Value
FROM
tblPayments
However, what this code actually shows is:
SQL sees an int data type and assumes that everything is an integer.
In the example above SQL converted the data type wrongly. Sometimes, you'll just get an error when you try to use mixed data types. Suppose you try to show the price above in text format:
SELECT
Purchase,
Amount + ' dollars'
FROM
tblPayments
This SQL will yield the following error:
SQL sees the Amount is an integer, and thinks the dollars text string should be too.
The easiest answer is to make sure in SQL that you are ALWAYS comparing data of exactly the same type.
The Conversion Functions
SQL has two separate conversion functions: CAST and CONVERT. The syntax of CAST is:
CAST(What you're converting, data type you're converting to)
Here's how we could have solved our two problems above. First the inaccurate calculation of the average value of the purchase:
SELECT
Purchase,
CAST(Amount AS float) /
CAST(Quantity AS float) AS Value
FROM
tblPayments
Now the concatenation error:
SELECT
Purchase,
CAST(Amount AS varchar(10)) + ' dollars'
FROM
tblPayments
This would give:
The integer has been converted to a text string before concatenation.
If you're concatenating text it's usually best to use varchar and not char, as char would put aside a fixed number of characters, leading to blanks in your final displayed answer.
The CONVERT Functon
The CONVERT function works well for dates, but CAST is easier to use for numbers and text (being much more intuitive). The syntax of CONVERT is:
CONVERT(Data type to convert to, what you're converting)
So we could have written our examples above as:
SELECT
Purchase,
CONVERT(float,Amount) /
CONVERT(float,Quantity) AS Value
FROM
tblPayments
and:
SELECT
Purchase,
CONVERT(varchar(10),Amount) + ' dollars'
FROM
tblPayments
CAST and CONVERT do the same thing, but CAST puts the arguments in a more logical order!
