Fibonacci Series in TSQL

Question

Post reply

Fibonacci Series in TSQL

ece.viky

Right there with Babe

Points: 727
More actions
December 1, 2014 at 5:35 pm

#312822

Comments posted to this topic are about the item Fibonacci Series in TSQL

Viewing 15 posts - 1 through 14 (of 14 total)

You must be logged in to reply to this topic. Login to reply

John Corkett SSCrazy Points: 2273 More actions · Answer 1

Hi,

I like the use of a CTE rather than a control loop structure, which is what I would have gone for.

I am curious though as to your use of the float data type (I avoid this data type like the plague!); the numbers you are dealing with are all integers and there is no division.

Thanks,

John

Lee Linares SSCrazy Points: 2727 More actions · Answer 2

Thanks for contributing this code. You should point out though that you are limited to the number of Fibonacci numbers that can be produced.

select * from dbo.fn_generate_fibonacciseries(47) will produce this error:

Msg 8115, Level 16, State 2, Line 2

Arithmetic overflow error converting expression to data type int.

The statement has been terminated.

If you changed the fields in the @returntable table to FLOAT you could get higher but when you pass 100 to the function you would get this error:

Msg 530, Level 16, State 1, Line 2

The statement terminated. The maximum recursion 100 has been exhausted before statement completion.

Realistically, I think the highest number you could pass to the function would be 183.

Thanks again.

Lee

Michael Meierruth SSChampion Points: 10051 More actions · Answer 3

OK, so it's a cute CTE exercise.

But has anyone ever used the Fibonacci series values for anything in the real world?

I would be curious to know.

John Corkett SSCrazy Points: 2273 More actions · Answer 4

has anyone ever used the Fibonacci series values for anything in the real world?
I would be curious to know.

I don't know if it is used in any database for anything; but it is certainly used in the real world. Search for Fibonacci and Flowers, Shells and Bees Ancestry on a search engine to get started. These all have features that follow the Fibonacci Sequence. It would be more acccurate to say that Finonacci discovered the sequence, rather than that he invented it. It has been used in the "real world" for millions of years".

John

Michael Meierruth SSChampion Points: 10051 More actions · Answer 5

By the term 'in the real world' I meant: actually used in some SQL code (or non SQL code) in a real software implementation.

akljfhnlaflkj SSC Guru Points: 76202 More actions · Answer 6

John Corkett (12/18/2014)
has anyone ever used the Fibonacci series values for anything in the real world?
I would be curious to know.
I don't know if it is used in any database for anything; but it is certainly used in the real world. Search for Fibonacci and Flowers, Shells and Bees Ancestry on a search engine to get started. These all have features that follow the Fibonacci Sequence. It would be more acccurate to say that Finonacci discovered the sequence, rather than that he invented it. It has been used in the "real world" for millions of years".
John

I guess there are some that use it. But I certainly don't in my mundane day-to-day computer programming job.

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 7

This was covered in 2009 by Peter Larsson.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

x SSC-Insane Points: 23660 More actions · Answer 8

x

SSC-Insane

Points: 23660

July 26, 2016 at 6:31 am

#1892020

where's the set oriented version? 😉

akljfhnlaflkj SSC Guru Points: 76202 More actions · Answer 9

Michael Meierruth (12/18/2014)
OK, so it's a cute CTE exercise.
But has anyone ever used the Fibonacci series values for anything in the real world?
I would be curious to know.

It can be used to search an array rather than the binary method.

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 10

Iwas Bornready (7/26/2016)
Michael Meierruth (12/18/2014)
OK, so it's a cute CTE exercise.
But has anyone ever used the Fibonacci series values for anything in the real world?
I would be curious to know.
It can be used to search an array rather than the binary method.

Interesting...do you have a link or example?

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 11

patrickmcginnis59 10839 (7/26/2016)
where's the set oriented version? 😉

Over to you, Patrick 🙂

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Thibaut Blanchin Newbie Points: 8 More actions · Answer 12

100 is the max number that can be passed to the function due to recursivity depth limit.

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 13

Thibaut Blanchin (7/26/2016)
100 is the max number that can be passed to the function due to recursivity depth limit.

From BOL:

"MAXRECURSION number

Specifies the maximum number of recursions allowed for this query. number is a nonnegative integer between 0 and 32767. When 0 is specified, no limit is applied. If this option is not specified, the default limit for the server is 100."

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

SoCal_DBD Hall of Fame Points: 3380 More actions · Answer 14

FWIW.... Although I am not using it in SQL, we do use it when assigning story points during "sprint planning". The highest story points we typically assign are 13. 1 story point = 1 day of work.