title: Selecting N rows in SQL Server
body: <p>Following query will return 1-10 in 10 rows.</p>
  
  <pre><code>DECLARE @Range AS INT = 10
  
  ;WITH CTE AS(
      SELECT TOP (@Range) Duration = ROW_NUMBER() OVER(ORDER BY OBJECT_ID)
      FROM sys.all_columns
      ORDER BY [Object_id]
  )
  SELECT Duration from CTE
  </code></pre>
  
  <p>But when I set @Range as 10000 it returns 7374 rows. Why this query can't return more than 7374 rows.</p>
  
  <p><strong>UPDATE</strong></p>
  
  <p>I just found another way to achieve my requirement as following</p>
  
  <pre><code>DECLARE @start INT = 1;
  DECLARE @end INT = 10;
  
  WITH numbers AS (
      SELECT @start AS number
      UNION ALL
      SELECT number + 1 
      FROM  numbers
      WHERE number &lt; @end
  )
  SELECT *
  FROM numbers
  OPTION (MAXRECURSION 0);
  </code></pre>
  
  <p>Without last line of code it breaks with error Maximum recursion 100 has been exhausted before statement completion and I found this line is specifying 0 for infinite recursion. But this query seems a little slower to me. Is there any faster way???</p>