Generate sequence in SQL Server, poor performance with cross apply

I obtained the following code from the web many years ago and it has served my very well. It is simply a function that generates a sequence of numbers from 1 to whatever you pass in.

Basically it’s a way of doing a for loop in a SQL statement.

CREATE FUNCTION [SequenceCreate]
(@MaxValue INT)
RETURNS TABLE
AS

RETURN
WITH
Num1 (n) AS (SELECT 1 UNION ALL SELECT 1),
Num2 (n) AS (SELECT 1 FROM Num1 AS X, Num1 AS Y),
Num3 (n) AS (SELECT 1 FROM Num2 AS X, Num2 AS Y),
Num4 (n) AS (SELECT 1 FROM Num3 AS X, Num3 AS Y),
Num5 (n) AS (SELECT 1 FROM Num4 AS X, Num4 AS Y),
Num6 (n) AS (SELECT 1 FROM Num5 AS X, Num5 AS Y),
Nums (n) AS
(SELECT ROW_NUMBER() OVER(ORDER BY n)
FROM Num6)
SELECT n AS [Value] FROM Nums
WHERE n BETWEEN 1 AND @MaxValue;

This generally works very well and is fast but I have found its performance to be very poor when using a cross apply statement, eg

DECLARE @T TABLE(StartNum INT, ItemCount INT)
INSERT INTO @T VALUES (100, 5)
INSERT INTO @T VALUES (110, 7)
INSERT INTO @T VALUES (55, 3)

SELECT Seq.Value + StartNum FROM @T
CROSS APPLY he.SequenceCreate(ItemCount) AS Seq

This is very slow on my machine. Does anyone know why it works fine when executed once but runs very badly when executed 3 times via cross apply? Even if the @T table contains only 1 row the performance is still terrible. Is there a better way to write this?

Thanks in advance, Michael

Answer

The query optimizer figures it is better to only execute the function once and then use the result in a join to get the rows you want. It is done like that because your function is an inline table valued function. If you instead make your function a multi-statement valued function it will execute the function once for each row in your source table. However I would recommend that you create a numbers table instead as suggested by Igor.

CREATE FUNCTION [SequenceCreate]
(@MaxValue INT)
RETURNS @T TABLE ([Value] INT NOT NULL PRIMARY KEY)
AS
BEGIN

  WITH
  Num1 (n) AS (SELECT 1 UNION ALL SELECT 1),
  Num2 (n) AS (SELECT 1 FROM Num1 AS X, Num1 AS Y),
  Num3 (n) AS (SELECT 1 FROM Num2 AS X, Num2 AS Y),
  Num4 (n) AS (SELECT 1 FROM Num3 AS X, Num3 AS Y),
  Num5 (n) AS (SELECT 1 FROM Num4 AS X, Num4 AS Y),
  Num6 (n) AS (SELECT 1 FROM Num5 AS X, Num5 AS Y),
  Nums (n) AS
  (SELECT ROW_NUMBER() OVER(ORDER BY n)
  FROM Num6)
  INSERT INTO @T
  SELECT n AS [Value] FROM Nums
  WHERE n BETWEEN 1 AND @MaxValue;

  RETURN
END