SQL Server - Prefix and zero padding ID column

Question

To be quick, here's my problem, I have been trying to search StackOverflow for the answer but I can't find a satisfactory answer.

I have an InvoiceHeader table with columns (simplified) :

InvoiceID   : Int - PK Identity(1,1)
InvoiceDate : date
CustomerID  : Int - FK to Customer table

Etc...

I will frequently display information about Invoice to users. When I display the invoice number to the users, I need to add prefix and zero padding the InvoiceID.

For example :

InvoiceID : 1 
Invoice Number : INV0000001

My question is, should I make a BRAND NEW PERSISTED COMPUTED column in the InvoiceHeader table to save the formatted invoice number like this :

InvoiceNumber AS 'INV' + RIGHT('000000'+cast(InvoiceID as varchar(7)),7)

or should I select the InvoiceID and process it into INV0000001 at runtime from my application?

My dilemma :

• If I add a new PERSISTED COMPUTED column (i.e InvoiceNumber) then I can make a Non Clustered Index at InvoiceNumber which will help when there is query with InvoiceNumber in WHERE clause. But this will still need covering index or include index to get other column's value. Also another plus is I don't have to "format" it to become INV0000001 everytime I need to display it.
• If I format InvoiceID to become INV0000001 at SELECT QUERY from my app, it will be too much "chore", everytime I want to display InvoiceNumber I have to format it. But i won't need another column which basically have the same value with InvoiceID with some prefix and zero padding.
• Invoice number will be used by users for searching, for example : find me an invoice with number like '1234'+'%'. If this is done to InvoiceID (integer column) it will make an IMPLICIT conversion, will it make the query slower? Will the PK index still be used by query plan?

---

EDIT : Thinking about lots of places where I will have to manually format Invoice Number everytime I need it (if I don't use computed column), I'm now almost confinced to go with Esperento57's solution :

Make 2 new column :

InvoiceID      : int identity (PK)
Prefix         : char(3) --> INV, etc
InvoiceNumber  : Prefix + RIGHT('000000'+cast(InvoiceID as varchar(7)),7)

But I won't make PK include Prefix because InvoiceID is identity so it's unique by itself.

I'm also still considering @Matt's point about separation of concerns too, because it makses sense.

Answer 1

My proposition for your columns :

• InvoiceID : integer not null with autoincrement (sequence are not necessary, SQL Server do the job)

• Prefix: varchar(10) not null FK on PREFIXTABLE

• InvoiceID and Prefix are the primary key

• InvoiceNumber : computed and persisted column = Prefix + RIGHT('0000000'+ InvoiceID,7)

• Add an index on InvoiceNumber

With my proposition you can :

1. Change prefix if necessary
2. You have integrity on prefix
3. You have integrity on your complet key and keep the really primary key
4. You have not to recalculate the complete key
5. In case of data recovery, you can make jumps of sequences to your keys or simply choose another prefix
6. You can add a description into your PREFIX table for explain your prefix (for futur developper by example)