Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
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 '12 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 '12 at 13:25
2  
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 '12 at 13:42
6  
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 '12 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 '13 at 12:10
show 1 more comment

2 Answers

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.

share|improve this answer
    
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 '13 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 '13 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 '13 at 8:50
add comment

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

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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