## random integer number scalar function

 Author Message kodracon SSC Veteran Group: General Forum Members Points: 253 Visits: 162 Comments posted to this topic are about the item random integer number scalar function Jeff Moden SSC Guru Group: General Forum Members Points: 339462 Visits: 42623 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 ModenRBAR 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.If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair Helpful Links:How to post code problemsHow to post performance problemsForum FAQs Jeff Moden SSC Guru Group: General Forum Members Points: 339462 Visits: 42623 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 ModenRBAR 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.If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair Helpful Links:How to post code problemsHow to post performance problemsForum FAQs kodracon SSC Veteran Group: General Forum Members Points: 253 Visits: 162 thanks thats a useful technique! ChrisM@Work SSC Guru Group: General Forum Members Points: 65820 Visits: 20214 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` “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 Jeff Moden SSC Guru Group: General Forum Members Points: 339462 Visits: 42623 That's about what I would expect from an almost real set of random numbers. --Jeff ModenRBAR 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.If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair Helpful Links:How to post code problemsHow to post performance problemsForum FAQs ChrisM@Work SSC Guru Group: General Forum Members Points: 65820 Visits: 20214 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? -- 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 ChrisM@Work SSC Guru Group: General Forum Members Points: 65820 Visits: 20214 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 Jeff Moden SSC Guru Group: General Forum Members Points: 339462 Visits: 42623 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? -- 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 ModenRBAR 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.If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair Helpful Links:How to post code problemsHow to post performance problemsForum FAQs RBarryYoung SSC Guru Group: General Forum Members Points: 55174 Visits: 9518 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."