Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Random number generator


Random number generator

Author
Message
Cindy Conway-312336
Cindy Conway-312336
Forum Newbie
Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)

Group: General Forum Members
Points: 8 Visits: 88
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.

[url=http://www.sqlwithcindy.com/2013/04/elegant-random-number-list-in-sql-server.html]

;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

www.sqlwithcindy.com
mister.magoo
mister.magoo
SSCrazy
SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)

Group: General Forum Members
Points: 2272 Visits: 7827
Gosh - didn't see the date ! sorry!

MM


select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);




  • Forum Etiquette: How to post Reporting Services problems
  • Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
  • How to Post Performance Problems - by Gail Shaw

  • Jeff Moden
    Jeff Moden
    SSC-Forever
    SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)

    Group: General Forum Members
    Points: 45135 Visits: 39923
    mister.magoo (4/25/2013)
    Gosh - didn't see the date ! sorry!


    Don't let dates of posts scare you away. What did you have to say?

    --Jeff Moden

    RBAR is pronounced ree-bar and is a Modenism for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
    Although they tell us that they want it real bad, our primary goal is to ensure that we dont actually give it to them that way.
    Although change is inevitable, change for the better is not.
    Just because you can do something in PowerShell, doesnt mean you should. Wink

    Helpful Links:
    How to post code problems
    How to post performance problems
    Forum FAQs
    Jeff Moden
    Jeff Moden
    SSC-Forever
    SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)

    Group: General Forum Members
    Points: 45135 Visits: 39923
    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.

    [url=http://www.sqlwithcindy.com/2013/04/elegant-random-number-list-in-sql-server.html]

    ;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

    RBAR is pronounced ree-bar and is a Modenism for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
    Although they tell us that they want it real bad, our primary goal is to ensure that we dont actually give it to them that way.
    Although change is inevitable, change for the better is not.
    Just because you can do something in PowerShell, doesnt mean you should. Wink

    Helpful Links:
    How to post code problems
    How to post performance problems
    Forum FAQs
    mister.magoo
    mister.magoo
    SSCrazy
    SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)

    Group: General Forum Members
    Points: 2272 Visits: 7827
    Jeff Moden (4/25/2013)
    mister.magoo (4/25/2013)
    Gosh - didn't see the date ! sorry!


    Don't let dates of posts scare you away. What did you have to say?


    Oh nothing important, just questioning the intent - whenever I see people asking for specific things in a non-specific way it makes me wonder what they really want. In this case, did the OP actually want random numbers assigned to each row - for what purpose? I can't think of any reasonable reason for random numbers being assigned to the rows unless it is for consistent/repeatable "random" ordering or consistent/repeatable "random" selection, and if it is one of those, do they want "random" numbers (with the possibility of duplicates - or even the low possibility of them all being assigned the same number) or unique numbers but randomly assigned ?

    However , I spotted that this was another old post being added to randomly and thought there was not much likelihood of the OP still looking for an answer 5 years one, so backed off :-P

    MM


    select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);




  • Forum Etiquette: How to post Reporting Services problems
  • Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
  • How to Post Performance Problems - by Gail Shaw

  • Jeff Moden
    Jeff Moden
    SSC-Forever
    SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)

    Group: General Forum Members
    Points: 45135 Visits: 39923
    Heh... got it and understood. It was a really open ended original question.

    --Jeff Moden

    RBAR is pronounced ree-bar and is a Modenism for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
    Although they tell us that they want it real bad, our primary goal is to ensure that we dont actually give it to them that way.
    Although change is inevitable, change for the better is not.
    Just because you can do something in PowerShell, doesnt mean you should. Wink

    Helpful Links:
    How to post code problems
    How to post performance problems
    Forum FAQs
    Cindy Conway-312336
    Cindy Conway-312336
    Forum Newbie
    Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)

    Group: General Forum Members
    Points: 8 Visits: 88
    Hi MM,

    I was assigning random numbers to each row because I was generating test data. In my case, I was simulating data returned from Market Research Surveys. The questions use a scale from 1 to 5 or 1 to 10. I wanted to randomly alot ratings to my test data.

    www.sqlwithcindy.com
    mister.magoo
    mister.magoo
    SSCrazy
    SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)

    Group: General Forum Members
    Points: 2272 Visits: 7827
    Cindy Conway-312336 (4/26/2013)
    Hi MM,

    I was assigning random numbers to each row because I was generating test data. In my case, I was simulating data returned from Market Research Surveys. The questions use a scale from 1 to 5 or 1 to 10. I wanted to randomly alot ratings to my test data.


    See, that's why I'm not a mind reader, I would never have guessed that!

    Good to know anyway, thanks :-D

    MM


    select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);




  • Forum Etiquette: How to post Reporting Services problems
  • Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
  • How to Post Performance Problems - by Gail Shaw

  • Cindy Conway-312336
    Cindy Conway-312336
    Forum Newbie
    Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)

    Group: General Forum Members
    Points: 8 Visits: 88
    Hi Jeff,

    Nice code! Clever how you did that. I knew that it could be done in t-sql (although I don't think I would have come up with a function as clever as yours). I like C# for things like random numbers and complex string manipulation. The functionality built in is more elegant IMO. The simple update statement you listed is great too, but it looses the ability to specify a lower and upper range to the random numbers. Like you said, performance is pretty equal. The CLR outperforms on very big data sets (1million+), but only by a small amount .

    Anyone who reads this post gets to choose the option best suited to their particular situation.

    www.sqlwithcindy.com
    Jeff Moden
    Jeff Moden
    SSC-Forever
    SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)

    Group: General Forum Members
    Points: 45135 Visits: 39923
    Cindy Conway-312336 (4/26/2013)
    The simple update statement you listed is great too, but it looses the ability to specify a lower and upper range to the random numbers.


    Thanks for the feedback, Cindy. However, not quite true on the upper and lower range of random numbers (integers, in this case). It uses the same basic formula that a lot of random number generators use to specify the domain to be returned. If you look carefully at the function, it takes min and max values just like your CLR function does.

    --Jeff Moden

    RBAR is pronounced ree-bar and is a Modenism for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
    Although they tell us that they want it real bad, our primary goal is to ensure that we dont actually give it to them that way.
    Although change is inevitable, change for the better is not.
    Just because you can do something in PowerShell, doesnt mean you should. Wink

    Helpful Links:
    How to post code problems
    How to post performance problems
    Forum FAQs
    Go


    Permissions

    You can't post new topics.
    You can't post topic replies.
    You can't post new polls.
    You can't post replies to polls.
    You can't edit your own topics.
    You can't delete your own topics.
    You can't edit other topics.
    You can't delete other topics.
    You can't edit your own posts.
    You can't edit other posts.
    You can't delete your own posts.
    You can't delete other posts.
    You can't post events.
    You can't edit your own events.
    You can't edit other events.
    You can't delete your own events.
    You can't delete other events.
    You can't send private messages.
    You can't send emails.
    You can read topics.
    You can't vote in polls.
    You can't upload attachments.
    You can download attachments.
    You can't post HTML code.
    You can't edit HTML code.
    You can't post IFCode.
    You can't post JavaScript.
    You can post emoticons.
    You can't post or upload images.

    Select a forum

































































































































































    SQLServerCentral


    Search