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 (this blog)
- Date and Time Data Types in SQL
- Logical, Boolean, Yes/No or Bit Data Types in SQL
- Converting between Data Types
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.
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|
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:
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 a new table to hold purchases
CREATE TABLE tblPayments(
-- add a record into it
INSERT INTO tblPayments (
) VALUES (
-- display this record
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.