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