Generating a Random Account Number

Question

For privacy purposes, I need to derive an "Account Number" field that contains a random number, ranging from 1000 to 2000.

Answer 1

You can try the following:

SELECT (CONVERT(INT, CRYPT_GEN_RANDOM(2)) % 1001) + 1000;

• CRYPT_GEN_RANDOM(2) returns a 2-byte randomized value. Range is 0x0000 - 0xFFFF (i.e. 0 - 65,535).
• % 1001 caps the returned value at 1000 (since a returned value of 1001 resets back to 0). This handles / ensures the 1000 value range between the requested low-end of 1000 and high-end of 2000. (ok, technically 1001 values since we need to include the 0, but you know what I meant ;-)
• + 1000 adjusts the total range from being 0 - 1000 to instead be 1000 - 2000 (i.e. setting the lowest possible value)

Please note: CRYPT_GEN_RANDOM() was introduced in SQL Server 2012, which is fine here as you are using 2016, but just for other readers to understand in case someone is using 2008 R2 or older (in which case you can still use the RAND() function as suggested by Scott).

Answer 2

There are numerous examples on the Internet of generating random numbers between two numbers.

Here is an example function

--Random number function
CREATE FUNCTION Random (@Upper INT,@Lower INT, @randomvalue numeric(18,10))
RETURNS INT
AS
BEGIN
DECLARE @Random INT
SELECT @Random = round(((@Upper - @Lower + 1 ) * @randomvalue + @Lower), 0, 1)
RETURN @Random
END;
go

--Test the function
select dbo.Random(2000,1000,RAND())

---

In response to one of the questions under this answer relating to why the RAND() function is being passed into the function, it's because RAND() cannot be used inside the function itself. Trying to include RAND() inside the function will result in an error

Msg 443, Level 16, State 1, Procedure Random, Line 8 [Batch Start Line 2] Invalid use of a side-effecting operator 'rand' within a function.