Business Application Development with: SQL Server, C#, VB, VB.Net, ASP, ASP.Net, and XML 

News Links Schedule Site Map Contact 

SQL Server TSQL UserDefined Function of the WeekHow Many Digits of Pi can SQL Server Represent?Volume 2 Number 6 January 27, 2004Check out the UDF Frequently Asked Questions at:
http://www.novicksoftware.com/UdfOfWeek/UDF_FAQ.htm
TransactSQL UserDefined 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. 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 TSQL 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.1415926535897932384626433832795028841That'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. Do you have a great UDF that you'd like to share? Or maybe you
have a TSQL problem that you think could be solved by a UDF
but you don't know how? Send them to:
UDF@NovickSoftware.com


Copyright © 20032008 Novick Software, Inc.  Terms of Use  Privacy Policy  Nice Things People Say 