BSW.Blog

Calculate First 182 Fibonaccis  (Fibonacci numbers) Using SQL Server CTE

with Fibonacci(ID,Prev,Number)as(
	select 1,cast(1 as Numeric(38)),cast(1 as Numeric(38))
	union all select ID+1, cast(Number as Numeric(38)) ,cast(Prev+Number as Numeric(38)) from Fibonacci where ID<182
	) 
select 'F('+cast(ID as nvarchar(50))+') = '+ cast(Number as nvarchar(50)) from Fibonacci
OPTION (MAXRECURSION 183);

Result:

F(1) = 1
F(2) = 2
F(3) = 3
F(4) = 5
F(5) = 8
F(6) = 13
F(7) = 21
F(8) = 34
F(9) = 55
F(10) = 89
F(11) = 144
F(12) = 233
F(13) = 377
F(14) = 610
F(15) = 987
F(16) = 1597
F(17) = 2584
F(18) = 4181
F(19) = 6765
F(20) = 10946
F(21) = 17711
F(22) = 28657
F(23) = 46368
F(24) = 75025
F(25) = 121393
F(26) = 196418
F(27) = 317811
F(28) = 514229
F(29) = 832040
F(30) = 1346269
F(31) = 2178309
F(32) = 3524578
F(33) = 5702887
F(34) = 9227465
F(35) = 14930352
F(36) = 24157817
F(37) = 39088169
F(38) = 63245986
F(39) = 102334155
F(40) = 165580141
F(41) = 267914296
F(42) = 433494437
F(43) = 701408733
F(44) = 1134903170
F(45) = 1836311903
F(46) = 2971215073
F(47) = 4807526976
F(48) = 7778742049
F(49) = 12586269025
F(50) = 20365011074
F(51) = 32951280099
F(52) = 53316291173
F(53) = 86267571272
F(54) = 139583862445
F(55) = 225851433717
F(56) = 365435296162
F(57) = 591286729879
F(58) = 956722026041
F(59) = 1548008755920
F(60) = 2504730781961
F(61) = 4052739537881
F(62) = 6557470319842
F(63) = 10610209857723
F(64) = 17167680177565
F(65) = 27777890035288
F(66) = 44945570212853
F(67) = 72723460248141
F(68) = 117669030460994
F(69) = 190392490709135
F(70) = 308061521170129
F(71) = 498454011879264
F(72) = 806515533049393
F(73) = 1304969544928657
F(74) = 2111485077978050
F(75) = 3416454622906707
F(76) = 5527939700884757
F(77) = 8944394323791464
F(78) = 14472334024676221
F(79) = 23416728348467685
F(80) = 37889062373143906
F(81) = 61305790721611591
F(82) = 99194853094755497
F(83) = 160500643816367088
F(84) = 259695496911122585
F(85) = 420196140727489673
F(86) = 679891637638612258
F(87) = 1100087778366101931
F(88) = 1779979416004714189
F(89) = 2880067194370816120
F(90) = 4660046610375530309
F(91) = 7540113804746346429
F(92) = 12200160415121876738
F(93) = 19740274219868223167
F(94) = 31940434634990099905
F(95) = 51680708854858323072
F(96) = 83621143489848422977
F(97) = 135301852344706746049
F(98) = 218922995834555169026
F(99) = 354224848179261915075
F(100) = 573147844013817084101
F(101) = 927372692193078999176
F(102) = 1500520536206896083277
F(103) = 2427893228399975082453
F(104) = 3928413764606871165730
F(105) = 6356306993006846248183
F(106) = 10284720757613717413913
F(107) = 16641027750620563662096
F(108) = 26925748508234281076009
F(109) = 43566776258854844738105
F(110) = 70492524767089125814114
F(111) = 114059301025943970552219
F(112) = 184551825793033096366333
F(113) = 298611126818977066918552
F(114) = 483162952612010163284885
F(115) = 781774079430987230203437
F(116) = 1264937032042997393488322
F(117) = 2046711111473984623691759
F(118) = 3311648143516982017180081
F(119) = 5358359254990966640871840
F(120) = 8670007398507948658051921
F(121) = 14028366653498915298923761
F(122) = 22698374052006863956975682
F(123) = 36726740705505779255899443
F(124) = 59425114757512643212875125
F(125) = 96151855463018422468774568
F(126) = 155576970220531065681649693
F(127) = 251728825683549488150424261
F(128) = 407305795904080553832073954
F(129) = 659034621587630041982498215
F(130) = 1066340417491710595814572169
F(131) = 1725375039079340637797070384
F(132) = 2791715456571051233611642553
F(133) = 4517090495650391871408712937
F(134) = 7308805952221443105020355490
F(135) = 11825896447871834976429068427
F(136) = 19134702400093278081449423917
F(137) = 30960598847965113057878492344
F(138) = 50095301248058391139327916261
F(139) = 81055900096023504197206408605
F(140) = 131151201344081895336534324866
F(141) = 212207101440105399533740733471
F(142) = 343358302784187294870275058337
F(143) = 555565404224292694404015791808
F(144) = 898923707008479989274290850145
F(145) = 1454489111232772683678306641953
F(146) = 2353412818241252672952597492098
F(147) = 3807901929474025356630904134051
F(148) = 6161314747715278029583501626149
F(149) = 9969216677189303386214405760200
F(150) = 16130531424904581415797907386349
F(151) = 26099748102093884802012313146549
F(152) = 42230279526998466217810220532898
F(153) = 68330027629092351019822533679447
F(154) = 110560307156090817237632754212345
F(155) = 178890334785183168257455287891792
F(156) = 289450641941273985495088042104137
F(157) = 468340976726457153752543329995929
F(158) = 757791618667731139247631372100066
F(159) = 1226132595394188293000174702095995
F(160) = 1983924214061919432247806074196061
F(161) = 3210056809456107725247980776292056
F(162) = 5193981023518027157495786850488117
F(163) = 8404037832974134882743767626780173
F(164) = 13598018856492162040239554477268290
F(165) = 22002056689466296922983322104048463
F(166) = 35600075545958458963222876581316753
F(167) = 57602132235424755886206198685365216
F(168) = 93202207781383214849429075266681969
F(169) = 150804340016807970735635273952047185
F(170) = 244006547798191185585064349218729154
F(171) = 394810887814999156320699623170776339
F(172) = 638817435613190341905763972389505493
F(173) = 1033628323428189498226463595560281832
F(174) = 1672445759041379840132227567949787325
F(175) = 2706074082469569338358691163510069157
F(176) = 4378519841510949178490918731459856482
F(177) = 7084593923980518516849609894969925639
F(178) = 11463113765491467695340528626429782121
F(179) = 18547707689471986212190138521399707760
F(180) = 30010821454963453907530667147829489881
F(181) = 48558529144435440119720805669229197641
F(182) = 78569350599398894027251472817058687522

