Dofactory.com
Dofactory.com
Earn income with your data and sql skills
Sign up and we'll send you the best freelance opportunities straight to your inbox.
We're building the largest freelancing marketplace for people like you.
By adding your name & email you agree to our terms, privacy and cookie policies.

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



Last updated on Dec 21, 2023

Earn income with your data and sql skills
Sign up and we'll send you the best freelance opportunities straight to your inbox.
We're building the largest freelancing marketplace for people like you.
By adding your name & email you agree to our terms, privacy and cookie policies.