Really bigint via T-SQL - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Really bigint via T-SQL

Author

Topic

Stoad
Freaky Yak Linguist

1983 Posts

Posted - 2004-01-08 : 13:21:58

Who can get it? E.g. 500! = 1*2*3*4*5* ... ... *499*500 (see below its
digits)....

12201368259911100687012387854230469262535743428031928421924135
88385845373153881997605496447502203281863013616477148203584163
37872207817720048078520515932928547790757193933060377296085908
62704291745478824249127263443056701732707694610628023104526442
18878789465754777149863494367781037644274033827365397471386477
87849543848959553753799042324106127132698432774571554630997720
27810145610811883737095310163563244329870295638966289116589747
69572087926928871281780070265174507768410719624390394322536422
60523494585012991857150124870696156814162535905669342381300885
62492468915641267756544818865065938479517753608940057452389403
35798476363944905313062323749066445048824665075946735862074637
92518420045936969298102226397195259719094521782333175693458150
85523328207628200234026269078983424517120062077146409794561161
27629145951237229913340169552363850942885592018727433795173014
58635757082835578015873543276888868012039988238470215146760544
54076635359841744304801289383138968816394874696588175045069263
65338175055478128640000000000000000000000000000000000000000000
00000000000000000000000000000000000000000000000000000000000000
0000000000000000000

X002548
Not Just a Number

15586 Posts

Posted - 2004-01-08 : 15:22:41

How do you proof it?

I'm figuring some form of iteration....but that's about it...

so far...

Brett

8-)

Stoad
Freaky Yak Linguist

1983 Posts

Posted - 2004-01-08 : 15:54:57

Aha, Brett :)
BTW, can anybody check for how fast it's calculated in
something like MathCad (never used it) or in something of this kind?

nr
SQLTeam MVY

12543 Posts

Posted - 2004-01-09 : 06:33:54

Looks like it's correct
000000000000000000000000000000000000000000000000000000000000000001220136825991110068701238785423046926253574342803192842192413588385845373153881997605496447502203281863013616477148203584163378722078177200480785205159329285477907571939330603772960859086270429174547882424912726344305670173270769461062802310452644218878789465754777149863494367781037644274033827365397471386477878495438489595537537990423241061271326984327745715546309977202781014561081188373709531016356324432987029563896628911658974769572087926928871281780070265174507768410719624390394322536422605234945850129918571501248706961568141625359056693423813008856249246891564126775654481886506593847951775360894005745238940335798476363944905313062323749066445048824665075946735862074637925184200459369692981022263971952597190945217823331756934581508552332820762820023402626907898342451712006207714640979456116127629145951237229913340169552363850942885592018727433795173014586357570828355780158735432768888680120399882384702151467605445407663535984174430480128938313896881639487469658817504506926365338175055478128640000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000

set nocount on
declare @i int
declare @t table (i int)
select @i = 0
declare @s1 varchar(8000)
declare @s2 varchar(8000)
declare @s3 varchar(8000)
declare @s35 varchar(8000)
declare @s4 varchar(8000)
declare @s5 varchar(8000)
declare @s6 varchar(8000)
declare @s7 varchar(8000)
declare @s998 varchar(8000)
declare @s999a varchar(8000)
declare @s999b varchar(8000)

while @i < 240
begin
select @i = @i + 1
insert @t select @i
end

select @s1 = coalesce(@s1 + ',', ' ') + '@t' + convert(varchar(10),i) + ' int' ,
@s2 = coalesce(@s2 + ',', ' ') + '@t' + convert(varchar(10),i) + '=0' ,
@s35 = coalesce(@s35 + ',', ' select ') + 't' + convert(varchar(10),i) + '= @t' + convert(varchar(10),i) ,
@s4 = coalesce(@s4 + ',', ' select ') + '@t' + convert(varchar(10),i) + '=@t' + convert(varchar(10),i) + '*@i'
from @t

declare @z int
select @z = max(i) from @t
select @s999a = ' select '
while @z > 120
begin
select @s999a = @s999a + 'right(''0000''+convert(varchar(20),@t' + convert(varchar(10),@z) + '),5)+'
select @z = @z - 1
end
select @s999b = ''
while @z > 0
begin
select @s999b = @s999b + 'right(''0000''+convert(varchar(20),@t' + convert(varchar(10),@z) + '),5)+'
select @z = @z - 1
end
select @s999b = left(@s999b,len(@s999b)-1)

