What is the best data type to use in order to store numbers with leading zeroes?

Question

In Access 2003 I need to display numbers like this while keeping the leading zeroes:

• 080000
• 090000
• 070000

What is the best data type to use for this?

Answer 1

Use a string (or text, or varchar, or whatever string variant your particular RDBMS uses) and pad it with whatever character you want ("0") that you need.

Answer 2

Do you need to retain them as numbers within the table (i.e. do think you will need to do aggregations within queries - such as SUM etc)?

If not then a text/string datatype will suffice.

If you DO then perhaps you need 2 fields.

1. to store the number [i.e. 80000] and
2. to store some meta-data about how the value needs to be displayed
   • perhaps some sort of mask or formatting pattern [e.g. '000000'].

You can then use the above pattern string to format the display of the number

• if you're using a .NET language you can use System.String.Format() or System.Object.ToString()

• if you're using Access forms/reports then Access uses very similar string formatting patterns in it's UI controls.

Answer 3

If your real data looks like your examples and has a fixed number of digits, just store the data in a numeric field and use the format/input mask attributes of the column in Access table design display them with the padded zeros.

Unless you have a variable number of leading zeros there is no reason to store them and it is generally a bad idea. unecessarily using a text type can hurt performance, make it easier to introduce anomalous data, and make it harder to query the database.

Answer 4

Fixed width character with Unicode compression with a CHECK constraint to ensure exactly six numeric characters e.g. ANSI-92 Query Mode syntax:

CREATE TABLE IDs 
(
 ID CHAR(6) WITH COMPRESSION NOT NULL
    CONSTRAINT uq__IDs UNIQUE, 
 CONSTRAINT ID__must_be_ten_numeric_chars
    CHECK (ID ALIKE '[0-9][0-9][0-9][0-9][0-9][0-9]')
);

Answer 5

Key question:

Are the leading zeros meaningful data, or just formatting?

For instance, 07086 is my zip code, and the leading zero is meaningful, so US zip codes have to be stored as text.

Are the values '1', '01', '001' and '0001' considered to be unique, legal values or are they considered to be duplicates?

If the leading zero is not meaningful in your table, and is just there for formatting, then store the data as a number and format with leading zeros as needed for display purposes.

Also, number storage and indexing in all database engines I know of are more efficient than text storage and indexing, so with large data sets (100s of thousands of records and more), the performance drag of using text data type for numeric data can be quite large.

Last of all, if you need to do calculations on the data, you want them to be stored as numbers.

The key is to start from how the data is going to be used and choose your data type accordingly. One should worry about formatting only at presentation time (in forms and reports).

Appearance should never drive the choice of data types in the fields in your table.