Collections:

Overflow and Rounding on NUMERIC Values in SQL Server Transact-SQL

Q

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

✍: FYIcenter.com

A

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

**⇒** Approximate Numeric Data Types in SQL Server Transact-SQL

**⇐** Overflow Errors with INT Values in SQL Server Transact-SQL

2017-04-19, 2203🔥, 0💬

Popular Posts:

How To Start Instance with a Minimal Initialization Parameter File in Oracle? The sample initializat...

How to connect SQL Server Management Studio Express to SQL Server 2005 Express in SQL Server? Once y...

How To Replace Given Values with NULL using NULLIF() in SQL Server Transact-SQL? Sometime you want t...

What is sqlservr.exe - Process - SQL Server (SQLEX?PRESS) in SQL Server? Process sqlservr.exe is the...

How To Set Up SQL*Plus Output Format in Oracle? If you want to practice SQL statements with SQL*Plus...