Number Data Types in T-SQL
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.
