What is numeric(18, 0) in SQL Server 2008 r2

sql

data-types

numeric-types

sql-server

byAnton Shumikhin·Jan 10, 2025

NUMERIC(18, 0) in SQL Server characterizes a numeric data type with up to 18 digits allowed. All these digits must be situated before the decimal point, essentially storing integer values with zero decimal fractions.

Example:

-- I'm one big fat numeric value. Don't I look zesty?
DECLARE @BigNatural NUMERIC(18, 0) = 123456789012345678;
SELECT @BigNatural; -- Outputs: 123456789012345678

This becomes handy whenever you're dealing with large precise values without a fractional component, such as in financial calculations, code identifiers, or other instances where you require exact numeric values.

Numeric type in a nutshell

NUMERIC and DECIMAL data types in SQL Server are practically identical twins. Both comprise the ability to define a fixed precision and scale. When you pronounce numeric(18, 0), you're allotting a precision of 18 (total digits) and a scale of 0 (digits after decimal). This combo permits storing any integer value within the chunky range from negative 999,999,999,999,999,999 to positive 999,999,999,999,999,999.

Dealing with negative and positive integers

Fear not, a NUMERIC(18, 0) column can grace both negative and positive integers. Here's the proof:

-- Oh no, I'm in the red! Negative values allowed.   
INSERT INTO TableWithNumbers (NumberColumn) VALUES (-123456789012345678);
-- Phew, back in black! Positive values allowed.
INSERT INTO TableWithNumbers (NumberColumn) VALUES (123456789012345678);

The space for numeric types

The NUMERIC types utilize a variable storage contingent on the precision need. For our NUMERIC(18, 0), it occupies only 9 bytes of space. This is because digits love to pair up in storage, and an extra byte is reserved for the sign.

Distinctions and misinterpretations

Numeric vs Integer

While NUMERIC(18, 0) and BIGINT can both store large numbers, their storage space varies. BIGINT is limited to 8 bytes with a max value of 2^63-1 or -2^63, contrary to numeric(18, 0) which has a wider capacity and precision.

Numeric vs Floating-point

Unlike floating-point types (FLOAT, REAL), NUMERIC(18, 0) does not expose you to approximation errors thanks to its exact precision - making it ideal for use with financial and accounting applications where precision is key.

The Scale Change

If the scale trips (like in NUMERIC(18, 5)), you're allowing for fractional numbers, yet reducing digits available for whole numbers to 13 (18 - 5).

explain-codes / Sql / What is numeric(18, 0) in SQL Server 2008 r2

Linked

Why doesn't SQL Server support unsigned datatype?



Convert a string to int using SQL query



Write a number with two decimal places SQL Server



Remove trailing zeros from decimal in SQL Server



How to convert integer to decimal in SQL Server query?

