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
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:
|
1 2 3 4 5 6 7 8 9 10 11 12 |
CREATE TABLE dbo.InvoiceHeader ( InvoiceHeaderID INT NOT NULL CONSTRAINT PK_InvoiceHeader PRIMARY KEY CLUSTERED IDENTITY , InvoiceDate DATE NOT NULL CONSTRAINT DF_InvoiceHeader_InvoiceDate DEFAULT (GETDATE()) , CustomerNumber INT NOT NULL .... ); |
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.
|
1 2 |
SELECT InvoiceHeaderID FROM dbo.InvoiceHeader; |
Results:
|
1 2 3 4 5 6 |
InvoiceHeaderID --------------- 1 2 3 4 |
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.
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:
|
1 2 3 4 5 6 7 8 9 10 |
CREATE TABLE dbo.InvoiceHeader ( InvoiceHeaderID VARCHAR(8) NOT NULL CONSTRAINT PK_InvoiceHeader PRIMARY KEY CLUSTERED , InvoiceDate DATE NOT NULL CONSTRAINT DF_InvoiceHeader_InvoiceDate DEFAULT (GETDATE()) , CustomerNumber INT NOT NULL ); |
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:
|
1 2 3 4 5 6 7 8 9 10 11 12 |
CREATE TABLE dbo.InvoiceHeader ( InvoiceHeaderID INT NOT NULL CONSTRAINT PK_InvoiceHeader PRIMARY KEY CLUSTERED IDENTITY(1,1) , InvoiceNumber AS RIGHT('00000000' + CONVERT(VARCHAR(8), InvoiceHeaderID), 8) , InvoiceDate DATE NOT NULL CONSTRAINT DF_InvoiceHeader_InvoiceDate DEFAULT (GETDATE()) , CustomerNumber INT NOT NULL ); |
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:
|
1 2 3 |
InvoiceHeaderID InvoiceNumber InvoiceDate CustomerNumber --------------- ------------- ----------- -------------- 1 00000001 2016-02-04 1 |
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.
[…] a previous post, I talked about how to store a number with leading zeros. This morning I came across an interesting function that could be used in SQL Server 2012 and […]
DBA.StackExchange.com question on this topic: http://dba.stackexchange.com/a/128335/10832