||SQL Server Tips by Gama and Naughter
Applications of XP_RAND
In the section about Nondeterministic UDF's, there is a UDF, named
UDFRandom that returns random numbers between one and ten. It uses
the trick of getting a value from a view with RAND() in a calculated
column. Unfortunately, RAND() returns a random float value from 0
through 1, which has five disadvantages:
* To generate random numbers with positive and negative
values requires extra calculations or logic.
* To generate random numbers with integer values will
run into problems with rounding the values, particularly for huge
* RAND() is a wrapper for the C runtime function
rand(). The numbers generated by rand() are not suited for
cryptographic or other uses where the quality of the random data is
essential. From the IEEE Std 1003.1: "The rand() function uses a
multiplicative congruential random-number generator with period 2^32
that returns successive pseudo-random numbers in the range of 0 to
RAND_MAX (defined in <stdlib.h>)". And from the same source: "The
limitations on the amount of state that can be carried between one
function call and another mean the rand() function can never be
implemented in a way which satisfies all of the requirements on a
pseudo-random number generator. Therefore this function should be
avoided whenever non-trivial requirements (including safety) have to
* RAND() will return a float, which is eight bytes but
there is no direct way to use those bytes as data. For example
casting RAND() to a bigint, which is also eight bytes long, will
result in 0 or 1 because casting in TSQL is implicit.
* As a consequence of point 4, generating a random
buffer of data in a loop will require many calls to the function
RAND() while XP_RAND would need only one.
A function that would return integer values, positive or negative
would be very useful for certain calculations. For practical
purposes, XP_RAND can be called from a UDF so that it can be used in
As an example, the following UDF does the same as UDFRandom but
using XP_RAND instead of RAND():
CREATE FUNCTION UDFRandomXP()
--UDF that returns a random number by using XP_RAND
DECLARE @random int
EXEC master..XP_RAND @random OUTPUT
SET @random=ABS(@random) % 10+1
This example does not take advantage of the benefits of XP_RAND over
RAND() but it shows how to use both techniques for the same purpose.
Using XP_RAND is twice slower than RAND() for single integers,
however the generated data is of higher quality. In this situation
it is necessary to choose between performance and quality/security.
The next example shows a much faster implementation by using XP_RAND.
Example: To create a function that returns an ASCII string with a
number of characters defined as the input for the function.
The above book excerpt is from:
Turbocharge Database Performance with C++ External Procedures
Joseph Gama, P. J. Naughter