SQL Server FAQ - Overflow Errors and Rounding on NUMERIC Literals

Home >> FAQs/Tutorials >> SQL Server FAQ

By: FYIcenter.com

How Extra Digits Are Handled with NUMERIC Data Type Literals?

Exact numeric data types defined with NUMERIC(p,s) has two limits defined by two parameters: p (precision) and s (scale):

Maximum number of digits of the integer part (digits before the decimal point) is defined as p-s. If this limit is passed, SQL Server will give you an arithmetic overflow error.
Maximum number of digits of the decimal part (digits after the decimal point) is defined as s. If this limit is passed, SQL Server will perform a round operation.

The tutorial exercise below gives an example of arithmetic overflow errors and rounding operations.

-- Exact numeric value
DECLARE @x NUMERIC(9,2); -- NUMERIC(p,s)
SET @x = 1234567.12;
SELECT @x;
GO
1234567.12

-- Overflow error: p-s limit passed
DECLARE @x NUMERIC(9,2); -- NUMERIC(p,s)
SET @x = 123456789.12;
GO
Msg 8115, Level 16, State 8, Line 2
Arithmetic overflow error converting numeric to data type
   numeric.

-- Rounding on extra decimal digits: s limit passed
DECLARE @x NUMERIC(9,2); -- NUMERIC(p,s)
SET @x = 1234567.12345;
SELECT @x;
GO
1234567.12

(Continued on next topic...)