• 13. 1. 2020
• Jan Zedníček - Data & Finance
• 0

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

Rate this post

### Jan Zedníček - Data & Finance

My name is Jan Zedníček and I work as a freelancer. I have been working as a freelancer for many companies for more than 10 years.
I used to work as a financial controller, analyst and manager at many different companies in field of banking and manufacturing. 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 and to put them forward to anyone.