Numeric DataTypes
Published: 3rd May 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.


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


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)


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

NB: Comments will only appear once they have been moderated.