Tip: Store large Integers as Decimal Data Type

"Q. I need to store integers - account numbers and other IDs - that have 20 or more digits. Storage and retrieval of these integers needs to be accurate, with no rounding, so I can't use the float data type. I've seen recommendations to others in this situation to store the integers in a CHAR(20) field. But I don't want to store my number in a character field because I want them to remain numeric values. How can I store integers that have 20 or more digits as numeric values?
A. I suggest using the decimal or numeric data type to store the integers. In SQL Server, the numeric and decimal data types are equivalent. Both data types let you store integers that have up to 38 digits. The syntax for defining a decimal data type is: DECIMAL[(p[, s])] The first number after the DECIMAL keyword is the precision setting, which defines the total number of digits the integer can have. The second number is the scale setting, which defines the total number of digits to the right of the decimal place..."

More info at http://www.winnetmag.com/SQLServer/Article/ArticleID/43114/43114.html

"..the decimal option requires only 13 bytes of storage, whereas the character option requires 20 bytes."
The largest number that can be stored naturally as a Integer is 9223372036854775807 (bigint), the total digits being 19

Comments