To be precise. . .
Oct
6
Written by:
Charles Flock
10/6/2008 10:36 AM
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.
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.