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

Exploring the size of a bigint

Volume 1 Number 9   January 13, 2003

Sign up for this newsletter at:
http://www.novicksoftware.com/UdfOfWeek/UDFofWeekSignup.htm

This week's issue plays around with factorials to get a better 
feeling for the bigint data type.  The idea came from a
presentation that I did last week for the New England SQL Server
Users Group in Waltham, MA.  If you want to see the slides from 
the presentation or download the sample script you'll find it at
http://www.NovickSoftware.com/Presentations.htm

Just in case you haven't taken any math classes recently,
remember that N factorial is the product of all the integers up 
to and including N.  So 5 factorial is 1 * 2 * 3 * 4 * 5 or 120.

I had originally coded a UDF to return a table of factorials as
a way to illustrate the Multistatement Table Valued User-defined
Function.  Here's the original coding of that UDF:

/------------- Copy From Below this line ----------------------\
CREATE FUNCTION dbo.udf_FactorialsTAB (
        @N int
        )
    RETURNS @Factorials TABLE (Number INT, Factorial INT)
AS BEGIN
    DECLARE @I INT, @F INT
    SELECT @I = 1, @F = 1
    WHILE @I < = @N  BEGIN
        SET @F = @I * @F
        INSERT INTO @Factorials VALUES (@I, @F)
        SET @I = @I + 1
    END -- WHILE
    RETURN
END
GO
\------------ Stop copying above this line --------------------/

It works fine but shows the limitation of the int data type
pretty quickly.   First let's use it:

/------------- Copy From Below this line ----------------------\
SELECT * from udf_FactorialsTAB (12)
GO
\------------ Stop copying above this line --------------------/
(Results)
Number      Factorial   
----------- ----------- 
          1           1 
          2           2 
          3           6 
          4          24 
          5         120 
          6         720 
          7        5040 
          8       40320 
          9      362880 
         10     3628800 
         11    39916800 
         12   479001600 

It works up to 12 but 13 factorial goes beyond the capacity
of the int data type.  Let's try it:
/------------- Copy From Below this line ----------------------\
SELECT * from udf_FactorialsTAB (13)
GO
\------------ Stop copying above this line --------------------/
(Results)
Server: Msg 8115, Level 16, State 2, Procedure udf_FactorialsTAB,
 Line 9
Arithmetic overflow error converting expression to data type int.

So an INT is only able to calculate 12 factorial.

Now let's rewrite the function using the bigint data type for 
holding the factorial.  I've also changed the function name so 
that it fits into the _Num_ group of functions where I might
find it the next time I go looking for it.  Here's the 
CREATE FUNCTION script:
/------------- Copy From Below this line ----------------------\
CREATE FUNCTION dbo.udf_Num_FactorialTAB (
        @N bigint
        )
    RETURNS @Factorials TABLE (Number int, Factorial bigint)
/* Returns a table with the series of numbers and factorials */
AS BEGIN
    DECLARE @I int, @F bigint
    SELECT @I = 1, @F = CONVERT(bigint, 1)
    WHILE @I < = @N  BEGIN
        SET @F = CONVERT(bigint, @I) * @F
        INSERT INTO @Factorials (Number, Factorial) VALUES (@I, @F)
        SET @I = @I + 1
    END -- WHILE
    RETURN
END

GRANT EXEC on dbo.udf_Num_FactorialTAB TO PUBLIC
GO
\------------ Stop copying above this line --------------------/

I've tried it out so I know it works up to 20 factorial:
/------------- Copy From Below this line ----------------------\
SELECT * from udf_Num_FactorialTAB (20)
GO
\------------ Stop copying above this line --------------------/
(Results)
Number      Factorial            
----------- -------------------- 
          1                    1 
          2                    2 
          3                    6 
          4                   24 
          5                  120 
          6                  720 
          7                 5040 
          8                40320 
          9               362880 
         10              3628800 
         11             39916800 
         12            479001600 
         13           6227020800 
         14          87178291200 
         15        1307674368000 
         16       20922789888000 
         17      355687428096000 
         18     6402373705728000 
         19   121645100408832000 
         20  2432902008176640000
         
So we're able to go up to 20 factorial.  A pretty big number.
Of course, the numeric(38,0) or float data types will both 
hold larger numbers when needed.

In udf_NUM_FactorialTAB I used a loop to calculate the
factorials.  Factorial is a formula that also lends it self 
recursion.  I'll leave that for another issue.

+--------------------------------------------------------------+
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
+--------------------------------------------------------------+