Calculating Fibonacci Sequence and Factorials With T-SQL

With this post, I wanted to figure out how to calculate the Fibonacci Sequence and Factorials using T-SQL.

Fibonacci Sequence:

The Fibonacci Sequence is a series of numbers that starts with 0 then 1. The rest of the values are calculated by adding the two values that precede it in the sequence.
So 0,1,1,2,3,5,8……

We can use a CTE to help calculate the sequence. Here, I added a WHERE clause to limit us to 10 values.
When calculating the next value, we’ll have the two previous values available (Fibonacci and PreviousValue), so we just have to add them together.

WITH cte_fib ([Level], PreviousValue, Fibonacci) AS (
	SELECT 1, 1, 0
	UNION ALL
	SELECT [Level] + 1, Fibonacci, PreviousValue + Fibonacci
	FROM cte_fib
	WHERE [Level] <= 9
)
SELECT STRING_AGG(Fibonacci, ', ') AS Fibonacci
FROM cte_fib;

Result:

Factorial:
Factorial is calculated by multiplying all of the numbers together from a sequence starting with a specified number, down to 1.
To calculate 4 factorial (or 4!), we would calculate 4 * 3 * 2 * 1, which gives us 24.

Again, we’ll use a CTE to calculate the values. To calculate the next value, we just have to multiply that number times the previous factorial value. For 4, we would take the factorial for 3 (Which is 6), and multiply by 4 to get the answer 4! = 24.
Again, the CTE has been limited to 20 levels.
We can use any number from 1 to 20 when selecting from the CTE to get the factorial value for that number.

WITH cte_fac (Num, Fac) AS (
	SELECT 1,CAST(1 AS BIGINT)
	UNION ALL
	SELECT Num + 1, (Num + 1) * Fac
	FROM cte_fac
	WHERE Num <= 19
)
SELECT Num, Fac
FROM cte_fac
WHERE Num = 6

Result: