Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 123»»»

random integer number scalar function Expand / Collapse
Author
Message
Posted Wednesday, November 26, 2008 8:48 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Thursday, July 17, 2014 6:57 AM
Points: 55, Visits: 159
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

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 8:08 AM
Points: 35,366, Visits: 31,906
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #609557
Posted Wednesday, November 26, 2008 10:09 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 8:08 AM
Points: 35,366, Visits: 31,906
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #609559
Posted Friday, November 28, 2008 7:12 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Thursday, July 17, 2014 6:57 AM
Points: 55, Visits: 159
thanks thats a useful technique!
Post #610330
Posted Friday, November 28, 2008 7:38 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 9:54 AM
Points: 6,813, Visits: 14,028
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 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
Post #610347
Posted Friday, November 28, 2008 7:54 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 8:08 AM
Points: 35,366, Visits: 31,906
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #610368
Posted Friday, November 28, 2008 7:57 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 9:54 AM
Points: 6,813, Visits: 14,028
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 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
Post #610371
Posted Friday, November 28, 2008 8:03 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 9:54 AM
Points: 6,813, Visits: 14,028
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
Post #610374
Posted Friday, November 28, 2008 3:43 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 8:08 AM
Points: 35,366, Visits: 31,906
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #610593
Posted Friday, November 28, 2008 5:18 PM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Wednesday, October 15, 2014 9:14 AM
Points: 9,294, Visits: 9,483
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."
Post #610609
« Prev Topic | Next Topic »

Add to briefcase 123»»»

Permissions Expand / Collapse