How do I do factorials in SQL Server?

In PostgreSQL, I often times want to do something like find the factorial of 7. I can do that very simply with

SELECT 7!;

-- PostgreSQL is so full featured
-- it even supports a prefix-factorial
SELECT !!7;

Even Excel has FACT,

=FACT(7)

How do I do that with SQL Server 2017 Enterprise?

I am not aware of a built-in function to do this. You need to roll your own. Here is how I do it:

SELECT SQL#.Math_Factorial(5); -- 120

The Math_Factorial function is in the Free version of the SQL# SQLCLR library (that I wrote).

OR

if you do not need it in function / UDF form, then it might be more efficient to do the following:

DECLARE @BaseNumber INT = 5,
        @Result BIGINT = 1;

;WITH cte AS
(
  SELECT TOP (@BaseNumber) ROW_NUMBER() OVER (ORDER BY (SELECT 0)) AS [Num]
  FROM   sys.columns
  ORDER  BY Num
)
SELECT  @Result *= [Num]
FROM    cte;

SELECT @Result;
-- 120

Both approaches shown above take into account the "special" condition of passing in 0 as the "BaseNumber" and it returning 1 instead of 0.

SELECT SQL#.Math_Factorial(0); -- 1

For the T-SQL approach, just make @BaseNumber = 0 and it will return 1 (no need to copy and paste it again here just for that).

StackExchange Database Administrators Q#200503, answer score: 16