Geek Answers Handbook

Storing really large quantities in SQL Server

How to store a large quantity, for example 92233720368547758079223372036854775807922337203699 in SQL Server 2008? Maximum bigint allows: 9223372036854775807

I guess one of the approaches I could do is to do the following, save the number as varchar(50) , and in C # code I could do this

 BigInteger x = BigInteger.Parse("922337203685477580792233720368547758079223372036");

Thank you for your feedback. Thanks

+4
source share
4 answers

The clever question to ask myself in such a situation, and the one I asked you in a comment, is this:

Is your really large number intended to be used as a number for arithmetic, or is it just some kind of identifier?

And you answered

From what I understand, the number is released from the Oracle system from some government agency as a unique identifier, it can be as long as 0-50 numbers in length, and we need to save this in our application

And that should lead you to a different answer than trying to save the data as numeric.

If it's just an identifier, you really don't need to store or treat it as a numeric one. Lines of numbers, such as United States Social Security numbers or credit card and credit account numbers, are not really numbers in terms of data. These are identifier strings. Store them that way.

+14
source

From your comment, if you are not doing any arithmetic (i.e. this is just an identifier), this is optional - and probably just shouldn't be - a number in the first place. Just consider it as a string both in the database and in the consumer application and move on.

However, for future visitors to the question who has a number, there are two options that I can think of:

  • Use varchar and BigInteger.Parse(string) / BigInteger.ToString()
  • Use varbinary and BigInteger..ctor(byte[]) / BigInteger.ToByteArray()

When migrating to a binary route, there may be an advantage in performance or space, but it may also limit other applications that should use the value. IMHO, although if you do not disassemble many of them per second, I doubt that it really is a hassle - and these days, storage is cheap.

In addition, strings are currently the actual serialization method (e.g. XML or JSON), and storing in varchar will also allow you to use SQL Server XML functions right out of the box.

The third option is to specifically create a CLR type to port BigInteger , so you can use it directly in SQL Server (i.e. for comparison and arithmetic). Again, not sure if it's worth the hassle though.

+4
source

Your approach to storing a number as a string is the safest approach. SQL is not designed to handle unlimited precision numbers. For example, DECIMAL is limited to just a few dozen significant digits.

Another alternative is to save the number as varbinar() (or binary() if you know the maximum size). This works, but you will store the number in the format used by the application, and not in the database. This may limit the portability of your application.

+1
source

Consider using the decimal data type. It can store up to 38 digits.

0
source

More articles:


All Articles