Decimal & Numeric Data Types in SQL Server

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.

Defining a Decimal Number

Use the following syntax to define a Decimal Number

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.

PrecisionStorage bytes
1 – 95
10-199
20-2813
29-3817

Creating a Decimal Column

The following shows how to create a table with decimal columns.

SQL Server Decimal Data Type Example

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.

But, if you try to insert a value that is more than the column limit, SQL Server throws the Arithmetic overflow error.

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.

Reference

decimal and numeric (Transact-SQL)

Read More

Leave a Comment

Your email address will not be published.

Scroll to Top