DOWNLOAD THE CODE:
Download the Code 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. 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.

End of Article


You must log on before posting a comment.

If you don't have a username & password, please register now.

Reader Comments

In some circumstances, using CHAR(20) actually makes more sense. If leading zeros are considered to be part of the identifier, it may be easier to manipulate and display the data as character data than as a decimal. In the question answered, we are talking about "account numbers and other IDs", where the data has no inherent "numberness". That is to say, there is no more inherent numberness to my driver's license number than to my name - both are intended to serve as an identifier for me, and to the extent that they are unique (and mine is, trust me:), that is all that need be known about them. It would make no more sense to take an average of people's drivers license numbers any more than it would to take an average of their names.

These lines are, of course, somewhat fuzzy. While I would never store inherently numeric data, such as account balances (which in some currencies can be quite large), in a CHAR or VARCHAR field, I do quite frequently use numeric datatypes to store identifiers. But not always.

--Tobermory Ranulf Ovod-Everett (told you it was unique:)

tovod-everett- August 12, 2004

Article Rating 4 out of 5

I have to disagree with the comment that it is sometimes OK for storage of the account as char(20) just for the sake of formatting it for display. That the data has no inherent 'numberness' does not mean that it should not be stored as efficiently as possible. Display and formatting (manipulation) are more correctly handled in the Stored Procedures and/or Views that provide the gateway to the actual data.

Also, while one might not 'take an average of people's drivers license numbers', one may need to aggregate multiple account records, or search for account numbers by account number and the most efficient index to aid that type of operation would be on a numeric data type. Translation between the User view of the data and the stored data should be handled by the layer and be transparent to the User.

I think the article also reinforces to new developers the concept/discipline that data is data and reporting on or displaying data is handled in the abstraction/presentation layer. I have seen many new developers using crutches such as storing the data (whatever kind of data) formatted for display because they aren't aware of the implications or schooled in efficient design practices.

- Bill Rogers

WilRogJr- August 13, 2004

Article Rating 4 out of 5

I, on the other hand, completely disagree with Bill. Efficiency gains will be minimal compared with the formatting time for one of the most-accessed fields. Leading zeros are a big issue here.

But the real reason to leave it as character data is extensibility. Account numbers, through mergers and/or expansion, often generalize to include characters. If the account number is stored as a decimal, every procedure, program, and query would have to be updated and retested for the entire system if this were to occur. In such situations, there's enough work to do already. Plan for the future!

Cheers, Eric Filson

Anonymous User- March 03, 2005

Article Rating 4 out of 5