# Generate N sequential numbers (fast)

• Comments posted to this topic are about the item Generate N sequential numbers (fast)

• For a limited number of rows (< 100) using a recursive common table expression is an alternative:

`DECLARE @lowerbound int`

`DECLARE @increment int`

`DECLARE @upperbound int`

`SET @lowerbound = -5`

`SET @upperbound = 90`

`SET @increment = 3`

`;`

`WITH seq AS (`

`SELECT @lowerbound AS n `

`UNION ALL`

`SELECT seq.n + @increment AS n`

`FROM seq INNER JOIN (SELECT NULL AS a) a ON`

`n < @upperbound`

`)`

`SELECT n FROM seq`

Note that due to recursion, the number of returned rows may not exceed 99.

Jan-Willem Lankhaar

• jw.lankhaar (6/13/2013)

For a limited number of rows (< 100) using a recursive common table expression is an alternative:

`DECLARE @lowerbound int`

`DECLARE @increment int`

`DECLARE @upperbound int`

`SET @lowerbound = -5`

`SET @upperbound = 90`

`SET @increment = 3`

`;`

`WITH seq AS (`

`SELECT @lowerbound AS n `

`UNION ALL`

`SELECT seq.n + @increment AS n`

`FROM seq INNER JOIN (SELECT NULL AS a) a ON`

`n < @upperbound`

`)`

`SELECT n FROM seq`

Note that due to recursion, the number of returned rows may not exceed 99.

Jan-Willem Lankhaar

`DECLARE`

`@lowerbound INT,`

`@increment INT,`

`@upperbound INT`

`SELECT`

`@lowerbound = -5,`

`@upperbound = 2800000,`

`@increment = 3;`

`SELECT x.Start + y.Inc`

`FROM [dbo].[InlineTally] (1000000) t`

`CROSS APPLY (SELECT Start = (t.n+@lowerbound-1)) x`

`CROSS APPLY (SELECT Inc = (t.n-1)*(2)) y`

`WHERE x.Start + y.Inc <= @upperbound;`

`-- (933,336 row(s) affected) / 00:00:05`

`WITH seq AS (`

`SELECT n = @lowerbound`

`UNION ALL`

`SELECT seq.n + @increment`

`FROM seq`

`WHERE n < @upperbound`

`)`

`SELECT n FROM seq OPTION(MAXRECURSION 0);`

`-- (933,336 row(s) affected) / 00:00:12`

“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

• Did you run any performance tests with this? It LOOKS simpler to the eye and at first I thought it looked really good--and it still may be a better alternative than the Itzik Ben-Gan algorithm. I ran some quickie tests with both by plugging your new code into a tally table itvf I use frequently which uses the Itzik method. Then I ran a very simple function that stepped through random characters in strings of varying length. Just something to make a comparison.

I plan on doing some more testing but it will probably be next week if I'm to do it right with some good functions. If you have any ideas for some functions/scripts that might give your model a good test, please pass them along. I've been working on creating a testing methodology that will (I hope) be easy enough to use so that any promising ideas like yours can be put to the test.

Now things might change with different applications, but the bad news at the moment is that old Itzik still seems to have a slight advantage. I've attached a screenshot of the intial test runs.

• Brian

It looks like you have a winner! I ran a test of DelimitedSplit8K using your method against 3 other methods including the Itzik Ben-Gan method. Your version consistently outperformed the Itzik method--not by much, but clearly a slight edge based on the numbers on my machine. The test results might be different on different servers of course.

The test results are attached.

Good work!

Steven Willis

• jw.lankhaar (6/13/2013)

For a limited number of rows (< 100) using a recursive common table expression is an alternative:

Please read the following article for why you should probably never use an rCTE for such a thing.

http://www.sqlservercentral.com/articles/T-SQL/74118/

Note that due to recursion, the number of returned rows may not exceed 99.

Careful now. It is true that the default for rCTEs is a max of 100 but that's easy to override using OPTION(MAXRECURSION x) where "x" can be any positive integer from 0 to 32,767 and "0" means "unlimited".

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

• Steven Willis (6/22/2013)

Brian

It looks like you have a winner! I ran a test of DelimitedSplit8K using your method against 3 other methods including the Itzik Ben-Gan method. Your version consistently outperformed the Itzik method--not by much, but clearly a slight edge based on the numbers on my machine. The test results might be different on different servers of course.

The test results are attached.

Good work!

Steven Willis

Which version of the DelimitedSplit8K?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

• Jeff Moden (8/15/2013)

Steven Willis (6/22/2013)

Brian

It looks like you have a winner! I ran a test of DelimitedSplit8K using your method against 3 other methods including the Itzik Ben-Gan method. Your version consistently outperformed the Itzik method--not by much, but clearly a slight edge based on the numbers on my machine. The test results might be different on different servers of course.

The test results are attached.

Good work!

Steven Willis

Which version of the DelimitedSplit8K?

Good guestion...there seems to have been some revisions and I may have used an older one? I'll have to do some research and let you know.

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply