Differences

This shows you the differences between two versions of the page.

Link to this comparison view

Both sides previous revision Previous revision
tsql:numbers_table [2017/07/21 21:47]
127.0.0.1 external edit
tsql:numbers_table [2019/08/15 06:35] (current)
admin
Line 1: Line 1:
 ====== Numbers table ====== ====== Numbers table ======
  
-A [[http://​www.sqlservercentral.com/​articles/​T-SQL/​62867/​|numbers table]] is a well-indexed,​ single-column table containing every positive integer in a given range. Numbers tables can be used to drive set-based approaches to problems that might naively be solved using less-efficient iterative methods, e.g.[[Sentence case formatting]].+A [[http://​www.sqlservercentral.com/​articles/​T-SQL/​62867/​|numbers table]] is a well-indexed,​ single-column table containing every positive integer in a given range. Numbers ​(sometimes called '​tally'​) ​tables can be used to drive set-based approaches to problems that might naively be solved using less-efficient iterative methods, e.g.[[Sentence case formatting]].
  
 ===== Creation ===== ===== Creation =====
Line 14: Line 14:
 INSERT INTO dbo.Numbers ( N )    ​ INSERT INTO dbo.Numbers ( N )    ​
 SELECT TOP 32000 SELECT TOP 32000
-  ROW_NUMBER() OVER( +  ROW_NUMBER() OVER(ORDER BY c1.[object_id]
-    ​ORDER BY c1.object_id ​ +FROM sys.all_columns ​c1 
-FROM sys.columns ​c1 +  CROSS JOIN sys.all_columns  ​c2
-  CROSS JOIN sys.columns ​c2+
  
 GRANT SELECT, REFERENCES ​ GRANT SELECT, REFERENCES ​
Line 51: Line 50:
   SELECT TOP 100000   SELECT TOP 100000
     0     0
-  FROM sys.columns ​c1 +  FROM sys.all_columns ​c1 
-    CROSS JOIN sys.columns ​c2+    CROSS JOIN sys.all_columns ​c2
  
 END END
Line 65: Line 64:
 ON dbo.BigNumbers ​ ON dbo.BigNumbers ​
 TO [public] TO [public]
 +</​code>​
 +
 +===== Numbers CTE =====
 +If you don't want a permanent numbers table you can derive a list of numbers on-the-fly in a CTE:
 +<code tsql>
 +WITH n AS (
 +  SELECT TOP 32000
 +    ROW_NUMBER() OVER(ORDER BY c1.[object_id]) AS N
 +  FROM sys.all_columns c1
 +    CROSS JOIN sys.all_columns ​ c2
 +)
 +SELECT *
 +FROM n
 +</​code>​
 +
 +You can generate numbers without a table source using a recursive CTE (from https://​sqlhints.com/​tag/​maxrecursion-sql-server/​):​
 +<code tsql>
 +WITH n AS (
 +  SELECT ​ 1 AS N
 +  UNION ALL
 +  SELECT N + 1 FROM n  ​
 +  WHERE N < 32000
 +)
 +SELECT * FROM n
 +OPTION (MAXRECURSION 0)
 </​code>​ </​code>​
  
 {{tag> tsql development}} {{tag> tsql development}}
 ~~DISCUSSION~~ ~~DISCUSSION~~