|
|
SQL Server T-SQL User-Defined Function of the Week
How Many Digits of Pi can SQL Server Represent?
Volume 2 Number 6 January 27, 2004
Check out the UDF Frequently Asked Questions at:
http://www.novicksoftware.com/UdfOfWeek/UDF_FAQ.htm
To ask a UDF related question or to contribute a UDF,
send e-mail to udf@NovickSoftware.com
Sign up for this newsletter at:
http://www.novicksoftware.com/UdfOfWeek/UDFofWeekSignup.htm
Transact-SQL User-Defined Functions has been published! Take a look at it
right now!
This week we get back to the number Pi. Pi is the
ratio between the diameter of a circle and its circumference. The
number has been fascinating mathematicians and schools children
since its discovery long long ago.
Considerable investigation has been und retaken into Pi and you'll
find more information at this link:
http://www.cacr.caltech.edu/~roy/upi/pi.html
Including a page
with the first 50,000 digits of Pi.
There are mathematical reasons for calculating Pi
with so much precision but they're not the type of reasons that
usually come into play in the typical SQL Server based application.
However, working with Pi gives us a chance to explore the numeric
precision that is available in SQL Server.
There are two data types that can store numbers with precision to
the right of the decimal place: floating point and numeric. The two
floating point data types real and float follow the IEEE 754
specification for representing approximate numeric data types. They
have a mantissa, which allows real to represent numbers from
-3.40E+38 through 3.40E+38 and float to represent numbers from
-1.79E+308 to 1.79E+308. That's 1.79 times ten raised to the 308th
power. Pretty big in my book.
But no matter how large or small the size of the number, real and
float can only represent 7 and 15 digits of precision respectively.
Most of the time that works we'll. After all, few things are
measured with 15 digits of precision. Using a value of Pi of
3.141592653589793 works well for computing the diameter of a circle
with fabulous precision.
When you don't want to use approximate numeric data types, SQL
Server has an exact numeric data type called numeric. It's also
called decimal and the two names can be used interchangeably. For
those of you who've programmed on mainframes, numeric is like Packed
Decimal.
When you use a numeric data type, you can specify the number of
digits in the data and the number of digits that should be to the
right of the decimal. A typical numeric variable might be declared
as:
DECLARE @myNum numeric (9, 2)
That would give you nine digits of precision with two of those to
the right of the decimal.
By using a numeric (38, 37) it's possible to store 37 digits to the
right of the decimal and that's how this week's UDF does it. Here's
the CREATE FUNCTION script:
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE FUNCTION udf_Num_Pi(
) RETURNS numeric (38,37) --Returns Pi 3.14159.....
/*
* Returns Pi. Pi is the ratio between the circumference and
* diameter of a circle.
*
* Example:
Select Diameter, Diameter * dbo.Udf_Num_Pi() As [Circumference]
From (Select convert(numeric(38, 37), 1) As Diameter
Union Select 2 Union Select 3) Circle
*
* © Copyright 2004 Andrew Novick http://www.NovickSoftware.com
* You may use this function in any of your SQL Server databases
* including databases that you sell, so long as they contain
* other unrelated database objects. You may not publish this
* UDF either in print or electronically.
* Published as T-SQL UDF of Week Newsletter Vol 2 #6 1/27/04
http://www.NovickSoftware.com/UDFofWeek/UDFofWeek.htm
****************************************************************/
AS BEGIN
-- 1 2 3 4
-- 1234567890123456789012345678901234567890
RETURN 3.1415926535897932384626433832795028841
END
GO
GRANT EXEC, REFERENCES on dbo.udf_Num_PI to [Public]
GO
As we all learned in elementary school, Pi is used to calculate the
circumference of a circle. Here's an example:
select dbo.udf_Num_Pi()[xxx1234567890123456789012345678901234567]
go
(Results)
xxx1234567890123456789012345678901234567
----------------------------------------
3.1415926535897932384626433832795028841
That's great because SQL Server is able to preserve all 38 digits of
precision. You have to be careful though as the next two
queries show. The first query multiples udf_Num_Pi by a numeric
(38,37). There's a loss of two digits of precision.
select CONVERT(int, diameter) Diameter
, Diameter * dbo.Udf_Num_Pi() As [Circumference]
From (Select convert(numeric(38, 37), 1) As Diameter
Union Select 2
Union Select 3
) Circle
GO
(Results)
Diameter Circumference
----------- ----------------------------------------
1 3.14159265358979323846264338327950288
2 6.28318530717958647692528676655900577
3 9.42477796076937971538793014983850865
It's only because the diameter was cast as a numeric (38,37) that 35
digits of precision were retained. Notice the technique for getting
three test cases into the one SELECT statement. Union can be used in
an inline SELECT.
The next query leaves the diameter as an int, which causes much more
loss of precision:
Select CONVERT(int, diameter) Diameter
, Diameter * dbo.Udf_Num_Pi() As [Circumference]
From (Select 1 As Diameter
Union Select 2
Union Select 3
) Circle
GO
(Results)
Diameter Circumference
----------- ----------------------------------------
1 3.14159265358979323846264338
2 6.28318530717958647692528677
3 9.42477796076937971538793015
In the course of performing the multiplication, SQL Server converted
to various intermediate data types and the result has only 26 digits
of precision.
Keeping around so many digits of precision hardly makes sense in any
situation in which I work. However, you might come across a
situation where preserving the precision that you do have is
important.
I've written about this extensively in chapter 12 of
Transact-SQL
User-Defined Functions where it's used in the context of converting
between unit systems.
Transact-SQL User-Defined Functions is available on my web
site and on Amazon and in many stores.
Do you have a great UDF that you'd like to share? Or maybe you
have a T-SQL problem that you think could be solved by a UDF
but you don't know how? Send them to:
UDF@NovickSoftware.com
and they might be published in this newsletter. I try and
respond to every request that I get.
Thanks,
Andrew Novick
|
|
