SQL Server get multiple next values for sequence

Question

In SQL Server you can select the next value from a sequence like this:

select next value for MY_SEQ

If you don't suppress the table from which to select, for every row the next value will be outputted:

select next value for MY_SEQ 
from MY_TABLE

[2020-09-08 15:47:34] 350 rows retrieved starting from 1 in 102 ms (execution: 62 ms, fetching: 40 ms)

How can I select the next n values for a sequence?

In Oracle this would look like that:

select MY_SEQ.nextval
from (
         select level
         from dual
         connect by level < 10
     )

I tried something like this:

select top(10) next value for MY_SEQ

But the result was:

[S0001][11739] NEXT VALUE FOR function cannot be used if ROWCOUNT option has been set, or the query contains TOP or OFFSET.

I guess I could create a temporary table with n rows and select from that, but this wouldn't be an especially elegant solution.

SteveC · Accepted Answer · 2020-09-08 13:59:37Z

12

I think you're looking for the system stored procedure 'sp_sequence_get_range'

To get the next 10 values from your sequence it would something like this

DECLARE @range_first_value_output sql_variant ;  
 
EXEC sys.sp_sequence_get_range  
@sequence_name = N'MY_SEQ'  
, @range_size = 10  
, @range_first_value = @range_first_value_output OUTPUT ;  
 
SELECT @range_first_value_output AS FirstNumber;

answered Sep 8, 2020 at 13:59

SteveC

6,0252 gold badges14 silver badges26 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

MetaColon Over a year ago

This still seems to return just one value

Frithjof Schaefer Over a year ago

It will return the first number of the requested range and "block" the next numbers until (FirstNumber + range_size-1). If you execute the above sql sereval times, you will see that the FirstNumber will exactly increase by the @range_size. Therefore, you got 10 values compressed to the startvalue. With the given range_size and the knowing of the sequences step (usually 1) you can safetly calculate the ids by yourself starting with FirstNumber

MtwStark · Accepted Answer · 2020-09-08 14:00:40Z

5

You can extract the right number of rows before to get the values for the sequence

DECLARE @N INT = 100;

SELECT next value FOR MY_SEQ 
FROM (
    SELECT 1 X
    FROM FN_NUMBERS(@N)
) X


CREATE FUNCTION [dbo].[FN_NUMBERS](
     @MAX INT
)
RETURNS @N TABLE (N INT NOT NULL PRIMARY KEY)  
BEGIN
     WITH
       Pass0 as (select '1' as C union all select '1'),       --2 rows
       Pass1 as (select '1' as C from Pass0 as A, Pass0 as B),--4 rows
       Pass2 as (select '1' as C from Pass1 as A, Pass1 as B),--16 rows
       Pass3 as (select '1' as C from Pass2 as A, Pass2 as B),--256 rows
       Pass4 as (select TOP (@MAX) '1' as C from Pass3 as A, Pass3 as B)    --65536 rows
       ,Tally as (select TOP (@MAX) '1' as C from Pass4 as A, Pass2 as B, Pass1 as C)  --4194304 rows
       --,Tally as (select TOP (@MAX) '1' as C from Pass4 as A, Pass3 as B)               --16777216 rows
       --,Tally as (select TOP (@MAX) '1' as C from Pass4 as A, Pass4 as B)               --4294836225 rows
     INSERT INTO @N
     SELECT TOP (@MAX) ROW_NUMBER() OVER(ORDER BY C) AS N
     FROM Tally
     RETURN
END

Instead of tally table you can simply use sys.objects

SELECT next value FOR MY_SEQ 
FROM (
    SELECT TOP (@N) 1 X
    FROM sys.objects o1, sys.objects o2, sys.objects o3
) X

edited Sep 8, 2020 at 14:00

answered Sep 8, 2020 at 13:58

MtwStark

4,0961 gold badge23 silver badges34 bronze badges

9 Comments

MetaColon Over a year ago

Therefore I'd need a table though which is guaranteed to have the number of rows I need, which doesn't seem elegant either

MtwStark Over a year ago

use a tally table

MetaColon Over a year ago

The option with the FN_NUMBESR function seems to work, but seems a bit overly complicated for the problem at hand

MetaColon Over a year ago

The solution with sys.objects seems to work fine and it makes sense that it should work most of the time, but it still seems quite hacky

MtwStark Over a year ago

you should always have a FN_NUMBERS in your DB, you will use it a lot. But you can simply use sys.objects many times to get same result

|

Marc Guillot · Accepted Answer · 2020-09-08 14:08:05Z

-2

Why don't you fill directly a table variable with the next values ?

declare @next_values table (next_value int)
declare @i int = 1

while @i <= 10 
begin
  insert into @next_values (next_value)
              select next value for MY_SEQ from MY_TABLE

  set @i = @i + 1
end

-- We return those values 
select * from @next_values

answered Sep 8, 2020 at 14:08

Marc Guillot

6,5431 gold badge23 silver badges54 bronze badges

2 Comments

MetaColon Over a year ago

I can't imagine it's especially performant to make n manual select queries

Marc Guillot Over a year ago

It's not a cursor, just a loop, I seriously doubt that you will see much of a difference calling n times next value from a loop instead of a select. You will still have to make the same n calls to that pseudo-function.

Collectives™ on Stack Overflow

SQL Server get multiple next values for sequence

3 Answers 3

2 Comments

9 Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

9 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related