SQL Numeric Data Types – Big Overview

Last modified date:

I will follow the previous article on Data types. I will go through numeral data types and their properties. Thanks to correct data type selection you may be able to save disk storage. You only need to think about range of values of column which you want to represent. Then just select the correct type.

In SQL Server, we distinguish 2 numeral data type categories from the precision of numeral expression point of view.

  1. Exact numerics
  2. Approximate numerics

1 Exact Numerics

As the title suggests, this is a data type with ability to precisely represent a number. This category can be divided into:

A) INTEGER data type – list in the table

Data Type Range of values Size
BIGINT -2^63 (-9,223,372,036,854,775,808) to 2^63-1 (9,223,372,036,854,775,807) 8 Bytes
INT -2^31 (-2,147,483,648) to 2^31-1 (2,147,483,647) 4 Bytes
SMALLINT -2^15 (-32,768) to 2^15-1 (32,767) 2 Bytes
TINYINT 0 to 255 1 Byte

B) NUMERIC data type – it is distinguished by its need to have count of numbers and precision defined firmly and in advance – number of decimals. For example DECIMAL (10,2) means that the number is precise up to hundredths and consists of 8 numbers prior to decimal point = 10 numbers in total.

Data type Precision Size according to precision
NUMERIC 1-38 1-9 = 5 Bytes; 10-19 = 9Bytes; 20-28 = 13 Bytes; 29-38 = 17 Bytes
DECIMAL 1-38 1-9 = 5 Bytes; 10-19 = 9Bytes; 20-28 = 13 Bytes; 29-38 = 17 Bytes

I do not quite see the difference between those two types to be honest. Comment if you know it.

C) MONEY data type – they are meant to represent financial expressions or expression of currency exchange rates expression. It can be used anywhere where we need precision to 4 decimals.

Data type Range of Values Size
MONEY -922,337,203,685,477.5808 to 922,337,203,685,477.5807 8 Bytes
SMALLMONEY – 214,748.3648 to 214,748.3647 4 Bytes

2 Approximate numerics

As I suggested, this group of data types does not precisely represent precision of numbers. FLOAT(n) and REAL sql data type belong here:

Data type Range of Values Size
FLOAT – 1.79E+308 to -2.23E-308, 0 and 2.23E-308 to 1.79E+308 Depends on “n”
REAL – 3.40E + 38 to -1.18E – 38, 0 and 1.18E – 38 to 3.40E + 38 4 Bytes

Example of insufficient precision of FLOAT data type

IF we need to express number precisely, we should avoid approximate data types (FLOAT, REAL). I will show you screenshot from book Training Kit (Exam 70-461) Querying Microsoft SQL Server 2012″ that caught my eye (I recommend the book by the way). You can see on this example how unsuitable can usage of Approximate data type can be in some situations. NB if we take barcodes into consideration. 🙂

SQL Číselné datové typy

Source: Training Kit (Exam 70-461): Querying Microsoft SQL Server 2012; Autoři: Itzik Ben-Gan, Dejan Sarka, Ron Talmage; ISBN: 978-0-7356-6605-4

 

My name is Jan Zedníček and I work as a BI Developer at Kentico Software in Brno. Mostly you can see me there working in the office, but I also work partly as a freelancer. I have been working as a freelancer for many companies for more than 5 years, but Kentico is the matter of my heart. I used to work as a financial controller at companies like Aero Vodochody or Sberbank and I also used to be a bond program manager in Unicapital Investment group. When I am not at work, I like playing volleyball, chess, doing a workout in the gym and I enjoy tasting of best quality rums. I am trying to summarize all my knowledge on this website not to forget them (because of the rum effect, you know =) and to put them forward to anyone. Don´t worry about asking for help or writing some comments.

Category: SQL Tutorials Tags:

About Ing. Jan Zedníček - BI Developer, Finance controller

My name is Jan Zedníček and I work as a BI Developer at Kentico Software in Brno. Mostly you can see me there working in the office, but I also work partly as a freelancer. I have been working as a freelancer for many companies for more than 5 years, but Kentico is the matter of my heart. I used to work as a financial controller at companies like Aero Vodochody or Sberbank and I also used to be a bond program manager in Unicapital Investment group. When I am not at work, I like playing volleyball, chess, doing a workout in the gym and I enjoy tasting of best quality rums. I am trying to summarize all my knowledge on this website not to forget them (because of the rum effect, you know =) and to put them forward to anyone. Don´t worry about asking for help or writing some comments.

Leave a Reply

Your email address will not be published.

This site uses Akismet to reduce spam. Learn how your comment data is processed.