SQL Numeric Data Types

The numeric data types in SQL Server store exact and approximate numeric values. Choose the appropriate type based on precision, scale, and storage requirements.

Type	Storage Size	Precision	Scale	Description
`tinyint`	1 byte	3	0	Integer data from 0 to 255.
`smallint`	2 bytes	5	0	Integer data from -32,768 to 32,767.
`int`	4 bytes	10	0	Integer data from -2^31 to 2^31‑1.
`bigint`	8 bytes	19	0	Integer data from -2^63 to 2^63‑1.
`decimal(p,s)`	Variable (1‑16 bytes)	1‑38	0‑38	Fixed‑precision and scale numeric data.
`numeric(p,s)`	Variable (1‑16 bytes)	1‑38	0‑38	Synonym for `decimal`.
`float[(n)]`	4 or 8 bytes	53	Approximate	Floating‑point numeric data.
`real`	4 bytes	24	Approximate	Floating‑point numeric data with less precision than `float`.
`money`	8 bytes	19	4	Currency values ranging from -922,337,203,685,477.5808 to 922,337,203,685,477.5807.
`smallmoney`	4 bytes	10	4	Currency values ranging from -214,748.3648 to 214,748.3647.

Example: Using `decimal` for Precise Financial Calculations

CREATE TABLE dbo.Invoice(
    InvoiceID INT PRIMARY KEY,
    Amount    DECIMAL(12,2) NOT NULL,
    TaxRate   DECIMAL(5,4)  NOT NULL,
    Total     AS (Amount + (Amount * TaxRate)) PERSISTED
);

INSERT INTO dbo.Invoice (InvoiceID, Amount, TaxRate)
VALUES (1, 1245.67, 0.0750);

SELECT * FROM dbo.Invoice;

Best Practices

Prefer decimal/numeric for monetary values to avoid rounding errors.
Use the smallest integer type that can accommodate your data to save space.
Avoid float and real for exact calculations; reserve them for scientific data.
Define explicit precision and scale for decimal/numeric to control storage.

SQL Numeric Data Types

Example: Using decimal for Precise Financial Calculations

Best Practices

Example: Using `decimal` for Precise Financial Calculations