May 7, 2008 at 7:09 am
How can i generate random unique number in sql server except newid() ?
May 7, 2008 at 7:11 am
Check out the RAND function in BOL.
Jack Corbett
Consultant - Straight Path Solutions
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
May 7, 2008 at 12:30 pm
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
May 7, 2008 at 1:25 pm
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?
May 7, 2008 at 10:59 pm
I have tested with rand() but its generating duplicate number . I need unique number for each transaction in my application thats why .
May 8, 2008 at 9:21 am
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
May 12, 2008 at 6:26 am
Thanks For your Reply
May 21, 2008 at 8:19 pm
What did you end up doing?
--Jeff Moden
Change is inevitable... Change for the better is not.
May 21, 2008 at 10:34 pm
Am using checksum(newid()) .....
May 21, 2008 at 10:50 pm
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
Change is inevitable... Change for the better is not.
May 29, 2008 at 10:17 am
select convert(bigint,HashBytes('SHA1', convert(varchar, getdate(),121)+convert(varchar,rand())))
this should be a bit unique:cool:
May 29, 2008 at 12:14 pm
I had some simple issue with converting newid() to a varchar. This could work
select convert(bigint,HashBytes('SHA1', convert(varchar(255), newid())))
and if you're paranoid
select convert(bigint,HashBytes('SHA1', convert(varchar(255), newid())+convert(varchar, getdate(),121) ))
May 29, 2008 at 9:26 pm
bcronce (5/29/2008)
select convert(bigint,HashBytes('SHA1', convert(varchar, getdate(),121)+convert(varchar,rand())))this should be a bit unique:cool:
It is, but not if you use it in a single select against a multi-row table to try to generate more than 1 number at a time.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply