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

random Number Expand / Collapse
Author
Message
Posted Wednesday, May 07, 2008 7:09 AM
SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Yesterday @ 9:39 PM
Points: 603, Visits: 1,281
How can i generate random unique number in sql server except newid() ?
Post #496284
Posted Wednesday, May 07, 2008 7:11 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 12:31 PM
Points: 10,910, Visits: 12,545
Check out the RAND function in BOL.



Jack Corbett

Applications Developer

Don't let the good be the enemy of the best. -- Paul Fleming

Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
How to Post Performance Problems
Crosstabs and Pivots or How to turn rows into columns Part 1
Crosstabs and Pivots or How to turn rows into columns Part 2
Post #496288
Posted Wednesday, May 07, 2008 12:30 PM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Monday, April 14, 2014 1:34 PM
Points: 15,442, Visits: 9,588
How unique does the random number need to be? As in, if someone or some process gets 10 today, then 10 can never, ever come up again, or as in 10 probably shouldn't be the next number? And how random does it need to be?

Rand() is pretty limited in SQL. With the same seed, it gets the same number every time. If the numbers need to be unique, that definitely won't do.

There are other methods to get random numbers, but I need to know how unique and how random before I recommend one.


- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Post #496592
Posted Wednesday, May 07, 2008 1:25 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Wednesday, May 12, 2010 8:52 PM
Points: 51, Visits: 2,715
As the pervious poster asked, what is the purpose? Remember, there are always multiple ways to accomplish a task in SQL. It is the end result/goal that dictates the best solution.

For example, do you need a function that will generate a unique number, or are you creating a table that needs a unique id column?
Post #496627
Posted Wednesday, May 07, 2008 10:59 PM
SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Yesterday @ 9:39 PM
Points: 603, Visits: 1,281
I have tested with rand() but its generating duplicate number . I need unique number for each transaction in my application thats why .
Post #496854
Posted Thursday, May 08, 2008 9:21 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Monday, April 14, 2014 1:34 PM
Points: 15,442, Visits: 9,588
If it needs to be unique, why does it need to be random?

Would a sequential (identity) number accomplish what you need?

If it truly needs to be both random and unique, you may want to pre-generate the numbers, then use an identity field to link to them:

create table RandomNumbers (
ID int primary key,
Number int)
go
;with
CTE1 (Number) as
(select abs(checksum(newid()))/100
from common.dbo.bignumbers bn1,
common.dbo.bignumbers bn2),
CTE2 (Number) as
(select distinct number
from cte1)
insert into dbo.randomnumbers (id, number)
select row_number() over (order by newid()), number
from cte2

Then, put an identity column in your table where you need a random number, and join it to the RandomNumbers table to get your random number.

On my desktop computer, this took about 10 seconds to generate about 1-million unique, random numbers.


- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Post #497182
Posted Monday, May 12, 2008 6:26 AM
SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Yesterday @ 9:39 PM
Points: 603, Visits: 1,281

Thanks For your Reply
Post #498706
Posted Wednesday, May 21, 2008 8:19 PM


SSC-Dedicated

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

Group: General Forum Members
Last Login: Yesterday @ 10:03 PM
Points: 35,956, Visits: 30,244
What did you end up doing?

--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." -- 04 August 2013
(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 #504948
Posted Wednesday, May 21, 2008 10:34 PM
SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Yesterday @ 9:39 PM
Points: 603, Visits: 1,281
Am using checksum(newid()) .....

Post #504982
Posted Wednesday, May 21, 2008 10:50 PM


SSC-Dedicated

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

Group: General Forum Members
Last Login: Yesterday @ 10:03 PM
Points: 35,956, Visits: 30,244
Not good... you can get dupes that way because of the relatively narrow INT range that CHECKSUM offers compared to the binary range of NEWID()... here's the proof of the dupage...

 SELECT N,COUNT(*)
FROM (SELECT TOP 1000000
CHECKSUM(NEWID()) AS N
FROM Master.dbo.SysColumns sc1,
Master.dbo.SysColumns sc2)d
GROUP BY N
HAVING COUNT(*) >1

The following, on the other hand, guarantees no dupage... guarantees unique numbers even across servers...

 SELECT N,COUNT(*)
FROM (SELECT TOP 1000000
(CAST(CAST(newid() AS VARBINARY) AS BIGINT)) AS N
FROM Master.dbo.SysColumns sc1,
Master.dbo.SysColumns sc2)d
GROUP BY N
HAVING COUNT(*) >1

... and this will return only positive numbers...

 SELECT N,COUNT(*)
FROM (SELECT TOP 1000000
ABS(CAST(CAST(newid() AS VARBINARY) AS BIGINT)) AS N
FROM Master.dbo.SysColumns sc1,
Master.dbo.SysColumns sc2)d
GROUP BY N
HAVING COUNT(*) >1

... although there's a one in a bazillion chance that could return a dupe because it "folds" positive and negative numbers over onto the same size of zero.


--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." -- 04 August 2013
(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 #504986
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse