How do I store a number with leading zeros?

Many line-of-business applications want to store document numbers with leading zeros, such as an Invoice Number, or Cheque Number.

The idea behind having the leading zeros is we’ve predefined the format for the document number in question; any number outside of those bounds is automatically invalid.

Edwin Lord Weeks - A Man Leading a Camel

Edwin Lord Weeks – A Man Leading a Camel

For example, we want our invoice numbers to always be 8 digits; the first one being 00000001 and the maximum invoice number being 99999999.

How do we store this data in our trusty SQL Server database without losing the leading zeros? Turns out, we shouldn’t store the data with the leading zeros since this can lead to performance and data-integrity issues. What issues, exactly, you say?

Well, let’s take a look.

The Setup

Keeping in line with our invoice number/cheque number example from above, the requirement is to create a table with a field (aka “column” in SQL Server parlance) where we can store an 8-digit number that always has 8 digits, such that whenever we look at the number we see a number like: 00005034

Imagine you have the following table, where we have an automatically incrementing number (InvoiceHeaderID) that uniquely identifies the invoice:

If we insert 4 rows into this table, and query the InvoiceHeaderID as in the following example, we see the invoice number does not have leading zeros.

Results:

This is fantastic for use as a foreign key in the InvoiceFooter table, and everywhere else we need to reference an Invoice number, however, it doesn’t fulfill our requirements for the number to always be displayed with a specific number of digits, including leading zeros if necessary.

Ads by Google, Paying the Rent:

How Not To Do It

We could instead decide it might be A Good Thing™ to store the InvoiceHeaderID in a VARCHAR(8) column instead of storing it as an integer. Now our CREATE TABLE statement looks like this:

We can no longer automatically generate the number using the IDENTITY(1,1) syntax, so we need to somehow generate that number sequentially, ensuring no two simultaneous requests will generate a duplicate number. We also need to ensure invalid “numbers” such as “abceasdf” will never be stored in the column. Everywhere we use the InvoiceHeaderID value as a reference, it will occupy 8 bytes of storage in memory and on-disk, instead of the 4 bytes required for an integer.

The Solution

We can resolve these problems by adding a virtual field, or “calculated column”, that provides the correct formatting for display while maintaining our integer number behind the scenes. Our “CREATE TABLE” statement would look something like:

This provides an excellent index on the InvoiceHeaderID, which can then be used in every table containing Invoice record references, and also allows us to provide the number for display in the required 8-digit-with-leading-zeros format.

If we query this table, we see the following results:

As you can see, this maintains both the 8-digit format the business desires, while keeping the efficient integer-based value.

Please Note!

In this example we are limiting the number of digits to 8, while we are using an IDENTITY(1,1) integer which offers a maximum value of 2147483647. This issue means if our system is expected to eventually generate more than 99,999,999 invoices, the calculated column InvoiceNumber will roll-over back to 00000000. If this is expected to be a concern, you may want to make the InvoiceNumber column consist of 10 digits instead of 8.

If you liked this post, please share it with your friends.

Take a look at the rest of our posts on SQL Server Basics.