CODEWITHSUNDEEP
sql-serveroracle-databasesequence
Raymond
Raymond

Reputation: 296

Oracle sequence but then in MS SQL Server

In Oracle there is a mechanism to generate sequence numbers e.g.;

CREATE SEQUENCE supplier_seq

    MINVALUE 1
    MAXVALUE 999999999999999999999999999
    START WITH 1
    INCREMENT BY 1
    CACHE 20;

And then execute the statement

supplier_seq.nextval

to retrieve the next sequence number.

How would you create the same functionality in MS SQL Server ?

Edit: I'm not looking for ways to automaticly generate keys for table records. I need to generate a unique value that I can use as an (logical) ID for a process. So I need the exact functionality that Oracle provides.

Upvotes: 14

Views: 21977

Answers (10)

Dav.id
Dav.id

Reputation: 2797

This might have already been answered a long time ago... but from SQL 2005 onwards you can use the ROW_NUMBER function... an example would be:

select ROW_NUMBER() OVER (ORDER BY productID) as DynamicRowNumber, xxxxxx,xxxxx

The OVER statement uses the ORDER BY for the unique primary key in my case...

Hope this helps... no more temp tables, or strange joins!!

Upvotes: 1

Dheer
Dheer

Reputation: 4066

There is no exact match.

The equivalent is IDENTITY that you can set as a datatype while creating a table. SQLSERVER will automatically create a running sequence number during insert. The last inserted value can be obtained by calling SCOPE_IDENTITY() or by consulting the system variable @@IDENTITY (as pointed out by Frans)

If you need the exact equivalent, you would need to create a table and then write a procedure to retun the next value and other operations. See Marks response on pitfalls on this.

Edit:
SQL Server has implemented the Sequence similar to the Oracle. Please refer to this question for more details.

How would you implement sequences in Microsoft SQL Server?

Upvotes: 16

Loudenvier
Loudenvier

Reputation: 8794

If you are able to update to SQL Server 2012 you can use SEQUENCE objects. Even SQL Server 2012 Express has support for sequences. 

CREATE SEQUENCE supplier_seq
    AS DECIMAL(38)
    MINVALUE 1
    MAXVALUE 999999999999999999999999999
    START WITH 1
    INCREMENT BY 1
    CACHE 20;

SELECT NEXT VALUE FOR supplier_seq
SELECT NEXT VALUE FOR supplier_seq
SELECT NEXT VALUE FOR supplier_seq
SELECT NEXT VALUE FOR supplier_seq
SELECT NEXT VALUE FOR supplier_seq

Results in:

---------------------------------------
1

(1 row(s) affected)


---------------------------------------
2

(1 row(s) affected)


---------------------------------------
3

(1 row(s) affected)


---------------------------------------
4

(1 row(s) affected)


---------------------------------------
5

(1 row(s) affected)

Just take care to specify the right data type. If I hadn't specified it the MAXVALUE you've provided wouldn't be accepted, that's why I've used DECIMAL with the highest precision possible.

More on SEQUENCES here: http://msdn.microsoft.com/en-us/library/ff878091.aspx

Upvotes: 1

ivan.ukr
ivan.ukr

Reputation: 3551

Exactly because of this IDENT_CURRENT is not limited by scope and session; it is limited to a specified table. we need to use SCOPE_IDENTITY() because scope identity will give us unique number generated in our session, and uniqueness is provided by identity itself.

Upvotes: 0

bjan
bjan

Reputation: 2030

Not an exact answer but addition to some existing answers

SCOPE_IDENTITY (Transact-SQL)

SCOPE_IDENTITY, IDENT_CURRENT, and @@IDENTITY are similar functions because they return values that are inserted into identity columns.

IDENT_CURRENT is not limited by scope and session; it is limited to a specified table. IDENT_CURRENT returns the value generated for a specific table in any session and any scope. For more information, see IDENT_CURRENT (Transact-SQL).

It means two different sessions can have a same identity value or sequence number so to avoid this and get unique number for all sessions use IDENT_CURRENT

Upvotes: 0

Keith
Keith

Reputation: 131

Not really an answer, but it looks like sequences are coming to SQLServer in 2012.

http://www.sql-server-performance.com/2011/sequence-sql-server-2011/

Upvotes: 0

