WiseOwl Training - Established 1992 Wise Owl Training

Established May 1992
Wise Owl Training
See 525 reviews for our classroom and online training
If you found this blog useful and youâ€™d like to say thanks you can click here to make a contribution. Thanks for looking at our blogs!

BLOGS BY TOPIC

BLOGS BY AUTHOR

BLOGS BY YEAR

T-SQL data types - int, float, decimal, varchar, etc.
Part three 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.

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.

# 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 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.