Recent PostsRecent Posts Popular TopicsPopular Topics
 Home Search Members Calendar Who's On

 random integer number scalar function Rate Topic Display Mode Topic Options
Author
 Message
 Posted Wednesday, November 26, 2008 8:48 AM
 Valued Member Group: General Forum Members Last Login: Tuesday, August 13, 2013 9:52 PM Points: 55, Visits: 157
 Comments posted to this topic are about the item random integer number scalar function
Post #609186
 Posted Wednesday, November 26, 2008 10:00 PM
 SSC-Dedicated Group: General Forum Members Last Login: Yesterday @ 4:26 PM Points: 34,534, Visits: 28,696
 Ummm... good idea and nice try... but doesn't work to create different random numbers within the same Select. For example, using the eample Select you have with your code....`select dbo.fx_getrandomnumber(500, 300, rand())FROM dbo.TallyWHERE N <= 10`... will return 10 identical numbers. --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." "Change is inevitable. Change for the better is not." -- 04 August 2013(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013Helpful Links:How to post code problemsHow to post performance problems
Post #609557
 Posted Wednesday, November 26, 2008 10:09 PM
 SSC-Dedicated Group: General Forum Members Last Login: Yesterday @ 4:26 PM Points: 34,534, Visits: 28,696
 Here's a "fix" for your code... doesn't change a thing in the code... just the way the "seed" for the RAND function works...`select dbo.fx_getrandomnumber(500, 300, rand(CHECKSUM(NEWID())))FROM dbo.TallyWHERE N <= 10`That will randomly generate 10 "different" integers with the understanding that it's the nature of random numbers to occasionally be duplicate in any set of random numbers.If I may suggest, if you need to write that much code to use a function, you may want to consider not even using a function. Just do it "inline".` SELECT ABS(CHECKSUM(NEWID()))%(500-300+1)+300 FROM dbo.Tally WHERE N <= 10` --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." "Change is inevitable. Change for the better is not." -- 04 August 2013(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013Helpful Links:How to post code problemsHow to post performance problems
Post #609559
 Posted Friday, November 28, 2008 7:12 AM
 Valued Member Group: General Forum Members Last Login: Tuesday, August 13, 2013 9:52 PM Points: 55, Visits: 157
 thanks thats a useful technique!
Post #610330
 Posted Friday, November 28, 2008 7:38 AM
 SSCertifiable Group: General Forum Members Last Login: Yesterday @ 9:55 AM Points: 6,272, Visits: 12,080
 Now this is interesting. Watch what happens when you try to find out how many of those random integers are randomly repeated:`SELECT Number, COUNT(*) FROM (SELECT ABS(CHECKSUM(NEWID()))%(500-300+1)+300 AS Number FROM dbo.Numbers WHERE number <= 200) d GROUP BY Number ORDER BY Number`:D “Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail ShawFor fast, accurate and documented assistance in answering your questions, please read this article.Understanding and using APPLY, (I) and (II) Paul White Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff ModenExploring Recursive CTEs by Example Dwain Camps
Post #610347
 Posted Friday, November 28, 2008 7:54 AM
 SSC-Dedicated Group: General Forum Members Last Login: Yesterday @ 4:26 PM Points: 34,534, Visits: 28,696
 That's about what I would expect from an almost real set of random numbers. --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." "Change is inevitable. Change for the better is not." -- 04 August 2013(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013Helpful Links:How to post code problemsHow to post performance problems
Post #610368
 Posted Friday, November 28, 2008 7:57 AM
 SSCertifiable Group: General Forum Members Last Login: Yesterday @ 9:55 AM Points: 6,272, Visits: 12,080
 Jeff Moden (11/28/2008)That's about what I would expect from an almost real set of random numbers.You mean almost reel, right? :P-- did you catch the dupes? “Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail ShawFor fast, accurate and documented assistance in answering your questions, please read this article.Understanding and using APPLY, (I) and (II) Paul White Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff ModenExploring Recursive CTEs by Example Dwain Camps
Post #610371
 Posted Friday, November 28, 2008 8:03 AM
 SSCertifiable Group: General Forum Members Last Login: Yesterday @ 9:55 AM Points: 6,272, Visits: 12,080
 Jeff Moden (11/28/2008)That's about what I would expect from an almost real set of random numbers.Sorry Jeff, should have explained more:`SELECT Number, COUNT(*) as NumberCountFROM (SELECT ABS(CHECKSUM(NEWID()))%(500-300+1)+300 AS Number FROM dbo.Numbers WHERE number <= 200) d GROUP BY Number ORDER BY Number`Some results:`Number NumberCount ----------- ----------- 300 1303 1303 1305 2307 3307 3307 1309 2``SELECT Number, SUM(NumberCount), count(*)FROM (SELECT Number, COUNT(*) as NumberCount FROM (SELECT ABS(CHECKSUM(NEWID()))%(500-300+1)+300 AS Number FROM dbo.Numbers WHERE number <= 200 ) d GROUP BY Number ) d2GROUP BY NumberORDER BY Number`Some results:`Number ----------- ----------- ----------- 301 4 3304 1 1307 2 2308 1 1309 1 1` “Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail ShawFor fast, accurate and documented assistance in answering your questions, please read this article.Understanding and using APPLY, (I) and (II) Paul White Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff ModenExploring Recursive CTEs by Example Dwain Camps
Post #610374
 Posted Friday, November 28, 2008 3:43 PM
 SSC-Dedicated Group: General Forum Members Last Login: Yesterday @ 4:26 PM Points: 34,534, Visits: 28,696
 Chris Morris (11/28/2008)Jeff Moden (11/28/2008)That's about what I would expect from an almost real set of random numbers.You mean almost reel, right? :P-- did you catch the dupes?Yep... and the dupes are appropriate for such a small set of random numbers. Consider the simplest of all random number systems... Black and Red spots (with the occasional Green spot or spots) on a Roulette wheel... what would you guess would be the maximum number of times that, say, Black would show up consecutively? --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." "Change is inevitable. Change for the better is not." -- 04 August 2013(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013Helpful Links:How to post code problemsHow to post performance problems
Post #610593
 Posted Friday, November 28, 2008 5:18 PM
 SSCrazy Eights Group: General Forum Members Last Login: Tuesday, November 26, 2013 10:42 AM Points: 9,874, Visits: 9,432
 Yeah, but he's getting dupes, even from the Group By categories, Jeff.Chris: FWIW, I am not seeing this on my Laptop. -- RBarryYoung, (302)375-0451 blog: MovingSQL.com, Twitter: @RBarryYoungProactive Performance Solutions, Inc. "Performance is our middle name."
Post #610609

 Permissions