onupdatecascade
onupdatecascade

Reputation: 3366

Identity is the best and most scalable solution, BUT, if you need a sequence that is not an incrementing int, like 00A, 00B, 00C, or some special sequence, there is a second-best method. If implemented correctly, it scales OK, but if implemented badly, it scales badly. I hesitate to recommend it, but what you do is:

  1. You have to store the "next value" in a table. The table can be a simple, one row, one column table with just that value. If you have several sequences, they can share the table, but you might get less contention by having separate tables for each.
  2. You need to write a single update statement that will increment that value by 1 interval. You can put the update in a stored proc to make it simple to use and prevent repeating it in code in different places.
  3. Using the sequence correctly, so that it will scale reasonably (no, not as well as Identitiy :-) requires two things: a. the update statement has a special syntax made for this exact problem that will both increment and return the value in a single statement; b. you have to fetch the value from the custom sequence BEFORE the start of a transaction and outside the transaction scope. That is one reason Identity scales -- it returns a new value irrespective of transaction scope, for any attempted insert, but does not roll back on failure. That means that it won't block, and also means you'll have gaps for failed transactions.

The special update syntax varies a little by version, but the gist is that you do an assignment to a variable and the update in the same statement. For 2008, Itzik Ben-Gan has this neat solution: http://www.sqlmag.com/Articles/ArticleID/101339/101339.html?Ad=1

The old-school 2000 and later method looks like this:

UPDATE SequenceTable SET @localVar = value = value + 5 -- change the tail end to your increment logic

This will both increment and return you the next value.

If you absolutely cannot have gaps (resist that requirement :-) then it is technically possible to put that update or proc in side the rest of your trnsaction, but you take a BIG concurrency hit as every insert waits for the prior one to commit.

I can't take credit on this; I learned it all from Itzik.

Upvotes: 6

Vaccano
Vaccano

Reputation: 82361

I wish that SQL Server had this feature. It would make so many things easier.

Here is how I have gotten around this.

Create a table called tblIdentities. In this table put a row with your min and max values and how often the Sequence number should be reset. Also put the name of a new table (call it tblMySeqNum). Doing this makes adding more Sequence Number generators later fairly easy.

tblMySeqNum has two columns. ID (which is an int identity) and InsertDate (which is a date time column with a default value of GetDate()).

When you need a new seq num, call a sproc that inserts into this table and use SCOPE_IDENTITY() to get the identity created. Make sure you have not exceeded the max in tblIdentities. If you have then return an error. If not return your Sequence Number.

Now, to reset and clean up. Have a job that runs as regularly as needed that checks all the tables listed in tblIdentites (just one for now) to see if they need to be reset. If they have hit the reset value or time, then call DBCC IDENT RESEED on the name of the table listed in the row (tblMySeqNum in this example). This is also a good time to clear our the extra rows that you don't really need in that table.

DON'T do the cleanup or reseeding in your sproc that gets the identity. If you do then your sequence number generator will not scale well at all.

As I said, it would make so many things easier of this feature was in SQL Server, but I have found that this work around functions fairly well.

Vaccano

Upvotes: 1

Mark Brady
Mark Brady

Reputation:

As DHeer said there is absolutely no exact match. If you try to build your own procedure to do this you will invariably stop your application from scaling.

Oracle's sequences are highly scalable.

OK, I take it back slightly. If you're really willing to focus on concurrency and you're willing to take numbers out of order as is possible with a sequence, you have a chance. But since you seem rather unfamiliar with t-sql to begin with, I would start to look for some other options when (porting an Oracle app to MSSS - is that what you're doing)

For instance, just generate a GUID in the "nextval" function. That would scale.

Oh and DO NOT use a table for all the values, just to persist your max value in the cache. You'd have to lock it to ensure you give unique values and this is where you'll stop scaling. You'll have to figure out if there's a way to cache values in memory and programmatic access to some sort of lightweight locks- memory locks, not table locks.

Upvotes: 3

Frans Bouma
Frans Bouma

Reputation: 8357

make the field an Identity field. The field will get its value automatically. You can obtain the last inserted value by calling SCOPE_IDENTITY() or by consulting the system variable @@IDENTITY

The SCOPE_IDENTITY() function is preferred.

Upvotes: 5

Related Questions