MSSQL: Got arithmetic overflow for INT ?

I had a nasty bug today in SQL with INT overflow:

SELECT SUM(COL)
  FROM MyTable

When the sum gets bigger than 2,147,483,647 in sql runtime it overflows.

"Arithmetic overflow error converting expression to data type int."

The bad thing is that I knew it. The SUM was also inside more complicated expression with assign to BIGINT parameter :(

BTW, SUM in T-SQL returns only INT, DECIMAL, MONEY, FLOAT according to BOL. So the fix should be like so:

SELECT SUM(CAST(COL AS DECIMAL))
  FROM MyTable

Which looks wierd at a 1st glance, but is correct. Another point: SUM can also return BIGINT in MSSQL 2005 like so:

SELECT SUM(CAST(COL AS BIGINT))
  FROM MyTable

But it is not documented...

Related Posts:

• MSSQL 2005 Express Edition - unbeatable
• T-SQL: The Rozenshtein Method
• Always Use Stored Procedures in MSSQL
• MSSQL 2005 Service Pack 1 is available
• Enterprise Library: Bug with decimal in DataAccess block
• Microsoft Query Optimization Team is Blogging
• MSSQL 2005: Paging with ROW_NUMBER is the fastest
• MSSQL: Managing Schema Changes on Large Tables
• The most important thing in SQL
• T-SQL: Get only Date from DateTime