select @s5 = coalesce (@s5 + ' ',' ') + 'if @t' + convert(varchar(10),i) + '>power(10,5) select @t' + convert(varchar(10),i+1) + '=@t' + convert(varchar(10),i+1) + '+@t' + convert(varchar(10),i) + '/power(10,5), @t' + convert(varchar(10),i) + ' = @t' + convert(varchar(10),i) + ' % convert(int,power(10,5))'
from @t
where i <= 80
select @s6 = coalesce (@s6 + ' ',' ') + 'if @t' + convert(varchar(10),i) + '>power(10,5) select @t' + convert(varchar(10),i+1) + '=@t' + convert(varchar(10),i+1) + '+@t' + convert(varchar(10),i) + '/power(10,5), @t' + convert(varchar(10),i) + ' = @t' + convert(varchar(10),i) + ' % convert(int,power(10,5))'
from @t
where i > 80 and i <= 160
select @s7 = coalesce (@s7 + ' ',' ') + 'if @t' + convert(varchar(10),i) + '>power(10,5) select @t' + convert(varchar(10),i+1) + '=@t' + convert(varchar(10),i+1) + '+@t' + convert(varchar(10),i) + '/power(10,5), @t' + convert(varchar(10),i) + ' = @t' + convert(varchar(10),i) + ' % convert(int,power(10,5))'
from @t
where i > 160 and i <= 239

select @s1 = 'declare ' + @s1 ,
@s2 = ' select ' + @s2

select @s3 = ' select @t1 = 1 declare @i int select @i = 0 while @i < 500 begin select @i = @i + 1' ,
@s998 = ' end'

select s1 = @s1
select s2 = @s2
select s3 = @s3
select s4 = @s4
select s5 = @s5
select s6 = @s6
select s7 = @s7
select s998 = @s998
select s999a = @s999b
select s999b = @s999b

exec (@s1 + @s2 + @s3 + @s4 + @s5 + @s6 + @s7 + @s998 + @s999a + @s999b)

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

nr
SQLTeam MVY

12543 Posts

Posted - 2004-01-09 : 08:28:07

This could become a readers challenge.
The solution I have given has hard coded stuff for @s5,@s6,@s7 - limitted by the length of the string.
You can get round this by building up the sql in a text column of a single rec temp table then splitting it into strings in dynamic sql.
It still has the problem that the output string is limitted to 8000 chars - you could get round this by another text column.

I've done a similar thing in the past by building the dynamic sql in dynamic sql to give a variable number of strings - you have to keep creating levels of exec statements as each level runs out of string space and create a dynamic sql nest level generator to be flexible. The text column would be a lot simpler.

Anyone want to give that a try or think of an easier way?

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Stoad
Freaky Yak Linguist

1983 Posts

Posted - 2004-01-09 : 08:46:22

Wow.. nr.. I am shocked! Very impressive!! Pity I can't run it on my ver.7.0.
Sure it's not easy to write.. at least it requires extremely careful coding....
Honestly speaking, LOL, I have not done this so far.

nr
SQLTeam MVY

12543 Posts

Posted - 2004-01-09 : 09:03:54

Why doesn't it run on v7?

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

nr
SQLTeam MVY

12543 Posts

Posted - 2004-01-09 : 09:13:02

Oops - table variable
try
set nocount on
declare @i int
create table #t (i int)
select @i = 0
declare @s1 varchar(8000)
declare @s2 varchar(8000)
declare @s3 varchar(8000)
declare @s35 varchar(8000)
declare @s4 varchar(8000)
declare @s5 varchar(8000)
declare @s6 varchar(8000)
declare @s7 varchar(8000)
declare @s998 varchar(8000)
declare @s999a varchar(8000)
declare @s999b varchar(8000)

while @i < 240
begin
select @i = @i + 1
insert #t select @i
end

select @s1 = coalesce(@s1 + ',', ' ') + '@t' + convert(varchar(10),i) + ' int' ,
@s2 = coalesce(@s2 + ',', ' ') + '@t' + convert(varchar(10),i) + '=0' ,
@s35 = coalesce(@s35 + ',', ' select ') + 't' + convert(varchar(10),i) + '= @t' + convert(varchar(10),i) ,
@s4 = coalesce(@s4 + ',', ' select ') + '@t' + convert(varchar(10),i) + '=@t' + convert(varchar(10),i) + '*@i'
from #t

