MS SQL Server Interview Question:

How Extra Digits Are Handled with NUMERIC Data Type Literals?

Submitted by: Administrator

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

Submitted by: Administrator