Creating a Non-Int and Non-Guid Unique Identifier

Question

I'm looking for a way SQL Server can generate a unique identifier that is not an increment Int or a GUID.

The Unique ID can be a combination of letters and numbers and has no other characters, and as previously mentioned Must be Unique.

ie AS93K239DFAK

And if possible must always start with AS or end with an K

It would be nice if this unique id can be generated automatically when there is an Insert like GUIDs and IsIdentity = Yes does. It can be a random number, it is not predetermined in the app.

Is doing something like this possible, or does it have to be generated application-side?

Answer 1

How are you inserting these new invoices to the table? A straight up batch insert or are you doing some business logic/integrity checks in a stored procedure first and 'creating' the invoices one by one?

In the second case, you could easily build a unique ID in the procedure. You could store a seed number in a table and take the number from there then cast it as a varchar and append the alphanumeric characters, you can then increment the seed. This also gives you the option of creating a gap between unique IDs if you needed to import some records into the gap at a later date.

Answer 2

What is so confusing about the random part being unique? If you have a two digit invoice number there can only be 100 unique values (00 - 99). A GUID has 2 to the power 128 values and is statistically unique. If you use a 8 characters of a GUID then with even 1 million invoices you have a betting chance of getting a collision. With 1 million invoices if you use 12 characters of GUID then you have very good chance of NOT getting a collision. If you use 16 characters of a GUID then you are pretty much statistically unique if you have less than 1 billion invoices. I would use 12 characters but check against actual values for uniqueness and you only have lottery chance of getting collision.

Answer 3

I've never seen a randomly generated invoice number. Most of them are usually a combination of multiple identifying fields. For example, one segment might be the companyID, another might be the InvoieID, and a third might be a Date value

For example, AS-0001-00005-K or AS-001-00005-021712-K, which would stand for CompanyId 1, Invoice #5, generated on 2/17/12

You said in a comment that you don't want to let the company know a count of how many past invoices there are, and this way they won't know the count except for how many invoices they have received, which is a value they should know anyways.

If you're concerned about giving away how many companies there are, use an alpha company code instead, so your end result looks like AS-R07S-00005-K or ASR07S00005K

Answer 4

So you can do it this way, just don't expect it to perform well.

(1) populate a big massive table with some exhaustive set of invoice values - which should be at least double the number of invoices you think you'll ever need. Populate the data in random order in advance.

(2) create a stored procedure that pulls the next invoice off the pile, and then either deletes it or marks it as taken.

But, be sure that this solution makes sense for your business. In many countries it is actually law for invoice numbers to be sequential. I'm guessing we're not really talking about invoices, but wanted to make sure it's at least considered.

Answer 5

From comments, it sounds like you would be OK with using an IDENTITY field and padding it with 0s and adding a prefix/suffix. Something like this should work:

1 - Add an IDENTITY field which will be auto-incremented
2 - Add a calculated field in the table with the definition of:

[InvoiceNo] AS ('AS' + RIGHT(('000000000' + CAST(idfield AS varchar(9))), 9) + 'FAK')

This will give you invoiceno in the format of:

AS000000001FAK
AS000000002FAK
...
AS000995481FAK