declare @z int
select @z = max(i) from #t
select @s999a = ' select '
while @z > 120
begin
select @s999a = @s999a + 'right(''0000''+convert(varchar(20),@t' + convert(varchar(10),@z) + '),5)+'
select @z = @z - 1
end
select @s999b = ''
while @z > 0
begin
select @s999b = @s999b + 'right(''0000''+convert(varchar(20),@t' + convert(varchar(10),@z) + '),5)+'
select @z = @z - 1
end
select @s999b = left(@s999b,len(@s999b)-1)

select @s5 = coalesce (@s5 + ' ',' ') + 'if @t' + convert(varchar(10),i) + '>power(10,5) select @t' + convert(varchar(10),i+1) + '=@t' + convert(varchar(10),i+1) + '+@t' + convert(varchar(10),i) + '/power(10,5), @t' + convert(varchar(10),i) + ' = @t' + convert(varchar(10),i) + ' % convert(int,power(10,5))'
from #t
where i <= 80
select @s6 = coalesce (@s6 + ' ',' ') + 'if @t' + convert(varchar(10),i) + '>power(10,5) select @t' + convert(varchar(10),i+1) + '=@t' + convert(varchar(10),i+1) + '+@t' + convert(varchar(10),i) + '/power(10,5), @t' + convert(varchar(10),i) + ' = @t' + convert(varchar(10),i) + ' % convert(int,power(10,5))'
from #t
where i > 80 and i <= 160
select @s7 = coalesce (@s7 + ' ',' ') + 'if @t' + convert(varchar(10),i) + '>power(10,5) select @t' + convert(varchar(10),i+1) + '=@t' + convert(varchar(10),i+1) + '+@t' + convert(varchar(10),i) + '/power(10,5), @t' + convert(varchar(10),i) + ' = @t' + convert(varchar(10),i) + ' % convert(int,power(10,5))'
from #t
where i > 160 and i <= 239

select @s1 = 'declare ' + @s1 ,
@s2 = ' select ' + @s2

select @s3 = ' select @t1 = 1 declare @i int select @i = 0 while @i < 500 begin select @i = @i + 1' ,
@s998 = ' end'

select s1 = @s1
select s2 = @s2
select s3 = @s3
select s4 = @s4
select s5 = @s5
select s6 = @s6
select s7 = @s7
select s998 = @s998
select s999a = @s999b
select s999b = @s999b

exec (@s1 + @s2 + @s3 + @s4 + @s5 + @s6 + @s7 + @s998 + @s999a + @s999b)
drop table #t

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

X002548
Not Just a Number

15586 Posts

Posted - 2004-01-09 : 09:31:18

quote:
Originally posted by Stoad

Honestly speaking, LOL, I have not done this so far.

You didn't have the answer?

oye

Where did you get the question?

Brett

8-)

Stoad
Freaky Yak Linguist

1983 Posts

Posted - 2004-01-09 : 10:57:16

Run it! Tremendously!!

Brett,

the Answer I got from "a third party" (it does not matter),

Solutions I have none (except that by nr),

the Question was put by the Nature....

btw, do you know what greek "anthropos" means? Aha!

nr
SQLTeam MVY

12543 Posts

Posted - 2004-01-09 : 11:00:03

Here's the result for 1000! - I'll leave it to someone to check

402387260077093773543702433923003985719374864210714632543799910429938512398629020592044208486969404800479988610197196058631666872994808558901323829669944590997424504087073759918823627727188732519779505950995276120874975462497043601418278094646496291056393887437886487337119181045825783647849977012476632889835955735432513185323958463075557409114262417474349347553428646576611667797396668820291207379143853719588249808126867838374559731746136085379534524221586593201928090878297308431392844403281231558611036976801357304216168747609675871348312025478589320767169132448426236131412508780208000261683151027341827977704784635868170164365024153691398281264810213092761244896359928705114964975419909342221566832572080821333186116811553615836546984046708975602900950537616475847728421889679646244945160765353408198901385442487984959953319101723355556602139450399736280750137837615307127761926849034352625200015888535147331611702103968175921510907788019393178114194545257223865541461062892187960223838971476088506276862967146674697562911234082439208160153780889893964518263243671616762179168909779911903754031274622289988005195444414282012187361745992642956581746628302955570299024324153181617210465832036786906117260158783520751516284225540265170483304226143974286933061690897968482590125458327168226458066526769958652682272807075781391858178889652208164348344825993266043367660176999612831860788386150279465955131156552036093988180612138558600301435694527224206344631797460594682573103790084024432438465657245014402821885252470935190620929023136493273497565513958720559654228749774011413346962715422845862377387538230483865688976461927383814900140767310446640259899490222221765904339901886018566526485061799702356193897017860040811889729918311021171229845901641921068884387121855646124960798722908519296819372388642614839657382291123125024186649353143970137428531926649875337218940694281434118520158014123344828015051399694290153483077644569099073152433278288269864602789864321139083506217095002597389863554277196742822248757586765752344220207573630569498825087968928162753848863396909959826280956121450994871701244516461260379029309120889086942028510640182154399457156805941872748998094254742173582401063677404595741785160829230135358081840096996372524230560855903700624271243416909004153690105933983835777939410970027753472000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000

