rand() generally doesn't do what you want. for instance, select TechId, rand() random_number from dbo.Person will return the same random_number for each record. Prior to SQL2008 we generally had to use cursors and rand() to get a per-record random number.
crypt_gen_random is new in SQL 2008 and is currently the preferred method of generating random numbers.
crypt_gen_random(4) will generate a 4 byte random number, which can be stored in an int field. crypt_gen_random(8) will generate a 8 byte random number, which can be stored in an bigint field. These numbers will include positive and negative numbers.
The function we use in the SurveyDB and ReportingServicesUtils databases returns a number between 0 and 1. We use crypt_gen_random(7) get get around dealing with negative numbers. That function is:
CREATE FUNCTION [dbo].[fnRanNum]( @CryptGenRandom as bigint )
RETURNS float(53)
AS
BEGIN
return cast(cast(@CryptGenRandom as float(53))/cast(cast(0xFFFFFFFFFFFFFF as bigint) as float(53)) as float)
END
This fuction is called using:
select [dbo].[fnRanNum] (crypt_gen_random(7))
We need to pass crypt_gen_random() into the function this way because using this crypt_gen_random within the function gives us a "Msg 443, Level 16, State 2, Line 1 Invalid use of a side-effecting operator 'RETURN' within a function." error in recent version of SQL Server.
Using this function instead of crypt_gen_random directly will prevent the query optimizer from running crypt_gen_rand() only once in the query. For instance,
-- this query doesn't do what you expect
select PersonId, convert(bigint, crypt_gen_random(8))
,row_number() over (order by convert(bigint, crypt_gen_random(8))) row
from Person
will not do what you want. The crypt_gen_random() call in the row_number() is only run once, so row is not random. Using the UDF above fixes this, because the engine doesn't attempt to optimize code in a UDF.
One method to determine a random sample from a population is:
select PersonID
from (
select PersonID
,row_number() over (order by ReportingServicesUtils.dbo.fnRanNum(crypt_gen_random(7)) desc) row
from dbo.Person
) a
-- sample size
where row<=100