Sequences are Oracle special database objects designed to produce values in sequence, starting with an initial value and incrementing by a given value. The defaults are to initialize the sequence at 1 and increment by 1. Although SQL Server uses the Identity column to obtain a similar functionality, Oracle sequences have two main advantages over the Identity column:
- SQL Server’s Identity column is linked to a specific table column, whereas an Oracle sequence is an independent entity.
- With SQL Server, you can use only one Identity column per table. With Oracle sequence objects, you can use any sequence to insert any value into any appropriate column in your table. Thus, you can use as many sequence columns as you need in a table.
Listing 1 shows the CreateSequence procedure, which uses T-SQL code to create a sequence. For example, to create a sequence called prod_seq that’s initialized at 1 and incremented by 1, you simply execute the code
exec sp_CreateSequence 'prod_seq'
Oracle also has two major sequence functions called CurrVal and NextVal. The CurrVal function gives the current value in a sequence, whereas the NextVal function produces the next value in a sequence. I used T-SQL code to implement similar operations. Listing 2 shows the T-SQL version of the CurrVal function. This function runs T-SQL’s IDENT_CURRENT statement, which obtains the current value in the specified sequence. Listing 3 shows a T-SQL stored procedure called NextVal that obtains the name of the target sequence and outputs the next value in that sequence. (NextVal can’t be a function because dynamic T-SQL code executes inside it.)
To call the CurrVal function and NextVal procedure for the prod_seq sequence, you’d use code such as
DECLARE @n int select dbo.CurrVal ('prod_seq') exec NextVal 'prod_seq',@n output print @n
Share your SQL Server discoveries, comments, problems, solutions, and experiences with products and reach out to other SQL Server Magazine readers. Email your contributions (400 words or less) to email@example.com. Please include your phone number. We edit submissions for style, grammar, and length. If we print your submission, you’ll get $50.