1

I want to an perform arithmetic operation like:

select 99999999999999999999999999999999999999 * 256

However, this results in the error

Arithmetic overflow error converting expression to data type numeric.

How do I perform an arithmetic operation that will return a value with a precision greater than 38?

Thom A
  • 88,727
  • 11
  • 45
  • 75
Sp007
  • 59
  • 1
  • 1
  • 10
  • 1
    https://stackoverflow.com/questions/3686265s5/arithmetic-overflow-error-converting-expression-to-data-type-numeric read this – gsa Jul 23 '18 at 11:47
  • 1
    CAST to Float data type – Stanislav Kundii Jul 23 '18 at 11:47
  • `select CAST(99999999999999999999999999999999999999 AS FLOAT) * CAST(256 AS FLOAT)` – M.Ali Jul 23 '18 at 11:49
  • 1
    The largest number you can store in SQL Server is `99999999999999999999999999999999999999` with a `decimal(38,0)` or `9223372036854775807` using `bigint`. If you need to work with larger numbers than that, you either need to be prepared to lose (a significant amount of) accuracy (by using `float`) or look at a different application that is happy to handle much larger values. – Thom A Jul 23 '18 at 11:50
  • Point in case: `STR(99999999999999999999999999999999999999 * 256e, 50, 0)` is `25599999999999999000000000000000000000000`, demonstrating the loss of accuracy when switching to binary floating-point. – Jeroen Mostert Jul 23 '18 at 11:57
  • But it does not work for data more than 38 digit. – Sp007 Jul 23 '18 at 12:19
  • You could wrap .NET's `BigInteger` type, which implements an arbitrary precision integer, using SQLCLR. This is not trivial, though. Consider if it's an absolute requirement that the arithmetic itself is done in SQL Server -- storing the digits themselves can be done using a `VARCHAR`. – Jeroen Mostert Jul 23 '18 at 12:40
  • You have to start using a `varchar` for the literal value if you have a precision higher than 38. `SELECT CONVERT(float,9999999999999999999999999999999999999999);` will fail, however `SELECT CONVERT(float,'9999999999999999999999999999999999999999');` will be fine. The reason the former fails is because the value is too large for a `decimal` and thus overflows. – Thom A Jul 23 '18 at 12:44
  • It is working fine . Thanks – Sp007 Jul 23 '18 at 13:05
  • But it is giving exponential value not numeric – Sp007 Jul 23 '18 at 13:17
  • That's the result of the default formatting of floating-point values. Use `STR` with appropriate length parameters or `FORMAT` to format values if you don't want exponential notation. (`SELECT FORMAT(CONVERT(float,'9999999999999999999999999999999999999999'), 'F0')`). This will still expose the loss of accuracy, though -- `9999999999999999999999999999999999999999` cannot be represented exactly in binary floating-point. – Jeroen Mostert Jul 23 '18 at 13:32

1 Answers1

1

You can use float - but read this excellent answer first:

Difference between numeric, float and decimal in SQL Server

 declare @x float = 99999999999999999999999999999999999999;

 declare @y float = 256;

 select @y*@x

otherwise decimal(38,0) is the only one to hold 9999.....

declare @x decimal(38,0) = 99999999999999999999999999999999999999;
SqlKindaGuy
  • 3,501
  • 2
  • 12
  • 29
  • 2
    But if they actually need a *precision* beyond 38, `float` doesn't help since it's only got an effective precision of ~15 decimal digits. – Damien_The_Unbeliever Jul 23 '18 at 12:02
  • 1
    Agreed, however, if they need a precision higher than 38 digits, then the OP needs to use a different application, as it's beyond SQL Server's ability. – Thom A Jul 23 '18 at 12:06
  • is it not possible in sql server ? – Sp007 Jul 23 '18 at 12:20
  • *"Fixed precision and scale numbers. When maximum precision is used, valid values are from - 10^38 +1 through 10^38 - 1. The ISO synonyms for decimal are dec and dec(p, s). numeric is functionally equivalent to decimal."* - [decimal and numeric (Transact-SQL)](https://learn.microsoft.com/en-us/sql/t-sql/data-types/decimal-and-numeric-transact-sql?view=sql-server-2017) – Thom A Jul 23 '18 at 12:24