SQL DECIMAL Data Type

The DECIMAL data type is an exact number with a fixed precision and scale.

Precision is an integer representing the total number of digits allowed in a column.

Scale is also an integer value that represents the number of decimal places.

Example

#

A table with a DECIMAL column.

CREATE TABLE DemoTable  
( 
  Id INT IDENTITY, 
  Employee VARCHAR(100),
  Salary DECIMAL(10,2)
);
GO  

INSERT INTO DemoTable VALUES ('Harold Smith', 2350.2);  
INSERT INTO DemoTable VALUES ('Robert Johnson', 1874.667);  
INSERT INTO DemoTable VALUES ('Janice Lopez', 869.352);
INSERT INTO DemoTable VALUES ('Kelly Wilson', 2500.50); 
INSERT INTO DemoTable VALUES ('Grace Taylor', 900); 
GO  

SELECT * FROM DemoTable;
GO

DROP TABLE DemoTable;
GO
Result:  5 records
Id Employee Salary
1 Harold Smith 2350.20
2 Robert Johnson 1874.67
3 Janice Lopez 869.35
4 Kelly Wilson 2500.50
5 Grace Taylor 900.00

Employee salary can hold up to 10 digits and 2 decimal places.

If the value is more than 2 decimal places, it will be rounded.

If the value is less than 2 decimal places, a zero is added to complete the 2 digits.


Syntax

#

Syntax of DECIMAL.

DECIMAL(precision, scale)

precision -- the maximum number of digits the decimal may store. Precision includes both left and right side of decimal point. It accepts values from 1 to 38. The default is 18.

scale -- optional, specifies the number of digits after the decimal point. Scale must be between 0 up to the same value as the precision.

The storage used by decimal depends on the precision:

Precision Storage (bytes)
1-9 5
10-19 9
20-28 13
29-38 17

More Examples

DECIMAL with PRICING

PRODUCT
Id
ProductName
SupplierId
UnitPrice
Package
IsDiscontinued
Problem: List all products and their prices.
SELECT ProductName, UnitPrice
  FROM Product
Result:  78 records
ProductName UnitPrice
Chai 18.00
Chang 19.00
Aniseed Syrup 10.00
Chef Anton's Cajun Seasoning 22.00
Chef Anton's Gumbo Mix 21.35

DECIMAL with OTHER EXACT NUMERIC TYPES

This example shows that decimal and numeric values are identical.
CREATE TABLE DemoTable  
( 
  MyDecimal DECIMAL(10,3),
  MyNumeric NUMERIC(10,3)
);
GO  

INSERT INTO DemoTable VALUES (1899.982, 1899.982);  
GO  

SELECT * FROM DemoTable;
GO

DROP TABLE DemoTable;
GO
Result:  1 record
MyDecimal MyNumeric
1899.982 1899.982

You may also like



Guides


vsn 3.1