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