SQL SERVER – Fibonacci Series with T-SQL

In mathematics, the Fibonacci series is a sequence that has fascinated scholars for centuries. Named after the Italian mathematician Leonardo of Pisa, also known as Fibonacci, this series is a classic example of a recursive sequence. What’s fascinating about the Fibonacci series is its ubiquitous presence in nature, from the arrangement of leaves on a stem to the shape of a hurricane when viewed from space.

What is a Fibonacci Series?

A Fibonacci series is a sequence of numbers where each is the sum of the two preceding ones, usually starting with 0 and 1. The series looks like this: 0, 1, 1, 2, 3, 5, 8, 13, 21, 34, and so on. Each subsequent number is generated by adding up the two numbers before it.

Beyond mathematics, the Fibonacci sequence has numerous applications in computer science, economics, and physics. It’s also an excellent tool for teaching recursion, a fundamental concept in computer science.

Diving Into T-SQL: Generating Fibonacci Series with CTE

In today’s post, we will use Transact-SQL (T-SQL), Microsoft’s extension of SQL, to generate a Fibonacci series. One of the powerful features of T-SQL is the Common Table Expression (CTE), which allows us to create a temporary result set that can be referenced within another SELECT, INSERT, UPDATE, or DELETE statement.

Here’s the T-SQL code to generate the first 50 numbers of the Fibonacci series using a CTE:

WITH Fibonacci_CTE (Num, Value, Next_Value) AS
(
SELECT
1 AS Num,
CAST(0 AS BIGINT) AS Value,
CAST(1 AS BIGINT) AS Next_Value
UNION ALL
SELECT
Num + 1,
Next_Value,
Value + Next_Value
FROM Fibonacci_CTE
WHERE Num + 1 <= 50
)
SELECT Num, Value AS Fibonacci_Number FROM Fibonacci_CTE
OPTION (MAXRECURSION 50);

This CTE starts with the first two numbers in the sequence (0 and 1) and recursively generates the next number by summing the two preceding numbers. The OPTION (MAXRECURSION 50) statement increases the default recursion level in T-SQL to allow us to generate the first 50 Fibonacci numbers.

Crafting a Fibonacci Function in T-SQL

Now, let’s take one step further and create a T-SQL function that returns a Fibonacci number at a specific position in the sequence. Here’s the function:

CREATE FUNCTION dbo.GetFibonacci(@n INT)
RETURNS BIGINT
AS
BEGIN
DECLARE @result BIGINT;
WITH Fibonacci_CTE (Num, Value, Next_Value) AS
(
SELECT
1 AS Num,
CAST(0 AS BIGINT) AS Value,
CAST(1 AS BIGINT) AS Next_Value
UNION ALL
SELECT
Num + 1,
Next_Value,
Value + Next_Value
FROM Fibonacci_CTE
WHERE Num + 1 <= @n
)
SELECT @result = Value FROM Fibonacci_CTE WHERE Num = @n
OPTION (MAXRECURSION 0);
RETURN @result;
END;

This function uses a similar CTE to generate Fibonacci numbers but includes a WHERE clause to stop recursion when the desired position is reached. The `OPTION (MAXRECURSION 0)` statement removes the default recursion level limit, allowing you to generate Fibonacci numbers at any position within the integer range.

To use this function, you can call it like so:

SELECT dbo.GetFibonacci(10) AS Fibonacci_Number;

This will return the 10th Fibonacci number.

Final Thoughts

While simple in its definition, the Fibonacci series unveils a world of complexity and beauty. By understanding how to generate it using T-SQL, we have not only been able to explore a fascinating sequence of numbers but also delved into powerful features of T-SQL, like CTE and recursive functions.

Whether you’re a database administrator, a developer, or just passionate about SQL, I hope you’ve found this exploration as exciting as mine. The Fibonacci series is a beautiful example of how mathematics and computer science intertwine, and it’s a reminder that sometimes, the most complex problems can be solved with simple and elegant solutions.

You can watch my YouTube videos over here.

Reference: Pinal Dave (https://blog.sqlauthority.com)