CheeseMan316 (11/24/2008)
Adam Machanic (11/24/2008)
Just throw the CTE into an inline TVF. In my tests it almost always performs equally fast, and sometimes is even faster, than using a table.Adam Machanic: Have you ever tested the time it takes to build out the CTE with millions of numbers? I'm not sure how you use numbers tables, but we have had the need to have over 8M numbers. A physical table is faster.
Yes, I've done some pretty massive tests. You seem to be thinking about the time to insert the numbers into a table, not the time for the engine to internally produce and use the numbers; these are not the same. But if you're routinely dealing with over 8,000,000 numbers, it sounds like you have much bigger problems than performance of a CTE vs. a physical table... What are you actually using these things for?
--
Adam Machanic
whoisactive