In SQL Server, you can use the CRYPT_GEN_RANDOM() function to return a cryptographic, randomly-generated number. The number is generated by the Cryptographic Application Programming Interface (CAPI).

CAPI is a Microsoft Windows platform specific application programming interface included with Microsoft Windows operating systems that provides services to enable developers to secure Windows-based applications using cryptography.

The CRYPT_GEN_RANDOM() function accepts two arguments: the length (required), and a seed (optional).

The return value is varbinary(8000).

Syntax

The syntax goes like this:

CRYPT_GEN_RANDOM ( length [ , seed ] )

Where length is the length, in bytes, of the number to be created, and seed is an optional hexadecimal number, for use as a random seed value.

The length argument must be between 1 and 8000.

The length of seed must match the value of the length argument.

Example 1 – Basic Usage

Here’s an example that creates a random number with a length of 4 bytes.

SELECT CRYPT_GEN_RANDOM(4) AS Result;

Result:

+------------+
| Result     |
|------------|
| 0x7D6B535F |
+------------+

Here’s one with a length of 20 bytes.

SELECT CRYPT_GEN_RANDOM(20) AS Result;

Result:

+--------------------------------------------+
| Result                                     |
|--------------------------------------------|
| 0xA56B1ADE554F48DAE55F26605AEA2E229D529BA2 |
+--------------------------------------------+

Example 2 – Use a Seed

In this example I add a seed.

SELECT CRYPT_GEN_RANDOM(4, 0x13F18C70) AS Result;

Result:

+------------+
| Result     |
|------------|
| 0xF36CB19D |
+------------+