SharePoint 2010: Largest Number in SharePoint Numeric Column

English:

So what is the largest number in SharePoint numeric column?

Before we can answer this question, let take a look how SharePoint store the data in SQL table. By design, SharePoint will map specific column type in SharePoint into SQL data type; they are nvarchar, ntext, sqlvariant, int, float and bit. For example, true/false column will be mapped to bit and numeric column will be mapped to float.

There are two types of float data type in SQL, 7 digits or 15 digits precisions (http://msdn.microsoft.com/en-us/library/ms173773.aspx). And of course, Microsoft’s engineer will choose the larger precision 15 digits – using 8 bytes storage. (See table below)

_n_value

Precision

Storage size

1-24

7 digits

4 bytes

25-53

15 digits

8 bytes

n is the number of bits that are used to store the mantissa of the float number in scientific notation.

The _n_value represent the number of bits that are used to store the mantissa of the float number. Therefore the maximum value of the float number is 2^53 =  9.00719925 × 1015 .

Precision represent the length of significant digit to which a number can be represented. When a number contains digits more than max precision digits, then it will be rounded up/down to the precision digits. For example, 12345 will be seen as 1.2345 x 104 in 5 digits precisions or 1.235 x 104 in 4 digits precisions. Hence bigger precision is desirable to represent the exact number, otherwise it will only represents approximation value.

Since SQL Server specifies 15 digits precision for its floating point, then you must be-careful for any number with more than 15 digits. For example, the 999,999,999,999,999 is maximum exact value based on 15 digits precision. Any number above that, even with different as small as 0.1 will be an approximate value; for example 999,999,999,999,999.1 is same as 999,999,999,999,999 . And if you do arithmetic operation – then you may end-up with big floating error.

So what is the impact?

1. Storage Boundaries (2^53)
   This boundaries limits the maximum value which can be stored in a numeric column. You can’t save 9.007199256 x 1015 without rounding. And everything beyond this storage boundaries will be ignored and saved as 2^53 value.
2. Precision Boundaries (15 digits)
   This boundaries limit the number of digits before SQL starts to round-up/round-down. Remember floating data is approximate , and therefore not all value can be represented as is.  As you may guess, if you try to save 999,999,999,999,999.5 – you will end-up with 1,000,000,000,000,000 value.

The good news is, we have used SQL floating data type without any problem; so we put the same faith to SharePoint numeric column. However, if you want to use SharePoint or SQL table, please ensure that you have enough precision for your data.