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

Take the Nth Root of a Number

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 if pretty simple. It takes the Nth root of a number. Of course, all it takes is a simple equation, so the UDF serves as a convenience so you don't have to remember the formula.

Here's the CREATE FUNCTION script:

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO


CREATE FUNCTION dbo.udf_Num_NthRoot (
@Number float
, @N float
) 
RETURNS float -- @N th root of @Number
/* 
* Return's the N'th Root of a number
*
* Equivalent Template: POWER (<number>, 1 / <root>)
*
* Example:
SELECT dbo.udf_NthRoot (1000, 3) as [Cube root of 1000]
*
* c Copyright 2002 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 #4 12/9/03 
http://www.NovickSoftware.com/UDFofWeek/UDFofWeek.htm
****************************************************************/
AS BEGIN
RETURN POWER(@Number , 1 / @N)
END

GO


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

Let's take a look at a few results from the function:

select dbo.udf_Num_NthRoot (1024, 10) [1024's 10th root]
, dbo.udf_Num_NthRoot (27, 3) [3rd root of 27]
, dbo.udf_Num_NthRoot (144, 2) [Square root of 144]
GO

1024's 10th root 3rd root of 27 square root of 144 
---------------- -------------- ------------------- 
             2.0            3.0                12.0 

Don't forget about the potential performance penalty when using UDFs. If you're retrieving more than around a thousand rows, you should consider substituting the expression for use of the UDF for performance reasons.

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