Wednesday, March 28, 2012

Robust unique identifiers?

Hi there ... after much research i still can't quite find the right
answer to this problem, so here goes.
I'm trying to find a simple, robust and scalable way to manage unique
identifiers in my database system. The system generates paper forms
containing the UIDs and sends them to customers, so maintaining
integrity of the numbers is critical.
The system needs to be non-DBMS-specific, needs to be able to run in
multiple instances simultaneously (although this is only a
hypothetical, later requirement) and needs to be as robust as possible
throughout DB restores, general maintenance and against rogue DBAs
resetting IDENTITYs and the like.
I've been looking at generating a unique key using a hashed combination
of the server ID and a low-level timestamp, which is fine except that
it generates enormous numbers. Customers need to be able to quote the
numbers easily to call centre staff, so I'm looking for something
ideally no more than about 12-16 digits. Is there a quality random
value generator that will give numbers as short as this? Or, is there a
suitably robust way of using SQL IDENTITYs to achieve what I need?
Many thanks for any help.
Pete.
Allocate the first digit (or more if you need) to identify the server
instance - 1 number per server. The rest can just be an incrementing count.
Won't that meet your requirements?
David Portas
SQL Server MVP
sql

No comments:

Post a Comment