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.
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