Generate a unique number for a record

  • Esteban-786736

    SSC-Addicted

    Points: 400

    Hi,

    I'm trying to generate a unique id for every record ni a table. The tricky part is, each number is composed of

    'EN' + CURRENT YEAR + a unique 10 digits number.

    for example

    EN20085135485162

    EN20089452185694

    ...

    Each number is a unique id for the record.

    Any body done that before?

    Thanks in advance

    Bye

  • SwePeso

    SSC-Dedicated

    Points: 39693

    Add an IDENTITY column to the table, and make your "unique" column as a persisted computed column.

    Also see this topic

    http://www.sqlteam.com/article/custom-auto-generated-sequences-with-sql-server

    for more calculations on IDENTITY column.


    N 56°04'39.16"
    E 12°55'05.25"

  • Esteban-786736

    SSC-Addicted

    Points: 400

    Thanks a lot.

  • alogic11

    SSC Enthusiast

    Points: 105

    Esteban-786736 (8/14/2008)


    Hi,

    I'm trying to generate a unique id for every record ni a table. The tricky part is, each number is composed of

    'EN' + CURRENT YEAR + a unique 10 digits number.

    for example

    EN20085135485162

    EN20089452185694

    ...

    Each number is a unique id for the record.

    Any body done that before?

    Thanks in advance

    Bye

    :hehe:

  • parthi-1705

    SSCrazy Eights

    Points: 9277

    Try

    Select 'EN'+CONVERT(varchar,DATEPART(yy,getdate()))+

    CONVERT(varchar,datepart(mm,getdate())) +

    CONVERT(varchar,datepart(dd,getdate()))+

    CONVERT(varchar,datepart(HH,getdate()))+

    CONVERT(varchar,datepart(MI,getdate()))+

    CONVERT(varchar,datepart(SS,getdate()))+

    CONVERT(varchar,datepart(MS,getdate()))

    this will never repeat unless you change server time.

    Thanks
    Parthi

  • Michael Valentine Jones

    SSC Guru

    Points: 64818

    parthi-1705 (4/27/2011)


    Try

    Select 'EN'+CONVERT(varchar,DATEPART(yy,getdate()))+

    CONVERT(varchar,datepart(mm,getdate())) +

    CONVERT(varchar,datepart(dd,getdate()))+

    CONVERT(varchar,datepart(HH,getdate()))+

    CONVERT(varchar,datepart(MI,getdate()))+

    CONVERT(varchar,datepart(SS,getdate()))+

    CONVERT(varchar,datepart(MS,getdate()))

    this will never repeat unless you change server time.

    I'm not sure why you posted that on a 3 year old topic. :unsure:

    In any case, that will not guarantee a unique number, because two different sessions can run that code at the same time and get the same number.

  • Sean Lange

    SSC Guru

    Points: 286486

    Not to mention that most servers have the time changed twice a year. Daylight saving time. :w00t:

    _______________________________________________________________

    Need help? Help us help you.

    Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

    Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

  • consultingforce

    SSC Enthusiast

    Points: 101

    If you are seeking a unique number like in Oracle when you use a sequence (which I so wish SQL server had) you can do what I have done....

    Create a table that only has a date.

    Use scopeidentity to retrieve the value of the newly inserted date

    You now have a unique number

    What sux about SQL Server in the scopeidentity paradigm is that you must be performing an insert to retrieve it.....what if I want a number to link but I am not doing an insert? You are stuck like chuck.....

    Oracle nailed that one....create sequence SEQ_TableName; SEQ_TableName.nextval....you are there.

    B

  • SwePeso

    SSC-Dedicated

    Points: 39693

    consultingforce (9/15/2011)


    If you are seeking a unique number like in Oracle when you use a sequence (which I so wish SQL server had) you can do what I have done....

    Which is enabled in Denali.


    N 56°04'39.16"
    E 12°55'05.25"

  • Jeff Moden

    SSC Guru

    Points: 995164

    {Edit} Post withdrawn... wrong post.

    --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
    "Change is inevitable... change for the better is not."
    When you put the right degree of spin on it, the number 3|8 is also a glyph that describes the nature of a DBAs job. 😉

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

  • TomThomson

    SSC Guru

    Points: 104772

    Sean Lange (4/27/2011)


    Not to mention that most servers have the time changed twice a year. Daylight saving time. :w00t:

    Nor that the part that's supposed to be a 10 digit number will vary between 6 digits and 13 digits depending on the date and time. :w00t:

    Tom

  • TomThomson

    SSC Guru

    Points: 104772

    Michael Valentine Jones (4/27/2011)


    In any case, that will not guarantee a unique number, because two different sessions can run that code at the same time and get the same number.

    And two sessions running months apart can generate the same number too.

    For example, 20111211054170 is generated from both 2011-12-01T01:10:54:17.000 and 2011-01-21T01:10:54:17.000, and it's easy to see how the same sort of thing can hapen with minutes and hours, hours and days, and so on. Look at 2135433 - is that Feb 1 03:05:43.003 or Feb 13 05:04:03.003 or Feb 1 03:54:03.003, you can see the thing repeats all over the place.

    Tom

  • kadampatil.bk

    Newbie

    Points: 7

    Try This...
      SELECT 'AB99'+CAST(RIGHT('00000000' + CAST(ABS(CHECKSUM(NEWID())) % 99999999 AS VARCHAR(8)), 8) AS VARCHAR) AS RANDOM   

  • drew.allen

    SSC Guru

    Points: 76662

    kadampatil.bk - Wednesday, September 5, 2018 5:34 AM

    Try This...
      SELECT 'AB99'+CAST(RIGHT('00000000' + CAST(ABS(CHECKSUM(NEWID())) % 99999999 AS VARCHAR(8)), 8) AS VARCHAR) AS RANDOM   

    You do realize that this thread is ten years old and that the OP hasn't been active in seven years.

    Also, this does not guarantee a unique number.

    Drew

    J. Drew Allen
    Business Intelligence Analyst
    Philadelphia, PA

Viewing 14 posts - 1 through 14 (of 14 total)

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