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