How to create selectable 'Oracle-like' sequence functionality in T-SQL?

I think in order to do this you will need to hold the seed in a table somewhere.

The counting functionality can be achieved using the ROW_NUMBER() T-SQL function, but you'd need to know the max id from the last run.

So your sproc would look something like:

SET @MaxID = (SELECT Seed FROM MySeedTable)

SELECT Col1, Col2, ROW_NUMBER() + @MaxID FROM MyTableName

Hope this helps

You could create a one column, one row table to hold the sequence number, and then just select it as a column, as below. Then after the query update your sequence number table. You'll need to wrap this in a transaction if multiple people are running it simultaneously.

USE AdventureWorks;
GO

CREATE TABLE NextSeq (Number int)
INSERT INTO NextSeq (Number) VALUES (0)

SELECT "NextSeq" = (SELECT Number FROM NextSeq) + ROW_NUMBER() OVER (ORDER BY NEWID()), * 
FROM Person.StateProvince

UPDATE NextSeq SET Number = Number + @@ROWCOUNT

If SQL Server had a DML trigger for SELECT, you could have the trigger update the sequence number for you. But alas...

HTH, Jesse

I've come back to look at this three times, and I'm going to ask WHY you need this. What else are you doing besides selecting data from somewhere? Are you storing the results in some other table? I've never run across anything about sequencing that couldn't be handled with IDENTITY() or ROW_NUMBER() except for some bizarre problems that couldn't be resolved by set-based processing.

If you will give more detail than the SELECT statement, we may be able to suggest an approach that doesn't require the NEXTSEQ() function you are hoping for.

Thanks for the explanation. I understand the issue now.

A couple of suggestions, both of which regrettably involve temporary tables, or table variables.

First, if you can tolerate substituting a guid for an integer, consider using T-SQL function NEWSEQUENTIALID ( ). It can deliver both uniqueness and sequence, but unfortunately has to be part of a default constraint, therefore you have to create a #temp table.

Second, to avoid the blocking issues I would suggest this approach. As everyone suggested, have a single row table that gets updated with the next available sequence number. Obviously you will have to put a lock on it to avoid duplication, so update it with the RANGE of numbers you are about to use for a query, as shown below. It is inelegant because you have to save your result set in a temp table to get the row count, but on the other hand you aren't having to lock and update the SequenceControl table but once for however many rows you are processing within the transaction. Good luck.

--- initial code to create and set first value of sequence control
--create table SequenceControl (LastSeq int)
--insert into SequenceControl
--select 0


--------------------------------------------------------------------------
--------------------------------------------------------------------------
declare @rc int
declare @SC table (LastSeq int)

-- primary query:  store result set and get row count
-- (a table variable with an identity column would also work here to avoid logging)
select identity(int,1,1) as rowSeq,name
into #temp
from master..sysdatabases where name like 'A%'
set @rc = @@rowCount


-- reserve range of rows about to be used
update SequenceControl with(tablock)
    set LastSeq = LastSeq + @rc
    output deleted.LastSeq into @SC


-- return results with row numbers adjusted 
-- to start AFTER last sequence number

select lastSeq+rowSeq,name 
from #temp
cross join @SC


-- select * from SequenceControl

drop table #temp

Another method is to use an independent sequence table with an IDENTITY column to generate a starting value. Set the IDENTITY increment to a value large enough for the biggest batch of rows you want to insert.

DECLARE @NextVal INT;
INSERT INTO Seq DEFAULT VALUES; -- table contains only an IDENTITY column

SET @NextVal = SCOPE_IDENTITY();

INSERT INTO YourTable (col1, col2, col3, col4)
SELECT @NextVal+ROW_NUMBER() OVER (ORDER BY xxx), ...
FROM ... ;

One advantage over using an IDENTITY column in the target table is that unlike IDENTITY the target column can be updateable.