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 tinyint
set @myNumber = 111
However:
declare @myNumber tinyint
set @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 tinyint
set @myNumber = 12
select 'I like the number ' + @myNumber
Correct usage:
declare @myNumber tinyint
set @myNumber = 12
select '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 = 9
select @myNumber / 2