| Downloads |
|---|
| 43114.zip |
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 numbers 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?
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. (Learn more from "Portable Data Types" and "Data Types Revealed").
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. For example, in the statement
DECLARE @MyLargeNumber DECIMAL(20, 0)
@MyLargeNumber is an integer that has 20 digits because the scale is equal to 0.
You have no reason to use CHAR(20) instead of DECIMAL(20, 0). For example, Listing 1 shows how to store a 20-digit number as both a decimal data type and a character data type. The T-SQL script's results show that the decimal option requires only 13 bytes of storage, whereas the character option requires 20 bytes. The SQL Server Books Online (BOL) topic "Decimal and Numeric" explains how many bytes a particular decimal value will need for internal storage based on the number's precision. Some part-time SQL Server professionals forget that they can work with simple integers as decimal values as long as they set the scale to 0.
