CODEWITHSUNDEEP
t-sqlmathsql-server-2012
hwcverwe
hwcverwe

Reputation: 5367

TSQL decimal precision issues when dividing

I am having an issue with dividing numbers in TSQL. I have read the Precision, scale, and length (Transact-SQL) page but still I cannot figure out why the following issue occurs:

PRINT 1.4259276982890801000000/0.4259276982890801000000
-- Result 3.347816317222208

DECLARE @VAR1 DECIMAL(30,22) = 1.4259276982890801000000
DECLARE @VAR2 DECIMAL(30,22) = 0.4259276982890801000000
PRINT @VAR1/@VAR2
-- Result 3.34781631

question

Upvotes: 2

Views: 69

Answers (1)

Dan Guzman
Dan Guzman

Reputation: 46425

The actual precision and scale of the literals in the first PRINT statement are decimal(23,22) and decimal(22,22). Per the documentation, the result type is decimal(38,15). You can observe this with sp_describe_first_result_set:

EXEC sp_describe_first_result_set N'SELECT 1.4259276982890801000000;';
EXEC sp_describe_first_result_set N'SELECT 0.4259276982890801000000;';
EXEC sp_describe_first_result_set N'SELECT 1.4259276982890801000000/0.4259276982890801000000;';

The variables are both decimal(30,22) so the result is decimal(38,8):

EXEC sp_describe_first_result_set N'
DECLARE @VAR1 DECIMAL(30,22) = 1.4259276982890801000000;
DECLARE @VAR2 DECIMAL(30,22) = 0.4259276982890801000000;
SELECT @VAR1/@VAR2;';

You can fix it by specifying a different precision and scale of the variables: 

DECLARE @VAR1 DECIMAL(23,22) = 1.4259276982890801000000;
DECLARE @VAR2 DECIMAL(22,22) = 0.4259276982890801000000;
SELECT @VAR1/@VAR2;

Upvotes: 4

Related Questions