SQL Server Decimal(9, 0) vs INT

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 DECIMAL over INT:

• 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?

I agree that there are no real benefits in terms of storage space as long as you are comparing DECIMAL(9, 0) vs INT or DECIMAL(18, 0) vs BIGINT. (Within a single byte.)

In terms of processing, like @Andriy says the DECIMAL will naturally divide into a type that doesn’t lose the fractional part, if that’s important to you.

On the other hand, working with native INT types is much faster from a numerical standpoint if you are doing a lot of SUM()s or comparisons (such as searching on the values) as they are pipelined more efficiently by the CPU. An int comparison is two assembly opcodes (MOV, CMP) but any decimal comparison will be many, many more.

It looks like there will be no benefits in terms of storage space.

If you client is concerned that you values will be bigger than 2^32-1 (maximum positive value integer can store) then you should consider moving to BigInt – with is 64 bits (8 bytes).