SQL Server Performance

Factorial

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

Page 2 of 2

  1. Jeff Moden New Member

    Heh... I wasn't sure that a whole lot of people would know what a zero exponent would be or do so stuck with the long hand. And, aApologies on my part for not stating that I was demonstrating the guts of a function. The ISNULL() was important in calculating "0!".

    I also didn't want to explain how POWER and EXP are similar nor the differences between base 10 logs and natural logs but you can shorten the code to bare bones with the understanding that it will no longer solve for "0!".
    Code:
    WITH cteTally AS
(SELECT TOP (170) --This could be a parameter in a function
        ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) N
  FROM sys.all_columns
)
SELECT EXP(SUM(LOG(N))) Factorial
  FROM cteTally;
    Jeff Moden, Mar 13, 2012
    #21

  2. Jeff Moden New Member

    Of course, if you want to go for broke with simplicity of code, break out your trusty Tally Table.
    Code:
     SELECT EXP(SUM(LOG(N))) Factorial
   FROM dbo.Tally
  WHERE N BETWEEN 1 AND 170;
    Being less "defensive", I'll come right out and say that using a counting rCTE for such a thing is actually one of the worst things you could do. Follow the link at the bottom of my first post on this thread for why.
    Jeff Moden, Mar 13, 2012
    #22

  3. FrankKalis Moderator

    Actually I wouldn never have thought of using a CTE to solve such a problem in the first place. Maybe not for the reasons you mentioned in your article, but first and foremost because it's an unnecessarily complex solution to the problem. Your last reply is pretty close to what I mentioned earlier in this thread, but frankly using this EXP(SUM(LOG())) expression thingy is much more impressive and looks way cooler. Think I learned about that from usenet posts by Steve Kass, but honestly, keep forgetting about it because I've never used it so far in my production code.:)

    ...actually, while writing this I realised that I could have modified one of my answers earlier to:

    Code:
    DECLARE @a float;
SELECT @a = 0;

SELECT
    @a = NULLIF(@a, 0) * N
FROM
    dbo.Tally
WHERE
    N BETWEEN 1 AND 170;

SELECT ISNULL(@a, 1);
    to deal with this special 0 treatment.
    Fwiw... :)
    FrankKalis, Mar 14, 2012
    #23
Page 2 of 2

Share This Page