Snippets

Craig Phillips SQL Server Random Numbers And Other Things!

Created by Craig Phillips
-- Create a temporary table in an in-memory variable simply to allow the
-- select statement to output multiple rows.
DECLARE @TestTable TABLE (uniqueID int)
INSERT INTO @TestTable
SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL
SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9 UNION ALL SELECT 10
 
-- The main Select statement that shows different correct (and incorrect) ways to generate
-- "random" things in a set-based select statement.
SELECT
    uniqueId,
	RAND() AS Not_Random_At_All,
	RAND(CHECKSUM(NEWID())) AS True_Random_Between_Zero_And_One,
	Abs(Checksum(NewID())) as Positive_Random_Number_Using_BuiltIn_NewID_And_CheckSum_Functions,
	(Abs(Checksum(NewId())) % 10) as Random_Number_Between_Zero_And_Nine,
	(Abs(Checksum(NewId())) % 10 + 1) as Random_Number_Between_One_And_Ten,
	DateAdd(d, (Abs(Checksum(NewId())) % 365), '01/jan/2013') as Random_Date_In_The_Year_2013
FROM
	@TestTable

Comments (0)