SQL Puzzle 9: How to Generate Arithmetic Overflow Error in SQL Server – A Fun SQL Challenge

Ever encountered an arithmetic overflow error while inserting data into a DECIMAL column in SQL Server?

In this SQL puzzle, we intentionally trigger this error by inserting values to Decimal Column and understnad the Behaviour of Decimal

Expected OutPut using MS SQL:

Tap For Closer Look

Input Table Script:

CREATE TABLE #ArthOverFlow
(
	ID INT,
	Valuess DECIMAL(10,4)
)

So, Lets see the below insert statments and comment which insert statment triggers the Arthematic over flow error for this table

INSERT INTO #ArthOverFlow VALUES  (1,50000.44542)
INSERT INTO #ArthOverFlow VALUES  (2,50.4454256)
INSERT INTO #ArthOverFlow VALUES  (3,50.445425656)
INSERT INTO #ArthOverFlow VALUES  (4,5000099.454)

Please try to answer the question, if you still not able to get it click below show button to see the answer.

INSERT INTO #ArthOverFlow VALUES  (4,5000099.454)

The fourth statement results in an arithmetic overflow error. To understand why, let’s break down the DECIMAL(10,4) data type:

• Precision (10): The total number of digits allowed (both before and after the decimal).
• Scale (4): The number of digits allowed after the decimal point.

This means the column can store up to 10 digits, with 4 reserved for decimal places. The remaining 6 digits are allocated for the whole number part.

Now, let’s analyze our insert statement:

INSERT INTO #ArthOverFlow VALUES (4, 5000099.454)

• The number 5000099.454 has 7 digits before the decimal, which exceeds the 6-digit limit for whole numbers.
• Since SQL Server cannot accommodate the extra digit, it triggers an arithmetic overflow error automatically.

This helps you understand how precision and scale impact data storage in SQL Server and why exceeding these limits leads to errors.