Calculate 2000! In Powershell

Multiplying Big Numbers In Powershell And Calculate 2000! in seconds.

function MUL {
    param ([string]$a ,[string]$b)
    [string]$res=""
    [bigint]$m=0
    $a.Split()|%{
    [bigint]$c=[bigint]$_*[bigint]$b+$m;
    [bigint]$d=$c%10
    $m=($c-$d)/10
    
    $res=$res+$d.ToString();
    }
    $res=$m.ToString()+$res;
    $res
}
$a=1
1..2000|%{$a=mul $a $_;echo "$($_)!=$($a)"} |select -last 1
[system.gc]::Collect()

Result:

PS C:\> C:\MUL.ps1
5!=120

PS C:\> C:\MUL.ps1
50!=30414093201713378043612608166064768844377641568960512000000000000

PS C:\> C:\MUL.ps1
500!=12201368259911100687012387854230469262535743428031928421924135883858453731538819976054964475022032818630136164771482035841633787220781772004807852051593292854779075
7193933060377296085908627042917454788242491272634430567017327076946106280231045264421887878946575477714986349436778103764427403382736539747138647787849543848959553753799
0423241061271326984327745715546309977202781014561081188373709531016356324432987029563896628911658974769572087926928871281780070265174507768410719624390394322536422605234
9458501299185715012487069615681416253590566934238130088562492468915641267756544818865065938479517753608940057452389403357984763639449053130623237490664450488246650759467
3586207463792518420045936969298102226397195259719094521782333175693458150855233282076282002340262690789834245171200620771464097945611612762914595123722991334016955236385
0942885592018727433795173014586357570828355780158735432768888680120399882384702151467605445407663535984174430480128938313896881639487469658817504506926365338175055478128
640000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000

