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:
NextSeq would return a unique integer over time. If I run the above query once on a table with 3 records I would get
When I run the same query a second time, I should get:
When I run the a different query on a different source table
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.
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...
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:
Hope this helps
answered Oct 23 '09 at 10:03 AM
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.
If SQL Server had a DML trigger for SELECT, you could have the trigger update the sequence number for you. But alas...
answered Oct 23 '09 at 10:12 AM
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.
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.
One advantage over using an IDENTITY column in the target table is that unlike IDENTITY the target column can be updateable.
answered Oct 25 '09 at 02:59 PM