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


Leave a Comment
Your email address will not be published. All fields are mandatory.
NB: Comments will only appear once they have been moderated.

Your SQL Trainer
Kevin Urquhart

I am a SQL Server DBA, Architect, Developer, and Trainer for SQL Training. This is my blog in which I’m simply trying to share my SQL knowledge and experiences with the world.

Kevin Urquhart
SQL Server Consultant, London

Categories


Archives


Copyright © 2018 London SQL Server Training Courses @ SQL Training | Website Design From Scott Heron