It should be a separate column and concatenated into the computed column that is displayed to the user.

I think thats the most important post in this discussion. I think one of the worst things you can do to a relational DB is to store MORE THEN ONE Information in a field. And if you "force" the field to become a wrong datatype it will even get worse.

Take the Year-Number example...

 SET @tmp_invoice_id = (SELECT MAX(SUBSTRING(invoice_id, 4, 5) + 1)

                        FROM Invoice WHERE (invoice_id LIKE @tmp_date + '%'))

To find all "Year entries" you can utilize an Index with "like '06%' after that you are using a function to find the maximum value of ALL Invoices for that year. That means in a "worst case" you have to do 99.999 times Substring ?

I think the overhead when creating the sequential Invoices can be negelected if you are using a sequence table... Also here is a small suggestion for the self updating of the sequence table. If I am faced with a the same problem, then i usually do it the "lazy way" Update the row i need to update, and if I get a rowcount of "0" then I know that i need to actually insert the row. Updating 0 Rows has no negative performace effect on SQL Server (Assuming correct indexes) and if I first check if the row exists, and then update, then i need to access the index 2 times... Once for checking and once for updating... So I can skip the 2nd Access to the index most of the times. And the Insert will automatically fierd when its needed.

But when the reporting season starts this (string) system will hurt you. It is not possible to generate a "good" index on these fields. and when you start running querries this will start to hurt you. It might only start with an concattinated Invoice Number... but in a current  application i am working with this started to run rampart... They needed to change the Datatype from Varchar to Binary to store all the "extra" information (Yes I tried to convice them that this is stupid... trust me I tried.. I am also looking for a Job now where ppl actually listen)

P.s: The suggested solution can still be valid if the number of Invoices remains small. You allways have to think about which compromise you can make...