Login     Register

        Contact Us     Search

To be precise. . .

Oct 6

Written by: Charles Flock
10/6/2008 10:36 AM  RssIcon

A brief journey into the world of significance and precision in T-SQL.

As we were testing the FLOOR function, we discovered that the following T-SQL statement:
Select wct .floor(2/3,.1)
did not return the expected result. After a little investigation, we discovered that simply entering the following statement:
SELECT 2/3
returned 0.
So, we started looking at the T-SQL documentation and found this http://msdn.microsoft.com/en-us/library/ms190476.aspx which was quite useful. It led us to believe that if we entered 2.0/3.0 we would get the resut that we were looking for:
SELECT 2.0/3.0
which gives us 0.666666, which is somewhat disconcerting as we were expecting 0.666667.
 
By entering 2/3, it appears that the engine conveted both the numerator and denominator to int data types, returning an int data type, which under the scale and precision rules maintainend the integrity of the value on the left hand side of the decimal (zero in this case).
 
We then decided to do a more structured test, the results of which are in the table below:
 
SELECT STATEMENT
RESULT
SELECT cast(2 as smallint)/cast(3 as smallint)
0
SELECT cast(2 as tinyint)/cast(3 as tinyint)
0
SELECT cast(2 as int)/cast(3 as int)
0
SELECT cast(2 as bigint)/cast(3 as bigint)
0
SELECT cast(2 as decimal)/cast(3 as decimal)
0.6666666666666666666
SELECT cast(2 as float)/cast(3 as float)
0.666666666666667
SELECT cast(2 as numeric)/cast(3 as numeric)
0.6666666666666666666
SELECT cast(2 as money)/cast(3 as money)
0.6666
SELECT cast(2 as smallmoney)/cast(3 as smallmoney)
0.6666
SELECT cast(2 as real)/cast(3 as real)
0.6666667
 
It was only when our values were defined as float or as real that we got the results that we expected.

Tags:
Categories:
Copyright 2008-2013 WestClinTech LLC         Privacy Policy        Terms of Service