SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


random integer number scalar function


random integer number scalar function

Author
Message
kodracon
kodracon
SSC-Enthusiastic
SSC-Enthusiastic (101 reputation)SSC-Enthusiastic (101 reputation)SSC-Enthusiastic (101 reputation)SSC-Enthusiastic (101 reputation)SSC-Enthusiastic (101 reputation)SSC-Enthusiastic (101 reputation)SSC-Enthusiastic (101 reputation)SSC-Enthusiastic (101 reputation)

Group: General Forum Members
Points: 101 Visits: 162
Comments posted to this topic are about the item random integer number scalar function
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)

Group: General Forum Members
Points: 87458 Visits: 41116
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.Tally
WHERE 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.
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 problems
How to post performance problems
Forum FAQs
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)

Group: General Forum Members
Points: 87458 Visits: 41116
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.Tally
WHERE 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.
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 problems
How to post performance problems
Forum FAQs
kodracon
kodracon
SSC-Enthusiastic
SSC-Enthusiastic (101 reputation)SSC-Enthusiastic (101 reputation)SSC-Enthusiastic (101 reputation)SSC-Enthusiastic (101 reputation)SSC-Enthusiastic (101 reputation)SSC-Enthusiastic (101 reputation)SSC-Enthusiastic (101 reputation)SSC-Enthusiastic (101 reputation)

Group: General Forum Members
Points: 101 Visits: 162
thanks thats a useful technique!
ChrisM@Work
ChrisM@Work
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16472 Visits: 19557
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



BigGrin

“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

For 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 Moden
Exploring Recursive CTEs by Example Dwain Camps
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)

Group: General Forum Members
Points: 87458 Visits: 41116
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.
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 problems
How to post performance problems
Forum FAQs
ChrisM@Work
ChrisM@Work
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16472 Visits: 19557
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? Tongue

-- 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 Shaw

For 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 Moden
Exploring Recursive CTEs by Example Dwain Camps
ChrisM@Work
ChrisM@Work
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16472 Visits: 19557
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 NumberCount
FROM (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 1
303 1
303 1
305 2
307 3
307 3
307 1
309 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
) d2
GROUP BY Number
ORDER BY Number



Some results:
Number                              
----------- ----------- -----------
301 4 3
304 1 1
307 2 2
308 1 1
309 1 1



“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

For 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 Moden
Exploring Recursive CTEs by Example Dwain Camps
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)

Group: General Forum Members
Points: 87458 Visits: 41116
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? Tongue

-- 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.
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 problems
How to post performance problems
Forum FAQs
RBarryYoung
RBarryYoung
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14982 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: @RBarryYoung
Proactive Performance Solutions, Inc.
"Performance is our middle name."
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