HiveBrain v1.2.0
Get Started
← Back to all entries
patternsqlModerate

How does SQL Server determine precision / scale?

Submitted by: @import:stackexchange-dba··
0
Viewed 0 times
howsqlprecisiondeterminedoesserverscale

Problem

I'm running SQL Server 2012

SELECT 
   0.15 * 30 / 360,
   0.15 / 360 * 30


Results:

0.012500, 
 0.012480


This one is even mor confusing to me:

DECLARE @N INT = 360
DECLARE @I DECIMAL(38,26) = 0.15 * 30 / 360     
DECLARE @C DECIMAL(38,26) = 1000000     

SELECT @C *  @I *  POWER(1 + @I, @N)  / ( POWER(1 + @I, @N) - 1 )
SELECT @C * (@I *  POWER(1 + @I, @N)  / ( POWER(1 + @I, @N) - 1 ) )


The first select gives me the correct result: 12644.44022
The second one truncates the result: 12644.00000

Solution

Determining precision and scale resulting from expressions is a rat's nest and I don't think anyone understands the exact rules in every scenario, especially when mixing decimal (or float!) and int. See this answer by gbn.

You can of course tailor the expressions to give you what you want by making much more verbose explicit conversions. This is probably overkill but:

SELECT 
   CONVERT(DECIMAL(15,6), CONVERT(DECIMAL(15,6), 0.15) 
   * CONVERT(DECIMAL(15,6), 30) 
   / CONVERT(DECIMAL(15,6), 360)),
   CONVERT(DECIMAL(15,6), CONVERT(DECIMAL(15,6), 0.15) 
   / CONVERT(DECIMAL(15,6), 360) 
   * CONVERT(DECIMAL(15,6), 30));


Neither result is rounded wrongly due to broken floating point math or wildly wrong precision/scale.

0.012500    0.012500

Code Snippets

SELECT 
   CONVERT(DECIMAL(15,6), CONVERT(DECIMAL(15,6), 0.15) 
   * CONVERT(DECIMAL(15,6), 30) 
   / CONVERT(DECIMAL(15,6), 360)),
   CONVERT(DECIMAL(15,6), CONVERT(DECIMAL(15,6), 0.15) 
   / CONVERT(DECIMAL(15,6), 360) 
   * CONVERT(DECIMAL(15,6), 30));
0.012500    0.012500

Context

StackExchange Database Administrators Q#77664, answer score: 15

Revisions (0)

No revisions yet.