14

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?

Improve this question
7
  • 1
    One benefit can be the five digit limit. But I wonder how much storage space can you spare with such a change.
    – dezso
    Jul 16, 2012 at 8:16
  • 3
    IMO, if you just need to make sure the column values fall within a range, use a check constraint. I think the only consideration here would be if there's the possibility of this column requiring fractional values in the future.
    – Jon Seigel
    Jul 16, 2012 at 13:25
  • 3
    If they're worried about storage space, they're better suited using compression than this. There is no tangible benefit to using decimal(x, 0) instead of int/bigint.
    – Robert L Davis
    Jul 16, 2012 at 13:42
  • 7
    Another difference between decimal(x,0) and an integer type shows itself in arithmetical division. If you divide an int by an int, you get an int. If you divide a decimal(x,0) by an int, you get a decimal(x+6,6).
    – Andriy M
    Aug 19, 2012 at 17:09
  • @AndriyM, I think this is the greatest benefit. Rephrase it into an answer instead of a comment and I'll mark it as answer.
    – vstrien
    Oct 14, 2013 at 12:10

2 Answers 2

Reset to default
7

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.

Improve this answer
3
  • Your answer makes sense, but a processor doesn't have any knowledge of datatypes. So one datatype of 4 bytes, is compared just as fast as another datatype of 4 bytes. Can you show (with a repeatable performance test, for example) that there's a performance hit in using DECIMAL(9, 0) instead of an INT?
    – vstrien
    Oct 5, 2013 at 7:35
  • 2
    I thought I could, but I'll admit that my tests are inconclusive. Perhaps SQL is doing an optimization to reduce the problem to integer math. My testbed: ` --Int comparison SET @StartTime = SYSDATETIME() WHILE (@CounterINT < @SizeINT) SET @CounterINT = @CounterINT + 1 PRINT 'INT took ' + CONVERT(VARCHAR(20), DATEDIFF(millisecond, @StartTime, SYSDATETIME())) + ' milliseconds' --Decimal SET @StartTime = SYSDATETIME() WHILE (@CounterDEC < @SizeDEC) SET @CounterDEC = @CounterDEC + 1 PRINT 'DEC took ' + CONVERT(VARCHAR(20), DATEDIFF(millisecond, @StartTime, SYSDATETIME())) + ' milliseconds' `
    – Chris Chubb
    Oct 23, 2013 at 18:13
  • I think SQL Server optimizes something there indeed. Despite the optimization DECIMAL still takes usually somewhat longer (at least on my development system). In a run of 10 tests around 51 ms DEC vs. 46 ms INT.
    – vstrien
    Oct 24, 2013 at 8:50
2

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).

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.