The Decimal or Numeric data types SQL server represents the numbers that contain the integer part & fractional part separated by a decimal point. The numbers include both negative & positive numbers. Both Decimal & Numeric data types are the same, you can interchange them.
Table of Contents
Defining a Decimal Number
Use the following syntax to define a Decimal Number
1 2 3 4 5 | decimal[ (p[ ,s] )] OR numeric[ (p[ ,s] )] |
The definition contains two parts. One is (p) Precision & the other one is (s) scale
p (precision)
The Precision defines the maximum number of digits that you can store. The number includes both integer & fractional parts.
For Example 123.45 has a precision of 5 as there is a 5 digits in that number.
The SQL Server allows a minimum precision is 1 and maximum precision of 38. The default is 18.
s (scale)
The scale defines the number of decimal digits that you can store. There is no minimum or maximum here. For Example, the decimal(5,2) column will store the number in 2 decimal places.
If you try to insert a number, which has more decimal position than the column permits, SQL Server will round it off. For Example, 123.456 is rounds to 123.46 in a decimal(5,2) column.
If the number does not have a decimal position (like an integer number), then SQL Server will add implicitly .00 to the number. For Example, 123 will become 123.00
Maximum Limit
The precision and scale determine the maximum limit that you can store in decimal data type.
The maximum number of digits to the right of the decimal point (the integer part) is equal to the precision minus scale (p-s). For Example in a Decimal(5,2) column the integer portion can contain only 3 digits (5-2).
Hence in decimal(5,2), you can store from number -999.00 to 999.99. For decimal(38,2), you can store from number -999999999999999999999999999999999999.00 to 999999999999999999999999999999999999.99.
If you want to store number larger than that, you need to use the float data type
Size in Bytes
The precision determines the storage size, that a decimal number takes in the disk. The bytes depends on the precision used.
The precision from 1 to 9 will require 5 bytes of disk space. For Example Decimal(3,2) , Decimal(5,2) & Decimal(9,2) will all use 5 bytes of disk space, although the Decimal(9,2) can store more numbers.
| Precision | Storage bytes |
|---|---|
| 1 – 9 | 5 |
| 10-19 | 9 |
| 20-28 | 13 |
| 29-38 | 17 |
Creating a Decimal Column
The following shows how to create a table with decimal columns.
1 2 3 4 5 6 7 8 9 10 | CREATE TABLE TestDecimal ( Col1 DECIMAL(5,2) , Col2 NUMERIC(7,2) Col3 DECIMAL, Col4 DECIMAL(7), ); |
Using only decimal without any precision or scale, will create a column decimal(18,0).
Inserting Decimal Value
Insert a new value using the insert statement.
1 2 3 | insert into TestDecimal (col1,Col2,col3) Values (123.45, 12345.67,12345) |
1 2 3 4 5 6 7 | select * from TestDecimal *Result col1 col2 col3 123.45 12345.67 12345 |
But, if you try to insert a value that is more than the column limit, SQL Server throws the Arithmetic overflow error.
1 2 3 4 5 6 7 8 | CREATE TABLE TestDecimal1 ( Col1 DECIMAL(5,2) , ); insert into TestDecimal1 (col1) Values (1234.56) |
Arithmetic overflow error converting numeric to data type numeric. The statement has been terminated.
But, if you have more digits in the fraction position, SQL Server rounds them off. For Example .456 is rounded off to .47 as the column allows only 2 decimal points.
1 2 3 4 5 6 7 8 | insert into TestDecimal1 (col1) Values (123.456) select * from TestDecimal1 *Result 123.46 |
Reference
decimal and numeric (Transact-SQL)
Read More
