Post reply

How to generate new sequence number in sql server.

  • February 17, 2012 at 3:16 am

    #256884

    Hi,

    Is there any function/syntax in sql server like "nextval" for generating new sequence number.

    How to generate new sequence number every time in sql server.

    Regards,

    Kiran

  • February 17, 2012 at 3:24 am

    #1448375

    Hi,

    Perhaps I'm not understanding correctly, but you should be able to take your pick of;

    Identity columns - Your table can increment itself automatically.

    ROW_NUMBER - You can assign a row number based on a partition and order.

    You could even do a MAX and add 1 to it if you wanted to, it depends on the context you are using it in.

    If you give us some more information as to what you are trying to acheive and why then we can hopefully provide a more detailed answer.

    Nic

  • February 17, 2012 at 3:28 am

    #1448378

    1. If you want an auto generated column for your table then you can use IDENTITY property.

    CREATE TABLE TestTable

    (

    IDColumn INT IDENTITY(1,1),

    DataColumn1 VARCHAR(20),

    DataColumn2 VARCHAR(20)

    )

    2. If you want to generate sequence numbers in a query result then you can use ROW_NUMBER() function.

    SELECT

    ROW_NUMBER() OVER (ORDER BY DataColumn1) AS SeqNo,

    DataColumn1, DataColumn2

    FROM TestTable

    WHERE DataColumn2 = 'SomeValue'

    Sujeet Singh

  • February 17, 2012 at 3:32 am

    #1448379

    Hi,

    thanks for the reply.

    your are correct.

    we have function in sql server IDENT_current but its giving the current value from the table

    But i have a requirement to insert a new row with new ID and ID should be unique.

    and I have to generate a new values by using function like "nextval" as like in the oracle.

    "nexval" is exists in the oracle so my concern is is there any function is in the sql server to generate new ID(as like sequence number) every time while inserting the rows from the front end(like front report) to the table?

    Regards,

    Kiran

  • February 17, 2012 at 3:40 am

    #1448383

    kiran.rajenimbalkar (2/17/2012)

    is there any function is in the sql server to generate new ID(as like sequence number) every time while inserting the rows from the front end(like front report) to the table?

    Yes, Divine Flame's first option will provide exactly what you require. Make sure you put a unique constraint on the ID column, as well.

    John

  • February 17, 2012 at 6:49 am

    #1448462

    kiran.rajenimbalkar (2/17/2012)

    and I have to generate a new values by using function like "nextval"

    Sequences as what you speak of will be available in SQL Server 2012. They are not available in SQL Server versions before that.

    Let me ask so we can actually give the correct answer to your problem. WHY is the business requirent to use a function instead of an autonumbering column?

    --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 6 posts - 1 through 5 (of 5 total)

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