patternsqlModerate
How does SQL Server determine precision / scale?
Viewed 0 times
howsqlprecisiondeterminedoesserverscale
Problem
I'm running SQL Server 2012
Results:
This one is even mor confusing to me:
The first select gives me the correct result: 12644.44022
The second one truncates the result: 12644.00000
SELECT
0.15 * 30 / 360,
0.15 / 360 * 30Results:
0.012500,
0.012480This 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:
Neither result is rounded wrongly due to broken floating point math or wildly wrong precision/scale.
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.012500Code 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.012500Context
StackExchange Database Administrators Q#77664, answer score: 15
Revisions (0)
No revisions yet.