Storing a really large number in SQL Server

Question

How would I store a large number like 92233720368547758079223372036854775807922337203699 in SQL Server 2008? Max bigint allows is 9223372036854775807

I guess one approach I could take is to do following, store the number as varchar(50) and in C# code I could do is

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

Would appreciate any feedback. Thanks

Answer 1

A pertinent question to ask yourself in this kind of situation, and one I asked of you in a comment, is the following:

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

And you responded

From what I understand is, the number is issued from an Oracle system from some governmental agency as a unique identifier, it can be as long as 0 - 50 number in length and we need to store that in our application

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

If it's just an identifier, then you really don't need to store or treat it as numeric. Strings composed of digits, such as Social Security Numbers in the United States or account numbers on credit cards and loans are not really numbers, from a data standpoint. They are identifier strings. Store them that way.

Answer 2

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

---

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

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

There might be a performance or space benefit in going the binary route, but it also may restrict other applications that need to use the value. IMHO though, unless you are going to be parsing many of these per second, I doubt it is truly worth the hassle - and storage is cheap these days.

Additionally, strings seem to be the de facto serialization method these days (e.g. XML or JSON), and storing in a varchar will also allow you to utilize SQL Server's XML features straight out of the box.

However, with these options you will need to do your arithmetic and sorting outside of the database. If this is data that needs to be sorted/filtered/manipulated, you may need to rethink the strategy. (See @MikeB's comment)

To get around the natural sort order problem (you still won't be able to do arithmetic) you could zero-pad the strings out to a fixed length. My suggestion is to use a persisted computed column to pad out the zeroes. If you persist the column data you can also put an index on it; just make sure to sort on the padded column when you do sort. SQL Fiddle demo

Or, a final option would be to specifically create a CLR type to wrap BigInteger, so you can use it directly in SQL Server (i.e. for comparisons and arithmetic). Again, not sure if it is worth the hassle, but if you need to do arithmetic in bulk this may be the fastest way.

Answer 3

Your approach of storing the number as a string is the safest approach. SQL is not designed to handle unlimited precision numbers. The DECIMAL, for instance, is limited in size to just a few dozen significant digits.

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