TSQL: Generate human readable ids

Question

We have a large database with enquiries, each enquirys is referenced using a Guid. The Guid isn't very customer friendly so we want to the additional 5 digit "human id" (ok as we'll very likely won't have more than 99999 enquirys active at any time, and it's ok if a humanuid reference multiple enquirys as they aren't used for anything important).

1) Is there any way to have a IDENTITY column reset to 1 after 99999?

My current workaround to this is to use a INT IDENTITY(1,1) NOT NULL column and when presenting a HumanId take HumanId % 100000.

2) Is there any way to automatically "randomly distribute" the ids over [0..99999] so that two enquirys created after each other don't get the adjacent ids? I guess I'm looking for a two-way one-to-one hash function??

... Ideally I'd like to create this using T-SQL automatically creating these id's when a enquiry is created.

Answer 1

You could have a table of available HUMANIDs, each time you add an enquiry you could randomly pull a HUMANID from the table (and DELETE it), and each time you delete the enquiry you could add it back (by INSERTing).

Answer 2

If performance and concurrency isn't too much of an issue, you can use triggers and the MAX() function to calculate a 'next human ID' value. You probably would want to keep your IDENTITY column as is, and have the 'human ID' in a separate column.

EDIT: On a side note, this sounds like a 'presentation layer' issue, which shouldn't be in your database. Your presentation layer of your application should have the code to worry about presenting a record in a human readable manner. Just a thought...

Answer 3

You can create composite primary key with two columns, say..BatchId and HumanId. Records in these columns will look like this:

BatchId, HumanId

1, 1

1, 2

1, 3

.

.

1, 99998

1, 99999

2, 1

2, 2

3, 3

use MAX or ORDER BY DESC to get next available HumanId with condition with BachId

SELECT TOP 1 @NextHumanId=HumanId
FROM [THAT_TABLE]
ORDER BY BatchId DESC, HumanID DESC

IF @NextHumanId>=99999 THEN SET @NextHumanId=1

Hope this help.

Answer 4

I would strongly recommend relooking at your logic. Your approach has a few dangers, including:

1. It is always a bad idea to re-use ID's, even if the original record has become "obsolete" - do you lose anything by continuing to grow ID's beyond 99999? The problem here is more likely to be with long term maintenance, especially if there is any danger of the system developing over time. Another thing to consider - is there any chance a user will take this reference number, and use it to reference your system at some stage in the future?

2. With manually assigning a generated / random ID, you will need to ensure that multiple records are not assigned the same ID. There are a few options that you have to follow this (for example, using transactions), however you should ensure that the scope of the transactions is not going to leave you open to problems with concurrent transactions being blocked - this may cause a few problems eg. Performance. You may be best served by generating your ID externally (as SQL does not do random especially well), and then enforcing a unique constraint on your DB, perhaps in the way suggested by Firoz Ansari.

If you still want to reset the identity column, this can be done with the DBCC CHECKIDENT command.

An example of generating random seeds in SQL server can be found here: http://weblogs.sqlteam.com/jeffs/archive/2004/11/22/2927.aspx

Answer 5

If you absolutely need to do this in the database, then why not derive your human-friendly value directly from the GUID column?

-- human_id doesn't have to be calculated when you retrieve the data
-- you could create a computed column on the table itself if you prefer
SELECT (CAST(your_guid_column AS BINARY(3)) % 100000) AS human_id
FROM your_table

This will give you a random-ish value between 0 and 99999, derived from the first 3 bytes of the GUID. If you want a larger, or smaller, range then adjust the divisor accordingly.