2

I am currently redesigning a legacy database to run on SQL Server 2005, and I want to replace most of the old float-columns with decimals.

Decimal(15,4) would be sufficent for my needs but the SQL Server doc states that this would use the same storage space (9 bytes) as a Decimal(19,4). Having a larger number in the same storage space seems like a good idea. So: is there any reason why I should not use the maximal precision of (19,4)? Performance drawbacks perhaps? Mind that I won't do extensive calculations in the database, only some SUMs or multiplications in queries.

Stephan Keller
  • 1,623
  • 11
  • 13

3 Answers3

4

The main reason to use a smaller data precision, despite using the same amount of storage space, is to convey meaning to future users of the system. Which is also why it's important to use appropriate data types - e.g. DECIMAL(15,4) for numbers, MONEY for money.

  • 1
    I like the idea of "conveying meaning" instead of "getting the most digits out of my storage bytes". – Stephan Keller Apr 30 '09 at 13:19
  • 1
    To a point: I've had this backfire on me before when using the a higher scale would have avoided refactoring, despite the correct analysis and requirements – gbn Apr 30 '09 at 13:25
2

For 4 decimal places, use money if you can

Reason: it's faster then decimal but is exact

Money, float, decimal article

Edit: based on comment

Interesting argument. I follow both blogs.

Note the examples here: different precision based on datatypes and precedence

declare @d decimal(19,4), @ddiv decimal(19,4)

set @d = 12.39
SET @ddiv = 1000

select (@d/@ddiv)*@ddiv, (@d/1000)*1000
--Gives   12.390000    12.390000000

Explained by my answer here to "T-SQL Decimal Division Accuracy"

Now, this is interesting. Decimal, different answers

declare @d decimal(19,4), @ddiv decimal(19,4)

set @d = 12.39
SET @ddiv = 1001

select (@d/@ddiv)*@ddiv, (@d/1001)*1001
--Gives   12.390000    12.389999622

Back to money: same answer for each

declare @d money, @ddiv money

set @d = 12.39
SET @ddiv = 1001

select (@d/@ddiv)*@ddiv, (@d/1001)*1001
--Gives   12.3123    12.3123

Moral: store in SQL, process in client languages...

Community
  • 1
  • 1
gbn
  • 422,506
  • 82
  • 585
  • 676
  • Thank you for the link. However, I'm a bit wary of the money datatype due to alleged inaccuracies as discussed here: http://groups.google.com/group/microsoft.public.sqlserver.programming/browse_thread/thread/a4101df9f779d166/af6861dc4d5e0705?#af6861dc4d5e0705 – Stephan Keller Apr 30 '09 at 12:46
  • I played around with your examples (thanks, btw), and the lesson I learned is "do not use literals" :) . I am still on the fence regarding the money vs decimal thing, though. – Stephan Keller Apr 30 '09 at 15:52
  • Personally, I'd go for money for performance and efficiency if you can offload your maths into the client. I use decimal(19,6) generally if I have any processing to do: I want to avoid precison/scale issues so I keep it all the same. – gbn Apr 30 '09 at 18:28
  • Guess I will go with decimal(19,6) then. Since it seems to be the same performancewise I will at least be covered if the customer needs to raise the scale or precision. – Stephan Keller May 03 '09 at 15:12
0

Performance-wise, there is no reason for not using decimal(19,4). I personally don't think that using a different precision just to "convey meaning" makes sense, not only because proper documentation (even a comment on the field) would take care of this, but especially because business rules do change.

If using MONEY is the preferred choice instead, be aware of potential accuracy pitfalls: https://groups.google.com/forum/?hl=en#!topic/microsoft.public.sqlserver.server/4ZvwaOII3Vs

FFiloseta
  • 33
  • 7