How Extra Digits Are Handled with NUMERIC Data Type Literals in SQL Server Transact-SQL?

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;
----------------------------
1234567.12

-- Overflow error: p-s limit passed
DECLARE @x NUMERIC(9,2); -- NUMERIC(p,s)
SET @x = 123456789.12;
----------------------------
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;
----------------------------
1234567.12

 

⇒Variables and Data Types in SQL Server Transact-SQL

⇒⇒SQL Server Transact-SQL Tutorials