random Number

  • How can i generate random unique number in sql server except newid() ?

  • Check out the RAND function in BOL.

  • 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

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

  • I have tested with rand() but its generating duplicate number . I need unique number for each transaction in my application thats why .

  • 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

  • Thanks For your Reply

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


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

  • Am using checksum(newid()) .....

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


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

  • select convert(bigint,HashBytes('SHA1', convert(varchar, getdate(),121)+convert(varchar,rand())))

    this should be a bit unique:cool:

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

  • 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


    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)

Viewing 13 posts - 1 through 12 (of 12 total)

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