Arithmetic overflow on SELECT query

Question

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.

• Damien_The_Unbeliever: The only possible way I can think of maybe forcing this to happen would be to edit system tables via the DAC - a fairly violent process that someone would hopefully be able to tell you if it had been done to this DB. Even then I'm not sure it would work out at all well (or even be possible). Outside of that, we really need a repro script to see this situation for ourselves and I suspect creating the repro, if possible, could easily take years of experimentation.
• Damien_The_Unbeliever: Even worse, just remembered that 9/10 is a cutover point for the storage size of decimal- a decimal(9,0) should occupy 5 bytes, a decimal(10,0) 9. So I think it's less likely that you could do this via editing the system tables since you won't have the correct storage size for the data in each row.
• stubs: @Damien_The_Unbeliever Have no idea how to reproduce. It took me an hour to figure out what happened. Seeing it was like seeing dry water or cold heat. Honestly, it left me stumped.

Answer 1

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.