title: Generate sequence in SQL Server, poor performance with cross apply
body: <p>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. </p>
  
  <p>Basically it's a way of doing a for loop in a SQL statement.</p>
  
  <pre><code>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;
  </code></pre>
  
  <p>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</p>
  
  <pre><code>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
  </code></pre>
  
  <p>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?</p>
  
  <p>Thanks in advance,
  Michael</p>