SQL Functions - factorial

I am a beginner in SQL Functions. What is the best way to create a function for fa开发者_如何学Cctorial in SQL Server- Say 10!

A non recursive way

;With Nums As
(
select ROW_NUMBER() OVER (ORDER BY (SELECT 0)) AS RN
FROM sys.objects
)
SELECT  POWER(10.0, SUM(LOG10(RN)))
FROM Nums
WHERE RN <= 10

And a recursive way

declare @target int
set @target=10;

WITH N AS
     (SELECT 1 AS i,
           1 AS f

     UNION ALL

     SELECT i+1,
            f*(i+1)
     FROM   N
     WHERE  i < @target
     )
SELECT f FROM N
WHERE i=@target

Here is a recursive solution:

CREATE FUNCTION dbo.Factorial ( @iNumber int )
RETURNS INT
AS
BEGIN
DECLARE @i  int

    IF @iNumber <= 1
        SET @i = 1
    ELSE
        SET @i = @iNumber * dbo.Factorial( @iNumber - 1 )
RETURN (@i)
END

-- Iterative method. -- Why Iterative? It is simpler and faster. -- For @N from 0 to 20 this gives an exact result. -- 21 will give an overflow.

DECLARE @N Bigint = 20
DECLARE @F Bigint = 1
WHILE @N > 0 BEGIN
  SET @F = @f*@n
  SET @N = @N-1
END
SELECT @F AS FACTORIAL

-- Change the datatype to float and you can get the factorial up to 170. -- 171 will result in an overflow. -- Remark the result will only be accurate over a limited number of positions.

DECLARE @N FLOAT = 170
DECLARE @F FLOAT = 1
WHILE @N > 0 BEGIN
  SET @F = @f*@n
  SET @N = @N-1
END
SELECT @F AS FACTORIAL

-- Ben

Try this

WITH MYCTE AS(
 SELECT VAL=1,NUM =6 
 UNION ALL
 SELECT VAL=VAL*NUM,NUM = (NUM -1)
 FROM MYCTE
 WHERE NUM > 1
)                  
SELECT VAL FROM MYCTE

Another way:

create function Fact(@num int)
returns bigint
as
begin
declare @i int = 1

 while @num>1
 begin
  set @i = @num *  @i
  set @num=@num-1
  end

return @i
end

select dbo.Fact(5)

... for my Set-based method:

DECLARE @n int=11, @f bigint=1;

WITH 
t(n,f) AS (SELECT TOP(@n) 
         ROW_NUMBER() OVER (ORDER BY (SELECT NULL))+1,
         ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) *
        (ROW_NUMBER() OVER (ORDER BY (SELECT NULL))+1)
     FROM sys.all_columns
     UNION SELECT 1, f=CASE WHEN @n=0 THEN 0 ELSE 1 END)
SELECT @f=@f*f
FROM t
WHERE n%2=@n%2 OR f=0;


SELECT @f AS FACTORIAL;

I know I'm a little late here, but it's worth noting that the recursive way that Martin posted does not work for 0.

This will (forgive me, I was having issues posting the code):

declare @target int=3;

WITH N AS
(SELECT 1 AS i, 
        1 AS f
 UNION ALL
 SELECT i+1,
        f*(i+1)
 FROM N
 WHERE  i < @target),
N0 AS
(SELECT f FROM N WHERE i=@target UNION SELECT 0)
SELECT MAX(f) FROM N0

And for the way, way faster version:

declare @target int=5;

WITH N AS
(SELECT 1 AS i, 
        1 AS f
 UNION ALL
 SELECT i+1,
        f*(i+1)
 FROM N
 WHERE i < @target),
N0 AS
(SELECT f FROM N WHERE i=@target UNION SELECT f=CASE WHEN @target=0 THEN 0 END)
SELECT f FROM N0
WHERE f>=0

This is much faster because I lose the MAX() function which, just like top 1, causes a DISTINCT sort.

Here is an other method to calculate factorial value of an integer in SQL Server

 create function sqlFactorial (@int int)
 returns int
 begin
  declare @factorial bigint = 1
  select @factorial = @factorial * i from dbo.NumbersTable(1,@int,1)
  return @factorial
 end

You need to use a SQL numbers table for this solution. The Select statement updates the declared integer variable for each row in the FROM part with multiplying it with the ordered integer values

If you are okay with an approximation, use Stirling's Approximation.

create table #temp (value int)

insert into #temp values (5),(6),(7),(8)

select 
    value,
    sqrt(2*3.14*value)*power((value/2.718),value) --stirling's approx.
from #temp

Note that you will have to make a case for 0!, if needed.

