Storing small negative values

Question

Note: I realize the question here won't grossly impact performance or storage size in my database, but I'm interested hearing expert opinions (if there are any).

I'm writing an application that needs to perform an operation on a datetime value and store the resulting offset in a database. I expect the offsets to stay the same (around -3), but because the source date (the beginning date of each term) is set by a college-level committee, and because my application is dealing with contacting students at regular, predictable intervals (which would also be set by a committee) it's possible that it could be between -30 and 30.

I originally intended to store the resulting offset as a tinyint, but since it's unsigned in SQL Server 2008, I'm not sure what would work best here. Should I use a smallint, or something like a numeric(2,0)?

Answer 1

Depending on your size constraints, I would lean towards a less error prone implementation, and give yourself enough leeway on min/max values. Use a datatype that will meet your needs today, and in the future, for all possible cases. If you feel like you may need the storage, give yourself the larger datatype.

Either case, your data access layer must accommodate the database column size.

Answer 2

Numeric(2,0) will actually take 5 bytes of storage, so I would suggest smallint, which only takes 2 bytes.

Answer 3

numeric(2,0) has a storage size of 5 bytes, vs. 2 bytes for smallint, so just use smallint.

Answer 4

Just use a smallint. Don't overthink it.

(Insert standard text about premature optimization here.)