And here's the text datatype version which I used (there are a few boundary bugs which I haven't bothered with).
If you go much further with it sql server decides it can't handle it.
Maybe I'll do an article about how I went about developing these.

set nocount on
declare @Val int
select @Val = 1000
declare @NumVars int
select @NumVars = 600

declare @i int
declare @s varchar(8000)

create table #sql (t text)
insert #sql select ''
declare @ptr binary(16)
select @ptr = textptr(t) from #sql

-- declare variables
updatetext #sql.t @ptr null 0 'declare '
select @i = 0
while @i < @NumVars
begin
select @i = @i + 1
select @s = '@t' + convert(varchar(20),@i) + ' int,'
if @i = @NumVars
select @s = left(@s,len(@s)-1)
updatetext #sql.t @ptr null 0 @s
end
updatetext #sql.t @ptr null 0 ' '

-- initialise variables
updatetext #sql.t @ptr null 0 'select '
select @i = 0
while @i < @NumVars
begin
select @i = @i + 1
select @s = '@t' + convert(varchar(20),@i) + '=0,'
if @i = @NumVars
select @s = left(@s,len(@s)-1)
updatetext #sql.t @ptr null 0 @s
end
updatetext #sql.t @ptr null 0 ' '

-- set first var to 1
updatetext #sql.t @ptr null 0 'select @t1 = 1 '

-- create loop
select @s = 'declare @i int select @i = 0 while @i < ' + convert(varchar(20),@Val) + ' begin select @i = @i + 1 '
updatetext #sql.t @ptr null 0 @s

-- Multiply all variables
updatetext #sql.t @ptr null 0 'select '
select @i = 0
while @i < @NumVars
begin
select @i = @i + 1
select @s = '@t' + convert(varchar(20),@i) + '=@t' + convert(varchar(20),@i) + '*@i,'
if @i = @NumVars
select @s = left(@s,len(@s)-1)
updatetext #sql.t @ptr null 0 @s
end
updatetext #sql.t @ptr null 0 ' '

-- move the data up the variable list
select @i = 0
while @i < @NumVars - 1
begin
select @i = @i + 1
select @s = 'if @t' + convert(varchar(20),@i) + '>power(10,5) select @t' + convert(varchar(20),@i+1) + '=@t' + convert(varchar(20),@i+1) + '+@t' + convert(varchar(20),@i) + '/power(10,5), @t' + convert(varchar(20),@i) + ' = @t' + convert(varchar(20),@i) + ' % convert(int,power(10,5)) '
updatetext #sql.t @ptr null 0 @s
end

-- end loop
updatetext #sql.t @ptr null 0 'end '

-- now get the data
updatetext #sql.t @ptr null 0 ' declare @res varchar(8000) select @res = '
select @i = @NumVars + 1
while @i > 1
begin
select @i = @i - 1
select @s = 'right(''0000''+convert(varchar(20),@t' + convert(varchar(20),@i) + '),5)+'
if @i = 1
select @s = left(@s,len(@s)-1)
updatetext #sql.t @ptr null 0 @s
end
updatetext #sql.t @ptr null 0 ' select right(@res,len(@res)-patindex(''%[^0]%'',@res)+1)'

-- create exec in variables
select @i = datalength(t) from #sql
select @NumVars = @i / 8000 + 1

create table #call (t text)
insert #call select ''
select @ptr = textptr(t) from #call

updatetext #call.t @ptr null 0 'declare '
select @i = 0
while @i < @NumVars
begin
select @i = @i + 1
select @s = '@s' + convert(varchar(20),@i) + ' varchar(8000),'
if @i = @NumVars
select @s = left(@s,len(@s)-1)
updatetext #call.t @ptr null 0 @s
end
updatetext #call.t @ptr null 0 ' '

