# random integer number scalar function

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

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

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

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

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

• thanks thats a useful technique!

• 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

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

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

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

• 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

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

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

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

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

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

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

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

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

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

• 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

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

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

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

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

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