Call now: (800) 766-1884  



 Home


 SQL Server Tips
 SQL Server Training

 SQL Server Consulting
 SQL Server Support
 SQL Server Remote DBA



 Articles
 Services
 SQL Server Scripts
 Scripts Menu



 

 

 

   
  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 numbers.
     * 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 be fulfilled.".
     * 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 set operations.
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
RETURNS int
AS
BEGIN
DECLARE @random int
EXEC master..XP_RAND @random OUTPUT
SET @random=ABS(@random) % 10+1
RETURN (@random)
END

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:

Super SQL Server Systems
Turbocharge Database Performance with C++ External Procedures

ISBN: 0-9761573-2-2
Joseph Gama, P. J. Naughter

 http://www.rampant-books.com/book_2005_2_sql_server_external_procedures.htm  

   

 

Burleson Consulting Remote DB Administration


 

 


 

 

 

 

 
Burleson is the America's Team

Note: The pages on this site were created as a support and training reference for use by our staff of DBA consultants.  If you find it confusing, please exit this page.

Errata?  SQL Server technology is changing and we strive to update our SQL Server support information.  If you find an error or have a suggestion for improving our content, we would appreciate your feedback.  Just  e-mail:and include the URL for the page.
 


Burleson Consulting
SQL Server database support

 

Copyright 1996 -  2013 by Vaaltech Web Services. All rights reserved.

Hit Counter