Numeric DataTypes
Published: Sep 02, 2013
I will presume that you know the most common numerical datatypes (if not, please refer to the list here).

This article will simply cover a basic manipulation of some numerical datatypes to highlight some key uses and pitfalls.

#### Integers

The main datatypes in this category are:
• tinyint
• smallint
• int
• bigint

These can all be used in the expected mathematical manner, for instance using addition, subtraction, multiplication, and division of whole numbers.

The most common error you may see when using a certain datatype is an overflow. This would occur when you exceed the limit of the datatype. For example:

`declare @myNumber tinyintset @myNumber = 111` However:

`declare @myNumber tinyintset @myNumber = 999` This will happen with all datatypes if you exceed their specified limits.

Another common error surrounds a misunderstanding that these are integer values only and cannot be used as decimals. Therefore this can cause calculation errors which are hard to find when using very long stored procedures to produce reporting figures and statistics. A good example of this is as follows:
`select 9 / 2` As you can see, instead of giving the accurate answer of 4.5, SQL will round this down and give the whole number 4. Definitely not ideal and something to look out for.

The last one that I will mention here is to note that you may want to output a sentence to a user instead of simply a figure, but this can cause problems as an integer cannot be natively combined with text. It must be converted...

Incorrect usage:

`declare @myNumber tinyintset @myNumber = 12select 'I like the number ' + @myNumber` Correct usage:

`declare @myNumber tinyintset @myNumber = 12select 'I like the number ' + convert(varchar, @myNumber)` #### Decimals

The main datatypes in this category are:
• float
• decimal
• real
• numeric

These have similar problems to the integers with respect to combining them with text or exceeding their limits. However, unlike the integer, they are natively decimal and therefore can prove much more accurate for calculations:

`declare @myNumber decimal(12, 4)set @myNumber = 9select @myNumber / 2` NB: Comments will only appear once they have been moderated. 