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

sql-server oracle unique-id
smilu picture smilu · Jan 23, 2012 · Viewed 10.7k times · Source

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

Andrew picture Andrew · Jan 23, 2012

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.

Related questions

How do I list all the columns in a table?

For the various popular database systems, how do you list all the columns in a table?

Read More
Is there a combination of "LIKE" and "IN" in SQL?

In SQL I (sadly) often have to use "LIKE" conditions due to databases that violate nearly every rule of normalization. I can't change that right now. But that's irrelevant to the question. Further, I often use conditions like WHERE something …

Read More
Can a foreign key be NULL and/or duplicate?

Please clarify two things for me: Can a Foreign key be NULL? Can a Foreign key be duplicate? As fair as I know, NULL shouldn't be used in foreign keys, but in some application of mine I'm able to input …

Read More