Sql-server – Arithmetic overflow on SELECT query

sql server

I encountered an arithmetic overflow in a simple SELECT statement. Query was as below e.g.

SELECT [SaleValue] FROM Sales

[SaleValue] was of data type decimal(9,0) and not a computed column.

The reason this happened was because somehow the column had a row where this field was storing a value GREATER than specified datatype, e.g. decimal(10,0).

I could only get the select to work when I increased the size of the column. The table in question has two other instances in two other columns and rows.

How was this situation possible? How was an out of range value saved in the column in the first place?

I'm using Microsoft SQL server + this is a base table, not a view.

Best Answer

This can happen in several ways, for example as described in Troubleshooting DBCC error 2570 in SQL Server 2005 and later versions:

Invalid or out-of-range data may have been stored in the SQL Server database in earlier versions for the following reasons:

  • Invalid data was present in the source while using bulk insert methods, such as the bcp utility.
  • Invalid data was passed through RPC event calls made to SQL Server.
  • Other potential causes of physical data corruption left the column value in an invalid state.

That article contains a lot of useful information on the topic. For the basics, see the documentation for DBCC CHECKDB and the DATA_PURITY option in particular.

Related Question