One of our customers uses for some columns the datatype
DECIMAL(18,0) in his SQL Server 2008R2 database. Because the columns grow quite slowly, he recently proposed to change the datatype to
DECIMAL(5,0) to regain some storage.
According to the MSDN library, the storage space of the
DECIMAL(5,0) datatype is, just like the
DECIMAL(9,0) datatype, 5 bytes.
INT is 1 byte smaller, but can store everything in the range of -2^31 to 2^31 instead of the -99,999 to 99,999 which
DECIMAL(5,0) can store. Even the largest
DECIMAL which fits into 5 bytes (
DECIMAL(9,0)) can store only integers in the range -999,999,999 to 999,999,999 (which is less than half of the range
INT offers in 4 bytes).
I can think of two "benefits" of using
- The ability to add scale afterwards, without using more storage space
- The ability to scale the precision up to 38 digits, without altering data type
but these aren't real benefits in my opinion:
- Adding scale to integers does only make sense in very few cases (in most cases where scale does make a difference, it could also be added beforehand)
- SQL Server sees every precision / scale combination as a different data type, so the datatype isn't left alone when increasing the precision or scale.
This makes me wonder: what is the added benefit of a
DECIMAL(5,0) datatype for integers?