SQL Server T-SQL User-Defined Function of the Week

Calculate Phi the Golden Ratio

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's UDF is another numeric utility. udf_Num_Phi calculates and returns the value of the numeric constant Phi. Functions with common numeric constants are useful to have around when you can't remember all those digits.

Here's the CREATE FUNCTION script:

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO


CREATE FUNCTION udf_Num_PHI() 
RETURNS float --Returns Phi  1.6818033.....
/*
* Returns Phi as a double. Phi is the golden ratio.
*
* Example:
Select Shortside, dbo.Udf_Num_Phi() * Shortside As [Long Side]
From (Select 1 As Shortside Union Select 2 Union Select 3) Rect
*
* © Copyright 2003 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 #5bis 1/6/04 
http://www.NovickSoftware.com/UDFofWeek/UDFofWeek.htm
****************************************************************/
AS BEGIN

RETURN (1 + SQRT(5))/2 -- good approximation for a double
END

GO


GRANT EXEC, REFERENCES on dbo.udf_Num_PHI to [Public]
GO

As we all learned some where in school, the golden ratio is the ratio between the sides of an "ideal" rectangle. Golden rectangles are considered "ideal" because they're particularly pleasing to the eye. The Parthenon is a good example.

The following example shows the function

Select Shortside, dbo.Udf_Num_Phi() * Shortside As [Long Side]
From (Select 1 As Shortside Union Select 2 Union Select 3) Rect
GO

Shortside   Long Side                     
----------- ----------------------------- 
          1            1.6180339887498949 
          2            3.2360679774997898 
          3            4.8541019662496847 

Notice the technique for getting three test cases into the one SELECT statement. Union can be used in an inline SELECT.  It's handy for writing ad hoc queries.

If you like this newsletter, you might be interested in my book: Transact-SQL User-Defined Functions. It's 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