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

Â

2017-04-19, 1124👍, 0💬

Popular Posts:

How To Create a Stored Function in Oracle? A stored function is a function with a specified name and...

What Is an Oracle Instance in Oracle? Every running Oracle database is associated with an Oracle ins...

How To Convert Binary Strings into Hexadecimal Character Strings in SQL Server? When a query returns...

How to run Queries with "sqlcmd" tool in SQL Server? "sqlcmd" is a client tool that you can use to i...

What Are Bitwise Operations in SQL Server Transact-SQL? Bitwise operations are binary operations per...