random integer number scalar function

Question

random integer number scalar function

kodracon

Ten Centuries

Points: 1027
More actions
November 26, 2008 at 8:48 am

#203499

Comments posted to this topic are about the item random integer number scalar function

Viewing 15 posts - 1 through 15 (of 26 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004329 More actions · Answer 1

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.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004329 More actions · Answer 2

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.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

kodracon Ten Centuries Points: 1027 More actions · Answer 3

kodracon

Ten Centuries

Points: 1027

November 28, 2008 at 7:12 am

#904768

thanks thats a useful technique!

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 4

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

Jeff Moden SSC Guru Points: 1004329 More actions · Answer 5

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.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 6

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

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 7

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

305 2

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

Jeff Moden SSC Guru Points: 1004329 More actions · Answer 8

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

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

RBarryYoung SSC Guru Points: 143329 More actions · Answer 9

Yeah, but he's getting dupes, even from the Group By categories, Jeff.

Chris: FWIW, I am not seeing this on my Laptop.

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

Jeff Moden SSC Guru Points: 1004329 More actions · Answer 10

Dang... I missed that... was looking at the wrong column for "dupes".

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

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004329 More actions · Answer 11

Chris, I've created a Numbers table with the same column name as you (Number), and I cannot get the code to fail the same way as you have with the dupes and all.

What version of SQL Server do you have and are you using either QA or SMS for the interface? I've seen the Oracle version of TOAD produce such things, but never QA or SMS. Also, single or multi CPU on your box?

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

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 12

Jeff Moden (11/28/2008)
Chris, I've created a Numbers table with the same column name as you (Number), and I cannot get the code to fail the same way as you have with the dupes and all.
What version of SQL Server do you have and are you using either QA or SMS for the interface? I've seen the Oracle version of TOAD produce such things, but never QA or SMS. Also, single or multi CPU on your box?

It doesn't happen on the 2k5 box, only on the 2k box queried from either QA or SMS. Version is 8.00.760 (SP3) and it's dual-processor.

^{“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

Jeff Moden SSC Guru Points: 1004329 More actions · Answer 13

I've not seen such a thing... perhaps try using MaxDop 1 just to see if parallelism is getting to it? I'd try it on a multi-processor box if I had access to one, but I don't so I can't hammer it out on this end. Sorry.

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

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

RBarryYoung SSC Guru Points: 143329 More actions · Answer 14

Huhn, the bug happens on my last remaining SQL 2000 server also. MAXDOP has no effect on it.

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]