Oracle-like Sequence Generator

[adrianblunt]

I need to be able to retrieve a unique number from a sequential list from a SQL Server 2000 database (like the SEQUENCE object in Oracle).

There doesn't seem to be anything quite like this in SQL Server - I have read previous posts here, and all replies suggest using an IDENTITY fiels. However, this only generates a new number when adding new rows to a database table. I need something that generates a new number every time it is READ.

Thanks in advance.

Adrian.

 

[jhall156]

If you mean so that you can do the oracle equivalent of inserting the sequence object's nextval then you'll need to create a table to hold an integer column and then create a user defined function that returns the scalar value of the column and then increments that column. You can even mimic the use of the rollover capability and increment value capability of the sequence object. I started out with nothing, and I still have most of it.

 

[adrianblunt]

I was thinking along those lines as well - I was just hoping that there was an out-of-the-box solution I had overlooked!

Thanks for your help!