Alternate for select <sequence>.nextval from dual of Oracle in SQL Server?

Question

Is there any way to create a unique sequence no in SQL Server

select <sequence>.nextval from dual

We have the above code for Oracle which will create a unique number and the number cannot be used by any other.

In the same way how can I create a unique sequence number in SQL Server????

Some one help me in this

Answer 1

Only the upcoming SQL Server 2012 supports a sql standard conform sequence object. :-( In any current version of SQL Server (2008 R2 and below) you must use a column with the identity contstraint as in:

CREATE TABLE t1
(
   id       int identity(1,1),
   somecol  varchar(50)
);

This will generate values in the id column starting with 1 and incrementing them in steps of 1.

Answer 2

Sequences are being added in SQL Server 2012 - but this is not quite released as yet. The release candidates for the product has them in if you want to try them out in advance.

If the sequence is to just be used by a single table, then you can use the identity column feature, which allows you to specify a starting number and the value added per record.

If you are looking for a sequence that can be used across a number of tables, you would need to create a dedicated table within SQL server, that has a single column of an identity field - and whenever you wanted a .nextval, you would insert a row into the table to get a new number. The table than acts like a sequence. The table will fill up over time, there is no requirement to keep records within the table, the identity stores it's current value and increment to obtain the next value on a system table.