SQL Server Performance

Factorial

Discussion in 'Contribute Your SQL Server Scripts' started by Madhivanan, Jan 19, 2005.

  1. Madhivanan Moderator

    Hi,

    This SP is used to find Factorial of a number


    CREATE Procedure Factorial
    (@Number numeric)
    as
    Declare @No numeric
    Declare @i numeric
    Declare @Product float

    set @No=@Number
    set @i=1
    set @Product=1
    C:
    set @Product=@Product * @i
    set @i=@i+1
    if @i<=@No goto C
    print @Product


    Factorial 5 will give the result 120

  2. FrankKalis Moderator

    Here's the recursive UDF approach


    CREATE FUNCTION dbo.fakultät(@n DECIMAL(38,0))
    RETURNS DECIMAL(38,0)
    AS
    BEGIN
    DECLARE @tmp DECIMAL(38,0)
    IF (@n <= 1)
    SELECT @tmp = 1
    ELSE
    SELECT @tmp = @n * dbo.fakultät(@n - 1)
    RETURN @tmp
    END
    GO
    SELECT dbo.fakultät(5)
    DROP FUNCTION dbo.fakultät
    GO


    --
    Frank
    http://www.insidesql.de
  3. Luis Martin Moderator

    Just a suggestion: check for negative numbers.


    Luis Martin
    Moderator
    SQL-Server-Performance.com

    One of the symptoms of an approaching nervous breakdown is the belief that one's work is terribly important
    Bertrand Russell


    All postings are provided “AS IS” with no warranties for accuracy.



  4. Madhivanan Moderator


    For negative numbers the Factorial is always 1

    Madhivanan
  5. FrankKalis Moderator

    No, that is not correct. Factorial is only defined for positive Integers, with the exception of 0, which is 1 by definition. I return 1 in my UDF, since this doesn't hurt in any multiplication or division, but that is just my convention.

    --
    Frank
    http://www.insidesql.de
  6. Luis Martin Moderator

    Yeap, if negative then error.


    Luis Martin
    Moderator
    SQL-Server-Performance.com

    One of the symptoms of an approaching nervous breakdown is the belief that one's work is terribly important
    Bertrand Russell


    All postings are provided “AS IS” with no warranties for accuracy.



  7. FrankKalis Moderator

    Normally I would agree here, but this is just for convenience (or call it laziness [<img src='/community/emoticons/emotion-5.gif' alt=';)' />] )<br /><br />--<br />Frank<br /<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a><br />
  8. Luis Martin Moderator

    Some people call me: The crazy tester[<img src='/community/emoticons/emotion-5.gif' alt=';)' />].<br /><br />Luis Martin<br />Moderator<br />SQL-Server-Performance.com<br /><br /><font size="1">One of the symptoms of an approaching nervous breakdown is the belief that one's work is terribly important<br />Bertrand Russell<br /></font id="size1"><br /><br /><font size="1">All postings are provided “AS IS” with no warranties for accuracy.</font id="size1"><br /><br /><br /><br />
  9. tomandlis New Member

    This won't work for numbers greater than 32. you will generate the error below

    Maximum stored procedure, function, trigger, or view nesting level exceeded (limit 32).
  10. Madhivanan Moderator

    Where did you use that code?
    Sounds different problem to me


    Madhivanan

    Failing to plan is Planning to fail
  11. dineshasanka Moderator

    I have suggestion. is it better to have int var for the input. Otherwise users will send decimal numbers as well.

    In Addition, negative numbers need to validate
  12. Madhivanan Moderator

    Well. Both the code will skip decimal part
    Negative numbers will result in 1


    Madhivanan

    Failing to plan is Planning to fail
  13. FrankKalis Moderator

    quote:Originally posted by tomandlis

    This won't work for numbers greater than 32. you will generate the error below

    Maximum stored procedure, function, trigger, or view nesting level exceeded (limit 32).
    You're right, for any number > 32 it will fail. But in my environment that's no problem. Anyway, you can easily calculate this with a number helper table like master..spt_values


    DECLARE @a FLOAT
    SET @a = 1
    SELECT @a = @a * Number
    FROM master..spt_values
    WHERE Number BETWEEN 1 AND 33
    AND Type='P'
    SELECT @a


    -----------------------------------------------------
    8.6833176188118859E+36

    (1 row(s) affected)


    --
    Frank Kalis
    Microsoft SQL Server MVP
    http://www.insidesql.de
    Ich unterstütze PASS Deutschland e.V. http://www.sqlpass.de)
  14. Madhivanan Moderator

    >>But in my environment that's no problem.

    What would be other environment?

    Madhivanan

    Failing to plan is Planning to fail
  15. FrankKalis Moderator

    Dunno, I guess any environment where you have the need to calcalate factorials &gt; 32. [<img src='/community/emoticons/emotion-1.gif' alt=':)' />]<br /><br />--<br />Frank Kalis<br />Microsoft SQL Server MVP<br /<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a><br />Ich unterstütze PASS Deutschland e.V. <a target="_blank" href=http://www.sqlpass.de>http://www.sqlpass.de</a>) <br />
  16. Madhivanan Moderator

    But I am able to find Factorial upto 170


    Factorial 170

    returns

    7.257415615307994E+306


    Madhivanan

    Failing to plan is Planning to fail
  17. FrankKalis Moderator

    Hm, do you also need this for your job? That's what I meant. [<img src='/community/emoticons/emotion-1.gif' alt=':)' />]<br /><br />--<br />Frank Kalis<br />Microsoft SQL Server MVP<br /<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a><br />Ich unterstütze PASS Deutschland e.V. <a target="_blank" href=http://www.sqlpass.de>http://www.sqlpass.de</a>) <br />
  18. Madhivanan Moderator

    Not really now [<img src='/community/emoticons/emotion-1.gif' alt=':)' />]<br /><br />Madhivanan<br /><br />Failing to plan is Planning to fail
  19. Jeff Moden New Member

    I realize that I'm responding to a post more than 6 years old. ;-)

    The largest factorial you can calculate using native datatypes in SQL Server is 170 for a total of 171 rows (if you include 0) which could be easily stored in a lookup table very much like a Tally Table. If you do find it inconvenient to do such lookups, we can take multiplication and recursion both out of the picture altogether. This one returns "1" for "0".

    Code:
    WITH
    cteTally AS
    (
     SELECT TOP 170
            N = ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
       FROM sys.all_columns
    )
     SELECT Factorial = ISNULL(POWER(CAST(10 AS FLOAT),SUM(LOG10(N))),1)
       FROM cteTally
    As a bit of a side bar for those using rCTEs (recursive CTEs) to count, you might want to take a look at the following article for why using rCTEs to count even small numbers is a very bad idea.

    http://www.sqlservercentral.com/articles/T-SQL/74118/
  20. FrankKalis Moderator

    Wow Jeff,
    this is sooo defensively written and way too much keystrokes... :)
    Since I guess, it's pretty fair to assume that sys.all_columns returns at least 1 row, why not shorten it to:
    Code:
    WITH cteTally AS
    (SELECT TOP 170
        ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) N
    FROM
        sys.all_columns
    )
    
    SELECT
        POWER(10e0, SUM(LOG10(N))) Factorial
    FROM
        cteTally;
        

Share This Page