Creating a unique id (pin number) for each record of a table

Question

I want to create a pin number that is unique within a table but not incremental to make it harder for people to guess. Ideally I'd like to be able to create this within SQL Server but I can do it via ASP.Net if needed.

Thanks, Josh

EDIT

Sorry if I wasn't clear - I'm not looking for a GUID as all I need is a unique id for that table - I just don't want it to be incremental

Answer 1

What you want is a GUID

http://en.wikipedia.org/wiki/Globally_unique_identifier

Most languages have some sort of API for generating this... a google search will help ;)

Answer 2

How about a UNIQUEIDENTIFIER type column with a default value of NEWID()?

That will generate a new GUID for each row.

Answer 3

Add a uniqueidentifier column to your table, with a default value of NEWID(). This will ensure that each column gets a new unique identifier, which is not incremental.

CREATE TABLE MyTable (
   ...
   PIN uniqueidentifier NOT NULL DEFAULT newid()
   ...
)

The uniqueidentifier is guaranteed to be unique, not just for this table, but for all tables.

If it's too large for your application, you can derive a smaller PIN from this number, you can do this like:

   SELECT RIGHT(REPLACE((SELECT PIN from MyTable WHERE UserID=...), '-', ''), 4/*PinLength*/)

Note that the returned smaller PIN is not guaranteed to be unique for all users, but may be more manageable, depending upon your application.

EDIT: If you want a small PIN, with guaranteed uniqueness, the tricky part is that you need to know at least the maximum number of users, in order to choose the appropriate size of the pin. As the number of users increases, the chances of a PIN collision increases. This is similar to the Coupon Collector's problem, and approaches n log n complexity, which will cause very slow inserts (insert time proportional to the number of existing elements, so inserting M items then becomes O(N^2)). The simplest way to avoid this is to use a large unique ID, and select only a portion of that for your PIN, assuming that you can forgo uniqueness of PIN values.

EDIT2:

If you have a table definition like this

CREATE TABLE YourTable (
    [id] [int] IDENTITY(1,1) NOT NULL,
    [pin]  AS (CONVERT(varchar(9),id,0)+RIGHT(pinseed,3)) PERSISTED,
    [pinseed] [uniqueidentifier] NOT NULL
)

This will create the pin from the pinseed a unique ID and the row id. (RAND does not work - since SQL server will use the same value to initialize multiple rows, this is not the case with NEWID())

Just so that it is said, I advise that you do not consider this in any way secure. You should consider it always possible that another user could guess someone else's PIN, unless you somehow limit the number of allowed guesses (e.g. stop accepting requests after 3 attempts, similar to a bank witholding your card after 3 incorrect PIN entries.)

Answer 4

Please have in mind that by requiring an unique PIN (which is uncommon) you will be limiting the max number of allowed users to the the PIN specification. Are you sure you want this ?

A not very elegant solution but which works is to use an UNIQUE field, and then loop attempting to insert a random generated PIN until the insert is successful.

Answer 5

You can use the following to generate a BIGINT, or other datatype.

SELECT CAST(ABS(CHECKSUM(NEWID()))%2000000000+1 as BIGINT) as [PIN]

This creates a number between 1 and 2 billion. You will simulate some level of randomness since it's derived from the NEWID function. You can also format the result as you wish.

This doesn't guarantee uniqueness. I suggest that you use a unique constraint on the PIN column. And, your code that creates the new PIN should check that the new value is unique before it assigns the value.

Answer 6

Use a random number.

SET @uid = ROUND(RAND() * 100000)

The more sparse your values are in the table, the better this works. If the number of assigned values gets large is relationship to the number of available values, it does not work as well.

Once the number is generated you have a couple of options. 1) INSERT the value inside of a retry loop. If you get a dupe error, regenerate the value (or try the value +/-1) and try again. 2) Generate the value and look for the MAX and MIN existing unique identifiers.

DECLARE
    @uid    INTEGER
SET @uid = ROUND(RAND() * 10000, 1)
SELECT @uid
SELECT MAX(uid) FROM table1 WHERE uid < @uid
SELECT MIN(uid) FROM table1 WHERE uid > @uid

The MIN and MAX value give you a range of available values to work from if the random value is already assigned.