Novick Software
SQL Server Consulting Design Programming Tuning

  andy novick is a sql server mvp

 

 

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


RSS as HTML

Personal Blog

 
New Tips:

Use dsinit to set the SQL Server instance for Windows Azure dev storage

Upcoming
Presentations:

SQL PASS
Nov 7, '12
Biggest
Loser: DB
Edition


Full Schedule