PS C:\> C:\BigNumberMultiply\MUL.ps1
2000!=3316275092450633241175393380576324038281117208105780394571935437060380779056008224002732308597325922554023529412258341092580848174152937961313866335263436889056340
5855616394060511725257187064785639354404540524395746703767410872297043468415834375243158087753364512748799543685924740803240894656150723325065279765575717967153671868935
9056112815871601717232657156110004214012420433842573712700175883547796899921283528996665853405579854903657366350133386550401172012152635488038268152152246920995206031564
4185654806759464970515522882052348999957264508140655366789695321014676226713320268315522051944944616182392752040265297226315025747520482960647509273941658562835317795744
8287631459645037399132733417726360885249009350662161014445970941270782131373256383157230201994991495831647094277447387032798554967429860883937632682415247883438746959582
9257740574539837501585815468136294217949972399813599481016556563876034227312912250384709872909626622461971076605931550201895135583165357871492290916779049702247094611937
6077851651106844322559056487362665303773846503907880495246007125494026145660722541363027549136715834060978310749452822174907813477096932415561113398280513586006905946199
6525731074117708151992256451677857145805660218565476095237746301667942248844448579834980154803262082989096585738175188861937669282827988845358463989659421395298446529109
2009103710046149449915828588050761867924946385180879874512891408019340074625920057098729578599643650655895612410231018690556060308783629110505601245908998383410799367902
0520768586691834779065585447001486926569246319333376124280974200671728463619392496986284687199934503938893672704871271727345617003548674775091029555239535479411074219133
0135681954109194146276641754216158762526285808980122244389024867718205495941575199170127176757178749586161966593187885514183578209260148207177733173539603430496908207058
9958701381980813035590160762908388574561288217698136182483576739218303118414719133986892842344000779246691209766731651433494437473235636572048844478331854941693030124531
6762327453678793228474738244850922831399525097325059791270310476836014811911022292533726976938236700575656124002905760438528529029376064795334581796661238396052625491071
8666386935476610845504619810208405063582767652658949239324951968595417167241932953068367349554400458635983816104305944982662753060542358075589410827888042782595108988063
5410567917950974017780688782869810219010900148352061688883720250310665922068601483649830532782088263536558043605686781284169217133047141176312175895777122637584753123517
2309905498292101346873042058980144180638753826641698977042377594062808772537022654265305808623793014226758211871435029186376363403001732518182620760397473695952026426323
6414544685111342720215045838385101013694131303485622191663162389263276581535501127630782505996915882453345743543786368317373067329658935519969445823687350883027865770087
9749889992343555566240682834763784685183844973648873952475103224222110561201295829657191368108693825475764118886879346725191246192151144738836269591643672490071653428228
1526612478004639225449451703637236279407577845420910483054616561906221742869816029733240465202019928138548826819510072828697010707375009276664875021747753727423515087482
4672027417003158112280589617812216074743794751095062093855667458125251837668215771280786149925587613235295042234638787895485088576446613629039412766597804420209228133798
7115900896264878942413210454925003566670632909441579372986743421470507213588932019580723064781498429522595589012754823971773325722910325760929790733299545056388362640474
6502450808094691160726320874941439730007041114185955302788273576548191820024496977611113463181952827615909641897909581173386272060889104329452449785351470141124421430554
8608963957837834732532359576329143892528839398625627324286277556314046383038916842163311344563630957196597846633855149231619633567535513840342580416291983782226690952177
0153175338730284610841886554138329171951332117895728541662084823682817932512931237521541926970269703299477643823386483008871530373405666383868294088487730721762268849023
0849346611942601802726138021080050782157410060548482013478595781027707077806555127725405016743323960662532164150048087724030476119290322101543853531386855384864255707907
9534117651957118868373988068389579274374968349814292329219630977709014393684365533335930782018131299345502420604456334057860696247196150560339489952332180043435996725662
3927196435402872055475012079854331970674797313126813523653744085662263206768837585132782896252333284341812977624697079543436003492343159239674763638912115285406657783646
2139112474470512552263427012395270181270454916480459322481088586746009523067931759677555810116799400052498063037631413444122690370349873557999160092592480750524855415682
6628176081544630830540667741263012444186420410837311909313000115447056027777372437806718889977085105672727678124719883285769584421758889516046786820481001004781646235822
0838532488134270834079868486632162720208823308727819085378845469131556021728873121907393965209260229101477527080930865364979858554010577450279289814603688431821508637246
2169678722821693473705992862771124476909209029883201668301702734202597656717098633112163495021712644268271196502640542282317596308744753018471940955242634114984695080733
9008000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000
0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000
000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000

Get Sequence of Numbers Using CTE (Common Table Expression)

Declare @From bigint ,@to bigint
Select @From =10 ,@To =100 ;
With CTE(Number)as 
	(Select @From 
	union all 
	select Number+1 
		from CTE
		where Number<@to 
	)
select Number From CTE 
option (maxrecursion 0)

Result:

Number
10
11
12
13
14
15
.
.
.
.
.
.
92
93
94
95
96
97
98
99
100

Convert Bigint Numbers to any Base using TSQL and Common Table Expressions

Here Is Small Query to Convert Numbers to Desired Base:

All the things needed is:

Set the available digits in your desired base as below

Select @digits ='0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz',@base = LEN (@Digits ) ,  @res='';

Then set the Number to convert Like Below

Select @Number=500000000000;

Common table expression query is :

Declare @Base Int , @res Nvarchar(50) ,@Digits Nvarchar (100)
declare @Number bigint 
Select @digits ='0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz',@base = LEN (@Digits ) ,  @res='';
select @Base as Base 
Select @Number=500000000000;
with cte(ID, Number , Module) as 
	(Select 1 ,@Number , cast (0 as int)
		Union all 
		Select cte.ID +1 , (floor(Number/@base)  ) , cast(Number%@Base as int )  from CTE
		where number>0  
)
select @res=substring(@digits,Module +1  , 1)+@res   From Cte where ID>1  order By ID 
select @res  as Result 

result

Base
-----------
62

(1 row(s) affected)

Result
--------------------------------------------------
8nlt2n2

(1 row(s) affected)

if you change the digit to 0 and 1 you can have base 2 Conversion and also for each base you can change the digits to available digits in that base.