How to Generate Fibonacci Series for the First 1000 Values? – Interview Question of the Week #174

May 20, 2018

Question: How to Generate Fibonacci Series for the First 1000 Values?

How to Generate Fibonacci Series for the First 1000 Values? - Interview Question of the Week #174 Fibonacci The reason, behind this question, is very simple, if you search online you will find the most of the answers only find the first 70 or maximum 80 values of the Fibonacci series. The reason behind is very simple, once you reach to 70th or 100th steps of the Fibonacci series the value is so big that often many programming languages produce the error.

Answer: In this blog post we will try to find an answer to Fibonacci in till 1000th value.

Here is the script which will generate first 1001 value of the Fibonacci series.

SELECT 0 AS Fibonacci
UNION ALL
SELECT
FLOOR(POWER(( 1 + SQRT(5)) / 2.0, number) / SQRT(5) + 0.5)
FROM master..spt_values
WHERE TYPE = 'p' AND number < 1000

You can always write Fibonacci series by writing cursor where you add previous values to the next value, however, that solution is very expensive and takes pretty long time to execute. Using the script which I have demonstrated, you will get the results pretty quickly.

If you are going to an interview, you can for sure write a cursor where you add values but writing script which is the most efficient will bring you additional brownie points.

Let me know if you prefer any other way to write this script. Please post your solution in the comments section. I will publish it with due credit.

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

SQL Function, SQL Scripts, SQL Server

How to Find Last Full Backup Time and Size for Database? – Interview Question of the Week #173

How to Write Errors in Error Log? – Interview Question of the Week #175

SQL SERVER – Example of DISTINCT in Aggregate Functions

June 3, 2008

SQL SERVER – Get Date of All Weekdays or Weekends of the Year

December 29, 2009

SQL SERVER – FIX – Error – Msg 4928, Level 16, State 1. Cannot Alter Column Because it is ‘Enabled for Replication or Change Data Capture’

December 24, 2016

2 Comments. Leave new

Stuart Turner
May 21, 2018 8:59 pm
with cte as (
select convert(float, 1) as PreviousValue, convert(float, 1) as Value
union all
select Value as PreviousValue, Value + PreviousValue as Value from cte)
select * from cte
option (maxrecusion 1000)
Reply
Kees de Boer
May 22, 2018 1:11 pm
SELECT 0 AS Fibonacci
UNION ALL
SELECT
FLOOR(POWER(( 1.0 + SQRT(5.0)) / 2.0, number) / SQRT(5.0) + 0.5)
FROM (
select
ROW_NUMBER() over(order by (select 1)) as [number] from sys.all_columns
) c
WHERE c.number < 1475
(1476 results in arithmatic overflow)
spt_values is a poorly document sort of lookup table inhereted from Sybase.
Roow_Number is handy for numbering records, sys.allcoumns will allways exist in mssql and holds several thousends of records…
NB (SELECT 1) is a trick to fool the window function which does not accept integer indices…
Reply