Can you create custom sequences in SQL Server 2000 like in Oracle

  • i want a column in one of my tables to increment automatically when i insert a new record into it. I'm fairly new to SQL Server, so i was wondering if you can create a sequence like in Oracle. If anyone can assist me in this problem i would be grateful.

  • Have a look at the IDENTITY property In SQL Server's Books Online.

    --
    Frank Kalis
    Microsoft SQL Server MVP
    Webmaster: http://www.insidesql.org/blogs
    My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

  • there no sequence objects in SQL Server

    but you can find Identity type in the SQL Server

    in the Enterprice Manager,select int as the data type then set Identity property to yes, you can define the Seed and the increment as you want




    My Blog: http://dineshasanka.spaces.live.com/

  • I personnaly prefer SQLServer's Identity to Oracle's (DB2's, PGSQL's...) sequence. Because a sequence is an "external" object to a table, what you have to create separatly, while SQLServer's identity is built'in the table, with a drawback, you can have only 1 identity column per table



    Bye
    Gabor

  • The advantage of a sequence is that it can be used for more than one table.


    Kindest Regards,

    Gimli

    If you are a spineless creature, they can't break you

  • Sure. But is it a real life scenario?

    I mean why would you use the same sequence (value) in several table.

    IMHO, but I may be wrong, a unique identifier or a counter makes sense if this is more or less consecutiv.

    I know that there is no possibility to ensure with the identity that there will be no gap in the numbering, especially in a multi user environment, where transaction could be rolled back, but it is still quite conveniant way tu have a sequenced primary key



    Bye
    Gabor

  • I'm working at an insurance company. Car insurance and travel insurance are different tables, but account numbers are unique. Okay, we resolved it another way, because we have DB2, SQLServer, Oracle, etc. But, it is an example. I think there are a lot of other examples.

    Bye Gimli


    Kindest Regards,

    Gimli

    If you are a spineless creature, they can't break you

Viewing 7 posts - 1 through 6 (of 6 total)

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