557 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 ...
TSQL data types  int, float, decimal, varchar, etc. Part three of a sixpart 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. 
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).
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.
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 

19  5 
1019  9 
2028  13 
2938  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).
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 preset 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..
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 tenthousandth of the monetary units that they represent".
The point behind money and smallmoney seems to be that they allow culturespecific 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(
Purchase varchar(100),
Amount decimal
)
 add a record into it
INSERT INTO tblPayments (
Purchase,
Amount
) VALUES (
'Fluffy toy',
$99.99
)
 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.
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.