This script has the advantages of being fast and not having any dependencies to existing tables so it can easily be used to generate a tally table, or a set of dimension keys. It becomes especially useful when used as the left part of a join (to control cardinality) or the left part of a cross apply (to replace a while loop for instance).
DECLARE@N INT; SET@N = 100000; SELECT TOP (@N) ROW_NUMBER() OVER(ORDER BY (SELECT 1)) N FROM master.sys.columns A CROSS JOIN master.sys.columns B CROSS JOIN master.sys.columns C
2013-08-22
16,467 reads
This query shows how to write a T-SQL statement that lists all numbers between 1 and 100 ordered, without use of any DATABASE table with only ONE select statement.
2012-02-20 (first published: 2011-12-31)
2,159 reads
This topic is nothing new, but well worth revisiting for those who have not used CROSS JOINS. You may have faced a problem where you need to compare historical records for a particular set of records to ensure that records have been saved in the correct order.
2009-01-28
4,596 reads
"Counting" is essential to many high performance code techniques. SQL Server MVP, Jeff Moden, shows us how to make sure that we're "Counting" and not "Crippling" our trick-code.
2011-08-08
21,145 reads
In the first part of a series on Tally Tables, Sioban Krzywicki shows how a Tally Table has helped out with fiscal year calculations.
2010-08-02
16,174 reads
