This probably falls under the category of ‘Why bother’. However, there has been some recent interest in calculating arbitrarily large fibonacci numbers. There is nothing particularly difficult about calculating these numbers until you reach the maximum number that a bigint (or decimal) can hold. With Fibonacci numbers, you reach this limit pretty fast. There’s nothing particularly difficult about calculating this sequence, it merely involves adding 2 numbers together. But, since we run in to the data type limit, we need to start thinking outside the box.

Obviously, we cannot store the numbers in any sort of number data type, so we need to use strings instead. Then, we need to create a way to add strings together as though they were numbers. This is the real challenge with calculating large numbers, but it’s really not that difficult either. We’ll simply write a function that adds 2 strings together as though they were numbers.

Please understand that this is not production quality code. I’m not validating the inputs and I’m being careless about the data type conversions. Also note that I use the varchar(max) data type. This implies SQL2005 code. If you want to test this on SQL2000, then you’ll need to replace varchar(max) with VarChar(8000). You won’t be able to calculate as many numbers, but you’ll still get quite a few.

``````Create Function dbo.AddString(@String1 VarChar(8000), @String2 VarChar(8000))
Returns VarChar(max)
As
Begin
Declare @CarryTheOne int
Declare @i Int
Declare @Output VarChar(max)
Declare @Digit1 Int
Declare @Digit2 int

If Len(@String1) < Len(@String2)
select @String1 = Replace(Right(Space(Len(@String2)) + @String1, Len(@String2)), ' ', '0')
Else
select @String2 = Replace(Right(Space(Len(@String1)) + @String2, Len(@String1)), ' ', '0')

set @CarryTheOne = 0
Set @i = 0
Set @Output = ''

While @i < Len(@String1)
Begin
Set @Digit1 = SubString(@String1, Len(@String1) - @i, 1)
Set @Digit2 = SubString(@String2, Len(@String1) - @i, 1)

Select @Output = Convert(VarChar(max), (@Digit1 + @Digit2 + @CarryTheOne) % 10) + @Output
If @Digit1 + @Digit2 + @CarryTheOne > 9
Set @CarryTheOne = 1
Else
Set @CarryTheOne = 0

Set @i = @i + 1

End

If @CarryTheOne = 1
Set @Output = '1' + @Output

Return @Output
End
``````

The user defined function shown above allows us to add strings as though they were numbers. Now, how do we calculate the fibonacci series? Like this:

``````Declare @Temp Table(Id Int Identity(1,1), FIB VarChar(max))

Insert Into @Temp(FIB) Values('0')
Insert Into @Temp(FIB) Values('1')

Declare @i Int
Select @i = Max(Id) From @Temp

While @i < 1000
Begin

Insert Into @Temp(FIB)
Select dbo.AddString((Select Fib From @Temp Where Id = @i), (Select Fib From @Temp Where Id = @i-1))

Set @i = @i + 1

End

Select Fib From @Temp Order By Id
``````

That’s all there is to it!