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


random Number


random Number

Author
Message
sudhakara
sudhakara
SSCrazy
SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)

Group: General Forum Members
Points: 2980 Visits: 1386
How can i generate random unique number in sql server except newid() ?
Jack Corbett
  Jack Corbett
SSC Guru
SSC Guru (54K reputation)SSC Guru (54K reputation)SSC Guru (54K reputation)SSC Guru (54K reputation)SSC Guru (54K reputation)SSC Guru (54K reputation)SSC Guru (54K reputation)SSC Guru (54K reputation)

Group: General Forum Members
Points: 54793 Visits: 14926
Check out the RAND function in BOL.



Jack Corbett

Applications Developer

Don't let the good be the enemy of the best. -- Paul Fleming
At best you can say that one job may be more secure than another, but total job security is an illusion. -- Rod at work

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
GSquared
GSquared
SSC Guru
SSC Guru (72K reputation)SSC Guru (72K reputation)SSC Guru (72K reputation)SSC Guru (72K reputation)SSC Guru (72K reputation)SSC Guru (72K reputation)SSC Guru (72K reputation)SSC Guru (72K reputation)

Group: General Forum Members
Points: 72561 Visits: 9730
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
Jared Hunt
Jared Hunt
SSC-Enthusiastic
SSC-Enthusiastic (169 reputation)SSC-Enthusiastic (169 reputation)SSC-Enthusiastic (169 reputation)SSC-Enthusiastic (169 reputation)SSC-Enthusiastic (169 reputation)SSC-Enthusiastic (169 reputation)SSC-Enthusiastic (169 reputation)SSC-Enthusiastic (169 reputation)

Group: General Forum Members
Points: 169 Visits: 2715
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?
sudhakara
sudhakara
SSCrazy
SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)

Group: General Forum Members
Points: 2980 Visits: 1386
I have tested with rand() but its generating duplicate number . I need unique number for each transaction in my application thats why .
GSquared
GSquared
SSC Guru
SSC Guru (72K reputation)SSC Guru (72K reputation)SSC Guru (72K reputation)SSC Guru (72K reputation)SSC Guru (72K reputation)SSC Guru (72K reputation)SSC Guru (72K reputation)SSC Guru (72K reputation)

Group: General Forum Members
Points: 72561 Visits: 9730
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
sudhakara
sudhakara
SSCrazy
SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)

Group: General Forum Members
Points: 2980 Visits: 1386
Thanks For your Reply
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (263K reputation)SSC Guru (263K reputation)SSC Guru (263K reputation)SSC Guru (263K reputation)SSC Guru (263K reputation)SSC Guru (263K reputation)SSC Guru (263K reputation)SSC Guru (263K reputation)

Group: General Forum Members
Points: 263521 Visits: 42183
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.
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
sudhakara
sudhakara
SSCrazy
SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)

Group: General Forum Members
Points: 2980 Visits: 1386
Am using checksum(newid()) .....
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (263K reputation)SSC Guru (263K reputation)SSC Guru (263K reputation)SSC Guru (263K reputation)SSC Guru (263K reputation)SSC Guru (263K reputation)SSC Guru (263K reputation)SSC Guru (263K reputation)

Group: General Forum Members
Points: 263521 Visits: 42183
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.
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
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