updatetext #call.t @ptr null 0 'select '
select @i = 0
while @i < @NumVars
begin
select @i = @i + 1
select @s = '@s' + convert(varchar(20),@i) + ' = substring(t,' + convert(varchar(20),(@i-1) * 8000 + 1) + ',8000),'
if @i = @NumVars
select @s = left(@s,len(@s)-1)
updatetext #call.t @ptr null 0 @s
end
updatetext #call.t @ptr null 0 ' from #sql '

updatetext #call.t @ptr null 0 'exec ('
select @i = 0
while @i < @NumVars
begin
select @i = @i + 1
select @s = '@s' + convert(varchar(20),@i) + '+'
if @i = @NumVars
select @s = left(@s,len(@s)-1)
updatetext #call.t @ptr null 0 @s
end
updatetext #call.t @ptr null 0 ') '

select @s = t from #call
exec(@s)

drop table #sql
drop table #call

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Stoad
Freaky Yak Linguist

1983 Posts

Posted - 2004-01-09 : 15:02:55

OMG.. It is left to understand how it works..

Stoad
Freaky Yak Linguist

1983 Posts

Posted - 2004-01-09 : 19:17:18

I only managed to write this ugly stored procedure for
'addition' of two 'string' numbers: '22222' + '111' >> '22333',
it can be used for calculating of factorials of small numbers
(I tested it), say, up to 20-30. But not for 500! - it is terribly
slow............... nr's code works like a champ.

create procedure spAdder
@s1 varchar(8000), @s2 varchar(8000), @s varchar(8000) output
as
declare @i int, @n tinyint, @f bit
if len(@s2)>len(@s1) begin set @s=@s1 set @s1=@s2 set @s2=@s end
set @s='' set @f=0
set @i=len(@s2)
while @i>0
begin
set @n=
cast(substring(@s1,@i+len(@s1)-len(@s2),1) as tinyint)+
cast(substring(@s2,@i,1) as tinyint)+@f
set @s=right(cast(@n as varchar(2)),1)+@s
if @n>9 set @f=1 else set @f=0
set @i=@i-1
end
if @f=1
begin
set @i=len(@s1)-len(@s2)
while substring(@s1,@i,1)='9' and @i>0 set @i=@i-1
if @i>0 set @s=left(@s1,@i-1)+
cast(cast(substring(@s1,@i,1) as tinyint)+1 as varchar(1))+
replicate('0',len(@s1)-len(@s2)-@i)+@s
else set @s='1'+replicate('0',len(@s1)-len(@s2))+@s
end
else set @s=left(@s1,len(@s1)-len(@s2))+@s
return 0

Stoad
Freaky Yak Linguist

1983 Posts

Posted - 2004-01-13 : 04:12:58

Quite good for "small" bignums (e.g., 100!) but much much less
efficient than Nigel's super-code (still out of my comprehension):

create procedure spAdder
@s1 varchar(8000), @s2 varchar(8000), @s varchar(8000) output
as
declare @i int, @f int, @a varchar(10)
set @f=0 set @s=''
if len(@s1)>len(@s2)
begin
set @s1=replicate('0',(9-len(@s1)%9)%9)+@s1
set @s2=replicate('0',len(@s1)-len(@s2))+@s2
end
else
begin
set @s2=replicate('0',(9-len(@s2)%9)%9)+@s2
set @s1=replicate('0',len(@s2)-len(@s1))+@s1
end
set @i=len(@s1)-8
while @i>0
begin
set @a=cast(@f+cast(substring(@s1,@i,9) as int)+
cast(substring(@s2,@i,9) as int) as varchar(10))
if len(@a)>9 set @f=1 else
begin set @f=0 if @i>9 set @a=replicate('0',9-len(@a))+@a end
set @s=right(@a,9)+@s
set @i=@i-9
end
if @f=1 set @s='1'+@s
return

declare @s1 varchar(8000), @s2 varchar(8000), @s varchar(8000)
declare @i int, @j int set @i=2
set @s1='2' set @s2=@s1

while @i<100
begin
set @j=0
while @j<@i
begin
exec spAdder @s1, @s2, @s output
set @s1=@s
set @j=@j+1
end
set @s2=@s1
set @i=@i+1
end
select @s

93326215443944152681699238856266700490715968264381621468592963
89521759999322991560894146397615651828625369792082722375825118
5210916864000000000000000000000000

mkbosmans
Starting Member

15 Posts

