Math in SQL Server – Calculating the Value of e

This is also called the natural logarithm, which is approximately equal to to 2.718281828459…

I like numbers: many numbers, huge numbers and extreme numbers.

For me, BIGINT is not enough! I have to break this unacceptable SQL limit.

But how?

While the highest number BIGINT can hold is 2^63, which is equal to about 10^19, I want to see numbers with 10^1000 digits, even more — what do you think about 10^4250??

I could suggest storing them as string variables, which is limited in SQL these days to 8000 digits. Nice!

Ooooh, I could get 9.9999 * 10^7999.

Now we need a set of functions to add, subtract, multiply, divide, and do other mathematical operations, but with strings.

So I come to this:

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=53268

By the way, 34! is the biggest number that BIGINT can hold.

Can you store more?  Yes, of course you can, just use the text or VARCHAR(MAX) data types. Up to 2G digits!!! This is beginning to be more exciting. This is all great for Numerical Fetish People, or NFPs  🙂

Now, let’s go back and try to calculate the value of e, but very precisely, as required in scientific fields.

There are many ways to calculate it. One of these is the sum of the N first terms in a geometrical progression.

The serial includes the following formula

e = 1/0! + 1/1! + 1/2! + …+ 1/N! = 2.71828….

It is easy to store data in a database; this is the main advantage of SQL Server. So I use it to hold and calculate this infinite geometrical serial. The following steps describe how to control the accuracy of successive terms and save them.

Step 1

Transact-SQL

CREATE TABLE dbo.FactorialHuge ( N BIGINT NOT NULL, [N!] VARCHAR(4000) NULL, [InverseN!] VARCHAR(4000) NULL )

1

2

3

4

5

CREATE TABLE dbo.FactorialHuge (

N BIGINT NOT NULL,

[N!] VARCHAR(4000) NULL,

[InverseN!] VARCHAR(4000) NULL

)

N is run numbers, beginning with 0 to as high as possible. The accuracy depends on the highest value of N.

It is important to begin with 0, because 0! = 1.

N! is the N factorial.

Note that the value of 34! touches the BIGINT limit. 35! can’t be contained in the BIGINT data type.

InverseN is 1 / N!.

We need to summarize this column to get the value of e.

The longer the serial will be, the more accurate the value of e.

Step 2

Transact-SQL

INSERT INTO dbo.FactorialHuge( N ) SELECT top 1000 Number FROM [dbo].[Numbers] ;

1

2

3

4

INSERT INTO dbo.FactorialHuge( N )

SELECT top 1000 Number

FROM [dbo].[Numbers]

;

Let’s fill the N column with running numbers from 0 to 1000.

Step 3

Transact-SQL

ALTER TABLE dbo.FactorialHuge ADD CONSTRAINT PK_FactorialHuge PRIMARY KEY CLUSTERED (N ASC)

1

2

ALTER TABLE dbo.FactorialHuge ADD CONSTRAINT PK_FactorialHuge

PRIMARY KEY CLUSTERED (N ASC)

We need this clustered index to have the physical order of the calculation process.

Step 4

Transact-SQL

DECLARE @Factorial VARCHAR(4000) ;WITH FactorTab AS ( SELECT TOP 100 PERCENT [N], [N!] FROM [dbo].[FactorialHuge] ORDER BY [N] ASC ) UPDATE FactorTab SET @Factorial = [N!] = CASE WHEN N > 0 THEN [dbo].[MulInt]( @Factorial, CONVERT(VARCHAR(1000), N) ) ELSE '1' END OPTION (MAXDOP 1) ; ;

1

2

3

4

5

6

7

8

9

10

11

12

13

DECLARE @Factorial VARCHAR(4000)

;WITH FactorTab AS

(

SELECT TOP 100 PERCENT [N], [N!]

FROM [dbo].[FactorialHuge]

ORDER BY [N] ASC

)

UPDATE FactorTab

SET @Factorial = [N!] = CASE WHEN N > 0 THEN [dbo].[MulInt]( @Factorial, CONVERT(VARCHAR(1000), N) )

ELSE '1' END

OPTION (MAXDOP 1)

;

;

Step 5

Transact-SQL

update dbo.FactorialHuge set [InverseN!] = [dbo].[DivFraction]( '1', [N!], '100' )

1

2

update dbo.FactorialHuge

set [InverseN!] = [dbo].[DivFraction]( '1', [N!], '100' )

Here it calculates the inverse of N!.

The ‘100’ is the precision after the decimal point. These 100 digits will take about 2-3 minutes of processing.

I try 1000, and that takes 30 minutes.

Step 6

Transact-SQL

declare @e varchar(2000) = '0' select @e = [dbo].[AddFraction]( @e, [InverseN!] ) from dbo.FactorialHuge select @e as 'Euiler Numer'

1

2

3

4

5

declare @e varchar(2000) = '0'

select @e = [dbo].[AddFraction]( @e, [InverseN!] )

from dbo.FactorialHuge

select @e as 'Euiler Numer'

This is the final step. We need to summarize the InverseN column. The known function sum() is not accepted here.

The function ‘AddFraction’ come to replace the regular sum. It retrieves two strings with a floating point and returns the result as a string.

Here it is with serial of 1000 terms and precision 100 digits.

2.7182818284590452353602874713526624977572470936999595749669676277240766303535475945713821785251664274

For your convenience, the calculation code with all the functions is attached:

Math in SQL Server – Calculating the value of e

Enjoy!

#tsql #Math