You asked which is the best way to create a function for factorial in SQL Server. As always, that depends on the context. But if you truly mean it in the generic sense, where performance matters, the best way to go is without a doubt to implement it as a CLR user-defined function.

https://learn.microsoft.com/en-us/sql/relational-databases/clr-integration-database-objects-user-defined-functions/clr-user-defined-functions?view=sql-server-2017

You can of course implement the function itself in whatever language you fancy. And a long/bigint doesn't really cut it for a factorial function (a bigint can only fit up to 20!, 21! is arithmetic overflow).

/* Print Factorial Sequence*/

WITH MYCTE AS(
 SELECT VAL=1,NUM =1 
 UNION ALL
 SELECT VAL=VAL*(NUM+1),NUM = (NUM +1)
 FROM MYCTE
 WHERE NUM < 11
)                  
SELECT VAL FROM MYCTE
 OPTION (MAXRECURSION 0);

MS-SQL: Factorial from 0 to 88:

with tb1 as (
select 10000000 as b, 1 as n, 
0 as t18, 0 as t17, 0 as t16, 0 as t15, 0 as t14, 0 as t13, 0 as t12, 0 as t11, 0 as t10, 0 as t9, 0 as t8, 0 as t7, 0 as t6, 0 as t5, 0 as t4, 0 as t3, 0 as t2, 0 as t1, 1 as t0
union all 
select 
tb1.b,
tb1.n + 1,
(tb1.t18 * tb1.n) + ((tb1.t17 * tb1.n) + ((tb1.t16 * tb1.n) + ((tb1.t15 * tb1.n) + ((tb1.t14 * tb1.n) + ((tb1.t13 * tb1.n) + ((tb1.t12 * tb1.n) + ((tb1.t11 * tb1.n) + ((tb1.t10 * tb1.n) + ((tb1.t9 * tb1.n) + ((tb1.t8 * tb1.n) + ((tb1.t7 * tb1.n) + ((tb1.t6 * tb1.n) + ((tb1.t5 * tb1.n) + ((tb1.t4 * tb1.n) + ((tb1.t3 * tb1.n) + ((tb1.t2 * tb1.n) + ((tb1.t1 * tb1.n) + (tb1.t0 * tb1.n) / tb1.b)/tb1.b)/tb1.b)/tb1.b)/tb1.b)/tb1.b)/tb1.b)/tb1.b)/tb1.b)/tb1.b)/tb1.b)/tb1.b)/tb1.b)/tb1.b)/tb1.b)/tb1.b)/tb1.b)/tb1.b,
((tb1.t17 * tb1.n) + ((tb1.t16 * tb1.n) + ((tb1.t15 * tb1.n) + ((tb1.t14 * tb1.n) + ((tb1.t13 * tb1.n) + ((tb1.t12 * tb1.n) + ((tb1.t11 * tb1.n) + ((tb1.t10 * tb1.n) + ((tb1.t9 * tb1.n) + ((tb1.t8 * tb1.n) + ((tb1.t7 * tb1.n) + ((tb1.t6 * tb1.n) + ((tb1.t5 * tb1.n) + ((tb1.t4 * tb1.n) + ((tb1.t3 * tb1.n) + ((tb1.t2 * tb1.n) + ((tb1.t1 * tb1.n) + (tb1.t0 * tb1.n) / tb1.b)/tb1.b)/tb1.b)/tb1.b)/tb1.b)/tb1.b)/tb1.b)/tb1.b)/tb1.b)/tb1.b)/tb1.b)/tb1.b)/tb1.b)/tb1.b)/tb1.b)/tb1.b)/tb1.b)%tb1.b,
((tb1.t16 * tb1.n) + ((tb1.t15 * tb1.n) + ((tb1.t14 * tb1.n) + ((tb1.t13 * tb1.n) + ((tb1.t12 * tb1.n) + ((tb1.t11 * tb1.n) + ((tb1.t10 * tb1.n) + ((tb1.t9 * tb1.n) + ((tb1.t8 * tb1.n) + ((tb1.t7 * tb1.n) + ((tb1.t6 * tb1.n) + ((tb1.t5 * tb1.n) + ((tb1.t4 * tb1.n) + ((tb1.t3 * tb1.n) + ((tb1.t2 * tb1.n) + ((tb1.t1 * tb1.n) + (tb1.t0 * tb1.n) / tb1.b)/tb1.b)/tb1.b)/tb1.b)/tb1.b)/tb1.b)/tb1.b)/tb1.b)/tb1.b)/tb1.b)/tb1.b)/tb1.b)/tb1.b)/tb1.b)/tb1.b)/tb1.b)%tb1.b,
((tb1.t15 * tb1.n) + ((tb1.t14 * tb1.n) + ((tb1.t13 * tb1.n) + ((tb1.t12 * tb1.n) + ((tb1.t11 * tb1.n) + ((tb1.t10 * tb1.n) + ((tb1.t9 * tb1.n) + ((tb1.t8 * tb1.n) + ((tb1.t7 * tb1.n) + ((tb1.t6 * tb1.n) + ((tb1.t5 * tb1.n) + ((tb1.t4 * tb1.n) + ((tb1.t3 * tb1.n) + ((tb1.t2 * tb1.n) + ((tb1.t1 * tb1.n) + (tb1.t0 * tb1.n) / tb1.b)/tb1.b)/tb1.b)/tb1.b)/tb1.b)/tb1.b)/tb1.b)/tb1.b)/tb1.b)/tb1.b)/tb1.b)/tb1.b)/tb1.b)/tb1.b)/tb1.b)%tb1.b,
((tb1.t14 * tb1.n) + ((tb1.t13 * tb1.n) + ((tb1.t12 * tb1.n) + ((tb1.t11 * tb1.n) + ((tb1.t10 * tb1.n) + ((tb1.t9 * tb1.n) + ((tb1.t8 * tb1.n) + ((tb1.t7 * tb1.n) + ((tb1.t6 * tb1.n) + ((tb1.t5 * tb1.n) + ((tb1.t4 * tb1.n) + ((tb1.t3 * tb1.n) + ((tb1.t2 * tb1.n) + ((tb1.t1 * tb1.n) + (tb1.t0 * tb1.n) / tb1.b)/tb1.b)/tb1.b)/tb1.b)/tb1.b)/tb1.b)/tb1.b)/tb1.b)/tb1.b)/tb1.b)/tb1.b)/tb1.b)/tb1.b)/tb1.b)%tb1.b,
((tb1.t13 * tb1.n) + ((tb1.t12 * tb1.n) + ((tb1.t11 * tb1.n) + ((tb1.t10 * tb1.n) + ((tb1.t9 * tb1.n) + ((tb1.t8 * tb1.n) + ((tb1.t7 * tb1.n) + ((tb1.t6 * tb1.n) + ((tb1.t5 * tb1.n) + ((tb1.t4 * tb1.n) + ((tb1.t3 * tb1.n) + ((tb1.t2 * tb1.n) + ((tb1.t1 * tb1.n) + (tb1.t0 * tb1.n) / tb1.b)/tb1.b)/tb1.b)/tb1.b)/tb1.b)/tb1.b)/tb1.b)/tb1.b)/tb1.b)/tb1.b)/tb1.b)/tb1.b)/tb1.b)%tb1.b,
((tb1.t12 * tb1.n) + ((tb1.t11 * tb1.n) + ((tb1.t10 * tb1.n) + ((tb1.t9 * tb1.n) + ((tb1.t8 * tb1.n) + ((tb1.t7 * tb1.n) + ((tb1.t6 * tb1.n) + ((tb1.t5 * tb1.n) + ((tb1.t4 * tb1.n) + ((tb1.t3 * tb1.n) + ((tb1.t2 * tb1.n) + ((tb1.t1 * tb1.n) + (tb1.t0 * tb1.n) / tb1.b)/tb1.b)/tb1.b)/tb1.b)/tb1.b)/tb1.b)/tb1.b)/tb1.b)/tb1.b)/tb1.b)/tb1.b)/tb1.b)%tb1.b,
((tb1.t11 * tb1.n) + ((tb1.t10 * tb1.n) + ((tb1.t9 * tb1.n) + ((tb1.t8 * tb1.n) + ((tb1.t7 * tb1.n) + ((tb1.t6 * tb1.n) + ((tb1.t5 * tb1.n) + ((tb1.t4 * tb1.n) + ((tb1.t3 * tb1.n) + ((tb1.t2 * tb1.n) + ((tb1.t1 * tb1.n) + (tb1.t0 * tb1.n) / tb1.b)/tb1.b)/tb1.b)/tb1.b)/tb1.b)/tb1.b)/tb1.b)/tb1.b)/tb1.b)/tb1.b)/tb1.b)%tb1.b,
((tb1.t10 * tb1.n) + ((tb1.t9 * tb1.n) + ((tb1.t8 * tb1.n) + ((tb1.t7 * tb1.n) + ((tb1.t6 * tb1.n) + ((tb1.t5 * tb1.n) + ((tb1.t4 * tb1.n) + ((tb1.t3 * tb1.n) + ((tb1.t2 * tb1.n) + ((tb1.t1 * tb1.n) + (tb1.t0 * tb1.n) / tb1.b)/tb1.b)/tb1.b)/tb1.b)/tb1.b)/tb1.b)/tb1.b)/tb1.b)/tb1.b)/tb1.b)%tb1.b,
((tb1.t9 * tb1.n) + ((tb1.t8 * tb1.n) + ((tb1.t7 * tb1.n) + ((tb1.t6 * tb1.n) + ((tb1.t5 * tb1.n) + ((tb1.t4 * tb1.n) + ((tb1.t3 * tb1.n) + ((tb1.t2 * tb1.n) + ((tb1.t1 * tb1.n) + (tb1.t0 * tb1.n) / tb1.b)/tb1.b)/tb1.b)/tb1.b)/tb1.b)/tb1.b)/tb1.b)/tb1.b)/tb1.b)%tb1.b,
((tb1.t8 * tb1.n) + ((tb1.t7 * tb1.n) + ((tb1.t6 * tb1.n) + ((tb1.t5 * tb1.n) + ((tb1.t4 * tb1.n) + ((tb1.t3 * tb1.n) + ((tb1.t2 * tb1.n) + ((tb1.t1 * tb1.n) + (tb1.t0 * tb1.n) / tb1.b)/tb1.b)/tb1.b)/tb1.b)/tb1.b)/tb1.b)/tb1.b)/tb1.b)%tb1.b,
((tb1.t7 * tb1.n) + ((tb1.t6 * tb1.n) + ((tb1.t5 * tb1.n) + ((tb1.t4 * tb1.n) + ((tb1.t3 * tb1.n) + ((tb1.t2 * tb1.n) + ((tb1.t1 * tb1.n) + (tb1.t0 * tb1.n) / tb1.b)/tb1.b)/tb1.b)/tb1.b)/tb1.b)/tb1.b)/tb1.b)%tb1.b,
((tb1.t6 * tb1.n) + ((tb1.t5 * tb1.n) + ((tb1.t4 * tb1.n) + ((tb1.t3 * tb1.n) + ((tb1.t2 * tb1.n) + ((tb1.t1 * tb1.n) + (tb1.t0 * tb1.n) / tb1.b)/tb1.b)/tb1.b)/tb1.b)/tb1.b)/tb1.b)%tb1.b, 
((tb1.t5 * tb1.n) + ((tb1.t4 * tb1.n) + ((tb1.t3 * tb1.n) + ((tb1.t2 * tb1.n) + ((tb1.t1 * tb1.n) + (tb1.t0 * tb1.n) / tb1.b)/tb1.b)/tb1.b)/tb1.b)/tb1.b)%tb1.b,
((tb1.t4 * tb1.n) + ((tb1.t3 * tb1.n) + ((tb1.t2 * tb1.n) + ((tb1.t1 * tb1.n) + (tb1.t0 * tb1.n) / tb1.b)/tb1.b)/tb1.b)/tb1.b)%tb1.b,
((tb1.t3 * tb1.n) + ((tb1.t2 * tb1.n) + ((tb1.t1 * tb1.n) + (tb1.t0 * tb1.n) / tb1.b)/tb1.b)/tb1.b)%tb1.b,
((tb1.t2 * tb1.n) + ((tb1.t1 * tb1.n) + (tb1.t0 * tb1.n) / tb1.b)/tb1.b)%tb1.b,
((tb1.t1 * tb1.n) + (tb1.t0 * tb1.n) / tb1.b)%tb1.b,
(tb1.t0 * tb1.n) % tb1.b
from tb1 where tb1.n < 89
)
, t2 as (
select  tb1.n - 1 as N,
        FORMAT(tb1.t18, '0000000') + 
        FORMAT(tb1.t17, '0000000') + FORMAT(tb1.t16, '0000000') + FORMAT(tb1.t15, '0000000') + 
        FORMAT(tb1.t14, '0000000') + FORMAT(tb1.t13, '0000000') + FORMAT(tb1.t12, '0000000') +
        FORMAT(tb1.t11, '0000000') + FORMAT(tb1.t10, '0000000') + FORMAT(tb1.t9, '0000000') +
        FORMAT(tb1.t8, '0000000') + FORMAT(tb1.t7, '0000000') + FORMAT(tb1.t6, '0000000') +
        FORMAT(tb1.t5, '0000000') + FORMAT(tb1.t4, '0000000') + FORMAT(tb1.t3, '0000000') +
        FORMAT(tb1.t2, '0000000') + FORMAT(tb1.t1, '0000000') + FORMAT(tb1.t0, '0000000') as FACT
from tb1
)

select  t2.N, SUBSTRING(t2.FACT, PATINDEX('%[^0]%', t2.FACT+'.'), LEN(t2.FACT))
from t2
order by t2.N