SQL NUMERIC Data Type

The NUMERIC 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 NUMERIC column.

CREATE TABLE DemoTable  
( 
  Id INT IDENTITY, 
  Employee VARCHAR(100),
  Salary NUMERIC(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 NUMERIC.

NUMERIC(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 numeric depends on the precision:

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

More Examples

NUMERIC with OTHER EXACT NUMERIC TYPES

This example shows that numeric and decimal 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