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

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

- 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