Posted - 2004-01-13 : 05:29:12

quote:
Originally posted by Stoad

BTW, can anybody check for how fast it's calculated in
something like MathCad (never used it) or in something of this kind?

I checked the numbers in Maple 8 and they seem to be right (didn't check ALL the digits)

Maple is a mathematical program and it calculates 500! in less then 1 ms. 10000! in 120 ms and the really, really large number 100000! (456574 digits!) in 5.6 seconds.

I don't think any SQL solution can beat this.
(but off course Maple can't do a cross join!)

nr
SQLTeam MVY

12543 Posts

Posted - 2004-01-13 : 05:29:25

What my code does is multiplies by each integer in turn.
If the result is greater than 99999 it moves the leftmost digits to the next integer variable up.

e.g. 123456789
would become
1234 56789

For each muliplication it multiplies all variables and performs the digit move on all.

At the end it converts all variables to character and concatenates them.
So without the dynamic sql it does the following.
You could just keep adding variables to get the required result - that's what the dynamic sql does.

declare @i int
declare @t1 int ,
@t2 int ,
@t3 int
select @t1 = 1, @t2 = 0, @t3 = 0
select @i = 0
while @i < 20
begin
select @i = @i + 1

select @t1 = @t1 * @i
select @t2 = @t2 * @i
select @t3 = @t3 * @i
if @t1>power(10,5)
begin
select @t2 = @t2 + @t1 / power(10,5), @t1 = @t1 % convert(int,power(10,5))
end
if @t2>power(10,5)
begin
select @t3 = @t3 + @t2 / power(10,5), @t2 = @t2 % convert(int,power(10,5))
end
end
select right('0000' + convert(varchar(20),@t3),5)
+ right('0000' + convert(varchar(20),@t2),5)
+ right('0000' + convert(varchar(20),@t1),5)

902008176640000

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Stoad
Freaky Yak Linguist

1983 Posts

Posted - 2004-01-13 : 13:55:02

mkbosmans,
many thanks for the info. Very exciting. Though I think the Maple
just stores these google numbers in ready-to-use state or ... your
machine is one of Cray family.

nr--
Thanks, Nigel. I just was going to ask you for such simplified description.
Worth learning. BTW, you mentioned nested dynamic-sql. Sounds cool.
Never used it.

mkbosmans
Starting Member

15 Posts

Posted - 2004-01-20 : 04:30:51

It is calculated directly, so no stored numbers here. But remember: this program is specifically programmed for these kind of tasks.
Anyway, I think that programming this algorithm in C would be about as fast.
It's just that T-SQL was made to work with sets in stead of numbers and thus has a lousy performance on calculating factorials.

Stoad
Freaky Yak Linguist

1983 Posts

Posted - 2004-01-20 : 06:52:39

Mostly accordatto.

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2004-01-21 : 10:23:53

hey -- I'm a little late, didn't get a chance to test my theory, but here's a solution for you to look at.

and it does things SQL-style -- each digit is a row in a table of digits, and there is a big "set-based" update command in the heart of it. unfortunately, the update must be issued 1 time per number -- 500 times. But it still is pretty short and quick.

EDIT: Even faster with a table variable -- took 4 seconds for 500!. But you don't need a table variable, there's no requirement for one. you can replace it with a usual table.


declare @Digits table (Position int primary key clustered, Value int)
declare @i int;
declare @Carry int;
declare @C2 int;

-- fill the Digits table will zeroes:

set nocount on
set @i = 1
while @i <1150
  begin
  insert into @Digits (position, value) values (@i,0)
  set @i = @i + 1
  end
set nocount off

-- start at 1
update @digits set value=1 where position = 1

-- Start calculating the results:

set nocount on
set @i = 1
while @i<= 500
  begin
  set @Carry = 0
  set @C2 = 0
  update @Digits 
  set Value = (Value * @i + @C2) % 10, @C2=@Carry,
      @Carry= ((Value * @i + @C2)/10)
  set @i= @i +1
  end
set nocount off

-- display the results:

declare @c varchar(8000)
set @c= ''
select @c=@c + convert(varchar(1),Value) from @Digits order by position desc

print @c

Try it out !

- Jeff

Stoad
Freaky Yak Linguist

1983 Posts

Posted - 2004-01-21 : 13:58:38

oh, Dr. Jeff, I tried it (w/o the table variable)!! Neat and fast!
Though nr's code 12.5 times faster (on the same machine and
under the same environment conditions.

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources