Everett T (8/2/2010)
A CTE could also be used to generate a tally table in memory. This can come in handy if you don't have access to create a table or don't wish to create a table for short-term use.
WITH Tally
AS
(
SELECT
TOP 20000
ROW_NUMBER() OVER (ORDER BY SC1.name) AS N
FROM
master.dbo.syscolumns AS SC1
CROSS JOIN master.dbo.syscolumns AS SC2
)
Sure, or you could just use a temp table or table variable. Still no need to use a CTE to do it either. Heck, if you really wanted to leave no long-term footprint, just make both your tally table and your date table temp tables and drop them when you're done. Or make them both table variables, which is harder to use if you want to keep adding bits and testing them as you add them, but still works.
--------------------------------------
When you encounter a problem, if the solution isn't readily evident go back to the start and check your assumptions.
--------------------------------------
It’s unpleasantly like being drunk.
What’s so unpleasant about being drunk?
You ask a glass of water. -- Douglas Adams