x

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

I have commonly solved this with a stored procedure which inserts and into a table with identity column and reads the current identity. I need functionality like Oracle sequence that returns the next identity within a select statement; which I cannot do with the stored procedure.

For example I need:

select col1, col2, col3, NextSeq(), col4 from tblA

NextSeq would return a unique integer over time. If I run the above query once on a table with 3 records I would get

col1   col2   col3   -----   col4
 a       b      c      1       d
 a       b      c      2       d
 a       b      c      3       d

When I run the same query a second time, I should get:

col1   col2   col3   -----   col4
 a       b      c      4       d
 a       b      c      5       d
 a       b      c      6       d

When I run the a different query on a different source table

select col6, col7, col8, NextSeq() from tblB

col6   col7   col8   -----
 f       g      h      7  
 f       g      h      8  
 f       g      h      9

and so on....

This seems like a simple issue, but I have not found an elegant way to get it done in T-SQL. Any help is greatly appreciated.

Thanks!

I need to populate a unique numeric value (key) in target system with data culled from one or more source systems. The source systems data is aggregated in an operational data store, then various report-like queries update the target system.

The obvious 'correct' way to handle this problem is to have the target system handle the unique keys upon insert - however that is not an option here. The target system handles the unique key through it's UI, there is no way to change that.

I am going directly through a back end API which accepts XML as input. I am generating the XML directly from the source systems in T-SQL with for XML query. In a single insert I may add multiple line items.

The answer below with the insert/row_number/update would potentially work, and I have explored this option. I was hoping to find something which was more speedy as this 'sequence function' would probably be called several hundred thousand times over the course of a few minutes/seconds. This transactional insert/select/update may produce blocking issues which I was trying to avoid, but may be necessary to achieve the desired result.

Hope this makes sense...

more ▼

asked Oct 23, 2009 at 07:12 AM in Default

farflip gravatar image

farflip
21 1 1 2

Can you specify a bit more about why you need the sequence like that? There is probably a way to achieve what you need, but it would help if we could understand why you needed that sequence.
Oct 23, 2009 at 07:20 AM Matt Whitfield ♦♦
(comments are locked)
10|1200 characters needed characters left

4 answers: sort voted first

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

more ▼

answered Oct 23, 2009 at 10:03 AM

Frank Bazan gravatar image

Frank Bazan
51 2

(comments are locked)
10|1200 characters needed characters left

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

more ▼

answered Oct 23, 2009 at 10:12 AM

Jesse McLain gravatar image

Jesse McLain
106

Wrapping that in a transaction wouldn't stop it from returning the same value to multiple people running it at the same time. To do that, you would need to add something like UPDATE NextSeq SET @SeqStart = Number = Number WITH (HOLDLOCK) at the start.
Oct 24, 2009 at 05:35 PM Matt Whitfield ♦♦
(comments are locked)
10|1200 characters needed characters left

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
more ▼

answered Oct 24, 2009 at 11:47 AM

Bob Hovious gravatar image

Bob Hovious
1.6k 5 6 9

I added the why above under the original post.

Thanks!
Oct 24, 2009 at 03:41 PM farflip
(comments are locked)
10|1200 characters needed characters left

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.

more ▼

answered Oct 25, 2009 at 02:59 PM

David 1 gravatar image

David 1
1.8k 1 3

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x986
x57
x6

asked: Oct 23, 2009 at 07:12 AM

Seen: 4663 times

Last Updated: Oct 24, 2009 at 09:56 AM