Cindy Conway-312336 (4/25/2013)
I've created a CLR TVF to handle this. I find C# easier to use than t-SQL for random numbers within a range. My TVF returns a uniqueKey along with the random numbers, so I just join the rowNumber() with the uniqueKey. The update statement ends up looking like this. Here is how to create the TVF that generated the random numbers.;WITH clientCTE AS
(
SELECT
ROW_NUMBER() OVER(ORDER BY int_ID) AS 'RowNumber',
randomNumber
FROM
client
)
UPDATE clientCTE
SET randomNumber = rn.RandomNumber
FROM dbo.tvfRandomNumberList(1,5,1000) AS rn
WHERE rn.UniqueKey = clientCTE.RowNumber
Hi Cindy,
Nice code.
Random numbers just aren't that difficult to handle in T-SQL. With just a little of the "Black Arts", it's easy to create a direct replacement for your fine CLR using only T-SQL. Here's the code to do such a thing. I've kept on with your naming convention and shamelessly borrowed the header comments from your CLR. Its performance is probably second only to a CLR such as yours.
CREATE VIEW dbo.vUnconstrainedRandomInt WITH SCHEMABINDING AS
/***************************************************************************************************
Returns an unconstrained random integer suitable for use virtually anywhere including in a function
***************************************************************************************************/
SELECT UnconstrainedRandomInt = ABS(CHECKSUM(NEWID()));
GO
CREATE FUNCTION dbo.tvfRandomNumberList
/***************************************************************************************************
Function returns a list of randomly generated numbers with a UniqueKey.
@pMinValue = the mimiumn value of the random numbers generated.
@pMaxValue = the maximum value of the random numbers generated.
@pSize = the number of random numbers to be returned.
This code is a direct replacement for the CLR function which may be found at the following URL.
http://www.sqlwithcindy.com/2013/04/elegant-random-number-list-in-sql-server.html
***************************************************************************************************/
--===== Declare the I/O for this function
(
@pMinValue INT
, @pMaxValue INT
, @pSize INT
)
RETURNS TABLE WITH SCHEMABINDING AS
--===== Create and return the random number list
RETURN WITH
E1(N) AS (
SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1 UNION ALL SELECT 1 --10E1 or up to 10 rows
)
, E3(N) AS (SELECT 1 FROM E1 a, E1 b, E1 c) --10E3 or up to 1,000 rows
, E9(N) AS (SELECT 1 FROM E3 a, E3 b, E3 c) --10E9 or up to 1,000,000,000 rows
SELECT TOP (@pSize)
UniqueKey = ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
, RandomNumber = ri.UnconstrainedRandomInt %(@pMaxValue-@pMinValue+1) +@pMinValue
FROM E9, dbo.vUnconstrainedRandomInt ri
;
The truth be told, though, the generation of random numbers don't need such sophistication as an iTVF. For example, you wrote the following code to assign random numbers to the "Client" table.
;WITH clientCTE AS
(
SELECT
ROW_NUMBER() OVER(ORDER BY int_ID) AS 'RowNumber',
randomNumber
FROM
client
)
UPDATE clientCTE
SET randomNumber = rn.RandomNumber
FROM dbo.tvfRandomNumberList(1,5,1000) AS rn
WHERE rn.UniqueKey = clientCTE.RowNumber
Again, using some of the "Black Arts" available in T-SQL, the code can be greatly simplified and the expense of a join can be avoided thusly...
UPDATE dbo.Client
SET RandomNumber = ABS(CHECKSUM(NEWID())) %5 +1
;
For more information on the generation of random Integers, random Floats, and random Dates with or without Times, please see the following articles:
http://www.sqlservercentral.com/articles/Data+Generation/87901/
http://www.sqlservercentral.com/articles/Test+Data/88964/
--Jeff Moden
Change is inevitable... Change for the better is not.