Worth Corner, Crawley, RH10 7SL

Support Hours - Mon-Sat, 8.00-18.00

Welcome to our SQL Server blog

This blog was created in order to pass on some of our knowledge and advice in the hope that the community might find it useful.

Obviously Microsoft SQL Server is our area of expertise and therefore this is our most thorough blog with everything from the basic through to the the incredibly advanced and, as such, we should have something for everyone within our pages.


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.

SQL  World  CEO
Kevin  Urquhart

iPhone Selfie

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

Categories


© Copyright 2020 SQLTraining Ltd.