Generate sequential alphanumeric IDs

Question

Hi
I am having a requirement in my project to generate sequential IDs i.e staring from A1001, A1002 and after 10,000 it should start from B1001, B1002,...B10000 ,C1001.. and so on.. I am fetching the max ID from my Database Table by using below SQL

  SELECT TOP 1 [ID_COLUMN] 
  FROM [NAME_OF_TABLE] 
  ORDER BY [ID_COLUMN] DESC

And then have to generate 3 new sequential IDs from the code behind of my ASPX page and then saving them to Table again. I looked through the different possibilities but din't get what exactly wanted. I tried the link below >Auto Generate alphanumeric Unique Id with C# and many more.. but unable to solve problem.

It does look simple enough to implement. I have done similar things. You need to devote an hour or so for the algorithm and the code. What is exactly the difficulty you have run to? — ThunderGr, Feb 27 '14 at 14:16
@ThunderGr Umm...This is such a bad idea. At high loading you will be creating soooo many key collisions. Unless you can architect a single application server, with some kind of thread-safe key generator singleton. — Aron, Feb 27 '14 at 14:52
@ThunderGr overall you will find that your database write perf will be limited by latency. Which means you could end up with ~10 DB IOP/s. — Aron, Feb 27 '14 at 16:11
@Aron Sorry, I am at loss here. You are telling me that something is a bad idea, but I have not proposed something solid. As everybody knows, all auto fields in a DB can be set to specific pattern and the DBMS handles the generation. Obviously, if you want to generate IDs for database items using c#, you do not generate primary keys. — ThunderGr, Feb 27 '14 at 16:17
@ThunderGr You are right. You didn't propose anything concrete. But generating SEQUENTIAL keys from the client involves creating a transaction across the database and all possible clients. To maintain ACID, you would need to place a lock on the database resources, whilst you generate the next key. That process is inherently latency bound. — Aron, Feb 27 '14 at 16:23
@Aron It could be so but as I said, if you are not generating primary keys, you do not have to lock anything. In addition, it is obvious that only one client would generate the keys, not all clients concurrently. You would just get the records, generate the keys and apply the changes. Clean, quick and free of problems. The sequence described in the question is hairy, to say the least, since it implies that, somehow, the C# program is called each time a new record is added, which it is rather unlikely to be practical for any real world application. — ThunderGr, Feb 28 '14 at 07:41
@ThunderGr that would not be guaranteed sequential. The problem is that during the time between "just get the records" and "apply the changes" another client/thread could come in and "just get the records". There by creating a race condition. I never said anything about locking on primary keys either. The problem is a generic problem on contention on a resource (the next id). — Aron, Feb 28 '14 at 07:45
@Aron I think I did specify that *only one client would do that job*. So, no race conditions whatsoever. — ThunderGr, Feb 28 '14 at 16:39

EventHorizon · Answer 1 · 2014-02-27T14:45:49.730

If you retrieve the current value as a string you'll have access to a Substring method to extract the numeric portion. Then you can use int.Parse to convert the numeric portion to a value before incrementing and checking its new value.

To extract the numeric value and character prefix assuming the current max value is stored in "id":

int value = int.Parse(id.Substring(1)); // 1000, 1001 ...
char prefix = id[0]; // A,B ...

To increase the prefix from A to B etc you can use this code:

prefix = (char)(prefix + 1); // A -> B ...

Just ensure you check for "Z" before increasing the value!

To assemble the new id you can use:

string newid = prefix + value.ToString("0000");

Things you need to consider:

Handle empty (null / DBNull.Value) value returned from an empty table (replace it with A1001)
What happens when you pass Z10000?
Reading the current max value, generating three new values and writing them back should be done in one database transaction (search for TransactionScope), otherwise you will have problems if the website traffic increases since several requests will try to generate new values simultaneously.

I hope this helps!

Yeah. No. TransactionScope isn't going to help you with this. You will still get race conditions (I know I've tried, because my manager at the time insisted I create a Message Queue in an RDBMS). — Aron, Feb 27 '14 at 14:49
You wont't get a race condition if you place a lock on the row containing the current highest value, but you'll definitely get performance problems on a busy website. You can also use optimistic locking but that's outside the scope of this answer (for more information see http://stackoverflow.com/questions/129329/optimistic-vs-pessimistic-locking) — EventHorizon, Feb 27 '14 at 14:57

Generate sequential alphanumeric IDs

1 Answers1