by: jayanth.kurupPosted on:

Adding a Quintillion

An interesting problem was asked on #Sqlhelp recently about adding very large numbers, in excess of 20 digits in SSAS tabular model. While most of us will find that bigint or decimal datatype is large enough for anything we can imagine storing; it is still an interesting problem. Mostly because as the volume of data increases we are bound to get to a point where even 20 digits might seem insufficient. Until say recently trillions were still considered rare which the case is no longer.

So how do we approach the problem?

Let’s assume for now that the numbers individually are in the very large number scale and we encounter the issue only when we add them. Let’s assume we create a table with a bigint datatype as shown below

The solution here would be simply to change the datatype to decimal (38, 0)

But what If we encounter numbers bigger than this?

No problem because we can store up to 38 digits into the decimal datatype

Let’s go for an even larger number 137654213712812673161231237234125412123132

Now even the decimal datatype is insufficient, do we have another datatype capable of storing even larger numbers?

Unfortunately not, so we need to implement some sort of work around. We could store the data into a varchar(200) data type so that the number is represented as its actual value however at this point we lose all ability to perform math operations on the underlying datatype.

In this approach I have used the string datatype to break the number into smaller sets and then perform the operation on the smaller set first. Here is the result we are trying to verify.

First I have a table in which the numbers are being stored.

CREATE TABLE [dbo].[bignumbers] (
    [id] [int] IDENTITY(1, 1) NOT NULL
    ,[number] [varchar](100) NULL
    ) ON [PRIMARY]

Then I Insert some random numbers into the table

INSERT INTO bignumbers
SELECT '123456789012345678901234567890123456.123456789012345678901234567890123456'

Finally I run the below script to generate the SUM of the above numbers. I have written the code in a bit of a hurry so please don’t judge me for not making it pretty. Also I am sure it could be rewritten to be a bit more efficient. But I haven’t tried that yet maybe in another post.

The below code has been tested for up to decimal (73, 36), Please feel free to share your thought and let me know if you find an error.

DROP TABLE #results

DECLARE @SigDigits INT = (
        SELECT MAX(LEN(CAST(SUBSTRING(number, CHARINDEX('.', number) + 1, 100) AS DECIMAL(38, 0))))
        FROM bignumbers
        )
DECLARE @DVal AS VARCHAR(100)

SELECT @DVal = SUM(CAST(SUBSTRING(number, CHARINDEX('.', number) + 1, 100) + REPLICATE('0', @SigDigits - LEN(SUBSTRING(number, CHARINDEX('.', number) + 1, 100))) AS DECIMAL(38, 0)))
FROM bignumbers

DECLARE @Carryover INT = 0

IF LEN(@DVal) > @SigDigits
BEGIN
    SET @Carryover = left(@DVal, len(@DVal) - @SigDigits)
    SET @DVal = RIGHT(@DVal, @SigDigits)
END

DECLARE @Reaval AS DECIMAL(38, 0)
DECLARE @Realprecision AS INT

SELECT 'Little' = sum(cast(CASE 
                WHEN LEN(substring(number, 1, charindex('.', number) - 1)) >= 37
                    THEN RIGHT(substring(number, 1, charindex('.', number) - 1), 37)
                ELSE substring(number, 1, charindex('.', number) - 1)
                END AS DECIMAL(38, 0))) + @Carryover
    ,@DVal AS Decimals
INTO #results
FROM bignumbers

SELECT cast(Little AS VARCHAR(38)) + '.' + cast(Decimals AS VARCHAR(38)) AS Final
FROM #results

Proof is in the pudding.

Please Consider Subscribing

Subscribe