All geek questions in one place

Creating a sequence in sql server

I am working on a function that will take a small number and a large number as parameters and return a table containing everything in between (and including).

I know that I could use the cursor and increment the variable by adding it to the table based on each individual iteration, but I would prefer, if possible, to avoid the cursor. Does anyone else have a suggestion for this? (As I am typing this, I think maybe CTE, which I will explore).

+4
source share
2 answers

Just create an indexed table of permanent helper numbers and do with it. This will lead to the execution of any other method.

See Jeff Moden for more details and a script to populate such a table. if for some reason this is not an option, this should beat the recursive CTE according to the performance tests in the linked answer.

WITH E00(N) AS (SELECT 1 UNION ALL SELECT 1), E02(N) AS (SELECT 1 FROM E00 a, E00 b), E04(N) AS (SELECT 1 FROM E02 a, E02 b), E08(N) AS (SELECT 1 FROM E04 a, E04 b), E16(N) AS (SELECT 1 FROM E08 a, E08 b), E32(N) AS (SELECT 1 FROM E16 a, E16 b), cteTally(N) AS (SELECT ROW_NUMBER() OVER (ORDER BY N) FROM E32) SELECT N FROM cteTally WHERE N BETWEEN 10 AND 20
+2
source

Yes, you can use recursive CTE for this. For example, to generate numbers from 10 to 20 inclusive:

 WITH f AS ( SELECT 10 AS x UNION ALL SELECT x + 1 FROM f WHERE x < 20 ) SELECT * FROM f
+4
source

Source: https://habr.com/ru/post/948850/

More articles:


All Articles