generate seemingly random unique numeric ID in SQL Server

Question

I need to use SQL Server to generate seemingly random unique 8-digit numeric ID (can pad zeros at front). Is there a built-in functionality for this? I saw this Identity property, but it is sequential, not random.

If this is not possible, is it good practice to directly write a randomly generated ID to db then check for exception? (note that my app is multi-threaded, so checking before writing doesn't guarantee uniqueness unless done in atomic action.)

Thanks!

UPDATE: Added "numeric" to clarify. Edited to show that the randomness doesn't need to be cryptographically strong or anything near. Just seemingly random is good enough. Oliver suggested an elegant solution, and I've posted an answer using that approach. Thanks, Oliver!

Answer 1

Randomness clashes with uniqueness, but there is an elegant solution suggested by @Oliver when the numbers only need to appear random, while an underlying order exists. From Erics' http://ericlippert.com/2013/11/14/a-practical-use-of-multiplicative-inverses/, the main idea is that for given a pair of coprime, positive integers x and m, we can find a multiplicative inverse y where (x*y) % m == 1. This is very useful because given a database row ID z, we can map z to another integer by doing encoded = (z*x) % m. Now given this encoded, how can we get z back? Simple, z = (encoded * y) % m since (x*y*z) % m == z given z < m. This one-to-one correspondence guarantees uniqueness of the "encoded" while provides an apparance of randomness.

Note that Eric showed how to calculate this multiplicative inverse. But if you are lazy, there is this.

In my implementation, I just store the sequential ID of each row as it is. Then, each ID is mapped to another number, something simlar to the "InvoiceNumber" in the article. When the customer hands you back this "InvoiceNumber", you can map it back to its original database ID by using multiplicative inverse.

Below is a C# example of encoding and decoding sequence from 0 to 9.

public static void SeeminglyRandomSequence()
{   //use long to prevent overflow
    long m = 10; //modulo, choose m to be much larger than number of rows
    long x = 7; //anything coprime to m
    long y = 3; //multiplicative inverse of x, where (y*x) % m == 1
    List<long> encodedSequence = new List<long>();
    List<long> decodedSequence = new List<long>();
    for (long i = 0; i < m; i++)
    {
        long encoded = (i * x) % m;
        encodedSequence.Add(encoded);
    }

    foreach (long encoded in encodedSequence)
    {
        long decoded = (encoded * y) % m;
        decodedSequence.Add(decoded);
    }
    Debug.WriteLine("just encoded sequence from 0 to {0}. Result shown below:", (m - 1));
    Debug.WriteLine("encoded sequence: " + string.Join(" ", encodedSequence));
    Debug.WriteLine("decoded sequence: " + string.Join(" ", decodedSequence));
}

The printed result is:

just encoded sequence from 0 to 9. Result shown below:
encoded sequence: 0 7 4 1 8 5 2 9 6 3
decoded sequence: 0 1 2 3 4 5 6 7 8 9

As you can see, each input is mapped to a unique output, and it's easy to reverse this mapping. In your application, you might want to start with 1 since 0 always maps to itself.

Just to show the "apparent randomness" for larger m, below are the first 10 mappings when m=100,000,000:

just encoded sequence from 1 to 10. Result shown below:
encoded sequence: 81654327 63308654 44962981 26617308 8271635 89925962 71580289 53234616 34888943 16543270
decoded sequence: 1 2 3 4 5 6 7 8 9 10

Answer 2

you can use NEWID() to generate uniqueIdentifier data which always random and unique each time

To get 8 character you can use substring, left etc function.

select substring( cast( NEWID() as varchar(100)),0,8)

or new logic for uniqueness:- http://forums.asp.net/t/1474299.aspx?How+to+generate+unique+key+of+fixed+length+20+digit+in+sql+server+2005+

select Left(NewID(),4)+Right(NewId(),4)

you can use random() function for this too.

check this links: How do I generate random number for each row in a TSQL Select?

How to get numeric random uniqueid in SQL Server

Updated If you want to unique value int data-type and 8 character long. Good to make a identity column like below, which is good for 8 character length data means (10,000,000). But after that it gives you exception. So be carefull which logic you want.(Still I say, its bad idea). store as random value as above with more length so uniqueness comes.

create table temp (id numeric(8,0) IDENTITY(1,1) NOT NULL, value1 varchar ) --if you want do not stop after 8 character , give int datatype.

insert into temp values( 'a'), ('b'), ('c')

select * from temp

drop table temp

Finally

It's not guaranteed unique but it's hard to get duplicates with NEWID()(refer above link forums.asp.net)

Answer 3

USE the below query to create 8 digit randow unique number.

SELECT CAST(RAND() * 100000000 AS INT) AS [RandomNumber]

To avoid exception while inserting of existing number into DB use below query.

IF NOT EXIST(SELECT UniqueColumnID FROM TABLENAME WHERE UniqueColumnID = @RandowNumber)
BEGIN
    --Insert query using  @RandowNumber.
END

Answer 4

Create a SQL function or procedure as follow:

ALTER FUNCTION [dbo].[GenerateRandomNo] 
(
@Lower INT  = 111111111,
@Upper INT = 999999999
)
RETURNS NVARCHAR(128)
AS
BEGIN
DECLARE @TempRandom FLOAT
DECLARE @Random NVARCHAR(128); 


-- Add the T-SQL statements to compute the return value here
SELECT @TempRandom = RandomNo from RandomNo
SELECT @Random = CONVERT(NVARCHAR(128),CONVERT(INT,ROUND(((@Upper - @Lower -1) * @TempRandom + @Lower), 0)))

WHILE EXISTS(SELECT * FROM Table WHERE Column = @Random)
BEGIN
    SELECT @TempRandom = RandomNo from RandomNo
    SELECT @Random = CONVERT(NVARCHAR(128),CONVERT(INT, ROUND(((@Upper - @Lower -1) * @TempRandom + @Lower), 0)))
END

-- Return the result of the function
RETURN @Random

END

And then call that function passing parameter if you want to generate random no with specific length or range.

Answer 5

 --create
--   table Tbl( idx int)
   DECLARE
   @unique_id int  

 SET @unique_id=  ( SELECT ROUND( 89999999 * RAND(
                                                   ) + 10000000 , 0
                                  )
                    )            
IF not EXISTS( SELECT idx
             FROM tbl
             WHERE idx = @unique_id
         )
    BEGIN
        INSERT INTO  tbl( idx
                               )
        VALUES( @unique_id
              )

SELECT @unique_id, * FROM tbl
    END;

    --TRUNCATE TABLE tbl