The background.  We send a several dozen different sorts of communications to clients, and for emails, log which were sent and which failed.  Depending on the type of communication, failing emails are attempted to be automatically resent between 0 and 10 times, and a resend count kept.

The code had been in place for some time and was all working fine.  However, the other day I was doing some stats on historical emails, and got an Arithmetic overflow error' for some data sent somewhat over a year ago.

Here's an abridged version of the setup.  In table CommunicationDefn, there is

CommunicationID Int,
CommunicationName Varchar(50),
ResendLimit TinyInt,
etc.

In table CommunicationLog, there is

CommunicationID Int,
ResendCount TinyInt,
etc.

I was actually using a view, CommunicationStatus, linking these, but I couldn’t see anything immediately wrong with it or the underlying data.  I eventually resorted to a 'binary chop' on the data by date to find the cause of the problem. It turned out that in the view there was a calculated field,

ResendLimit – ResendCount as RetriesLeft,

and for one now defunct communication, the ResendLimit was currently set to 2, but in one instance 3 had been sent before the limit had been reduced. This meant that RetriesLeft now had a value of -1, but negative values are not allowed for TinyInt, and this was the cause of the Arithmetic overflow.

Given the choice of casting to SmallInt in the view, or changing the definition, I decided to change the fields to SmallInt. The moral of the story: If an arithmetic calculation could be meaningful, you should probably use SmallInt rather than TinyInt data type, even if you think it will 'never' be negative.