Random functions in MS SQL

In this article, we delve into various random functions available in MS SQL and explore their practical applications. While some of these functions may be considered outdated, others continue to offer possibilities for developers. I have used these over the years and just wanted to write them down.

MS SQL provides several methods for generating random numbers. The most common function is RAND(), which produces a random float value between 0 and 1. Another option is NEWID(), which generates a unique identifier (GUID) that can effectively serve as a random value. Below, I will show a couple of examples.

Select a random row

This is something I've used quite a bit. Sometimes it's pretty neat to grab a random row, like when you want to show one or more random recipes on your website. ;)

SELECT TOP 1 * FROM MyTable ORDER BY NEWID();

The above is a simple fix and does the job well for smaller tables. In my case (recipe database), it's just what I need.

But there's a performance issue with NEWID(). So here is another solution using CHECKSUM() and RAND():

SELECT TOP 1 * FROM MyTable ORDER BY RAND(CHECKSUM(*) * RAND());

This second solution is faster and also opens up another possibility. If I want to display a random recipe on the front page but also keep that recipe for the whole day, I could use the seed option in RAND().

SELECT CONVERT(VARCHAR,GETDATE(),112); -- Todays date, example result: 20240303

DECLARE @seed INT = CONVERT(VARCHAR,GETDATE(),112);
SELECT TOP 1 * FROM CommonWords ORDER BY RAND(CHECKSUM(*) * RAND(@seed));

We could condense the above code into a single line if necessary. However, in a proper database, this is probably something I would incorporate into a Stored Procedure, so I'll leave it as it is.

Random pin code

In this code snippet, I'm utilizing NEWID() to create a random unique identifier.

SELECT NEWID();
-- Example result: ADF23CB4-2DE1-4033-9375-6B0C9907CA34

Afterward, the string is converted to VARBINARY and then to a positive integer.

SELECT ABS(CAST(CAST(NEWID() AS VARBINARY) AS INT));
-- Example result: 1727542732

For the final step, we need to cast it to a varchar and limit it to the desired number of digits (in this case, 6).

SELECT LEFT(CAST(ABS(CAST(CAST(NEWID() AS VARBINARY) AS INT)) AS VARCHAR), 6) AS [RandomNumber];
-- Example result: 172754

Alternatively, we can employ the RAND() function. This generates a decimal value between 0 and 1. We can then multiply this value by a large number for the LEFT() function to extract the desired digits. To ensure we never encounter the edge case where RAND() returns 0, we can add a large number so that we always obtain a 6-digit number.

SELECT LEFT(CAST(RAND()*1000000000+999999 AS INT),6) AS [RandomNumber];
-- Example result: 144878

Random and User Defined Functions

I'm a big fan of encapsulating reusable code within functions, but attempting to do so with code involving RAND() or NEWID() will result in the following error:

Invalid use of a side-effecting operator 'rand' within a function.

It seems that using those built-in functions inside a User-Defined Function is not possible. So, how do we handle this limitation?

An alternative approach would be to place the code within a stored procedure instead. However, since my aim is to utilize a User Defined Function (UDF), this approach won't be suitable.

Yet another option is to incorporate a random number as an input variable into the function.:

CREATE FUNCTION UDF_PinCodeGenerate
(
	@rand DECIMAL(20,10),
	@pinCodeLength INT
)
RETURNS INT
AS
BEGIN
	RETURN LEFT(CAST(@rand*1000000000+999999 AS INT),@pinCodeLength);
END
GO

SELECT dbo.UDF_PinCodeGenerate(RAND(), 6);

-- Example result: 700874

But I'm not particularly fond of that approach. I prefer not to use parameters in that manner. So, let's explore the HACK version instead. While there may be other solutions available, this is the one I've implemented and found effective.

The primary hack involves creating a simple view that returns a random number. Then, this view is invoked from within the function. Create the view like this:

CREATE VIEW vwRandom
AS
	SELECT RAND() random;
GO

And then modify the function to resemble the following:

ALTER FUNCTION UDF_PinCodeGenerate
(
	@pinCodeLength INT
)
RETURNS INT
AS
BEGIN
	RETURN LEFT(CAST((SELECT random FROM vwRandom)*1000000000+999999 AS INT),@pinCodeLength);
END
GO

And now, we can call this updated User Defined Function and obtain the expected result:

SELECT dbo.UDF_PinCodeGenerate(6);
-- Example result: 257850

And that's a wrap for now. If I come across more of my old random snippets elsewhere, I'll be sure to incorporate them into this article in the future.