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.
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:
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.
CREATE TABLE dbo.FactorialHuge ( N BIGINT NOT NULL, [N!] VARCHAR(4000) NULL, [InverseN!] VARCHAR(4000) NULL )
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.
INSERT INTO dbo.FactorialHuge( N ) SELECT top 1000 Number FROM [dbo].[Numbers] ;
INSERT INTO dbo.FactorialHuge( N )
SELECT top 1000 Number
Let’s fill the N column with running numbers from 0 to 1000.
ALTER TABLE dbo.FactorialHuge ADD CONSTRAINT PK_FactorialHuge PRIMARY KEY CLUSTERED (N ASC)
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.
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) ; ;