DannyC wrote:My research has led me to understand that table variables perform well when the data is somewhat static and there isn't many rows, just as you say. However the broad rule would be around 100 rows, not 10,000 rows. But that's what I've read and I haven't done any conclusive tests to prove or disprove that theory.
I just ran the tests published in this
article on a SQL 2014 version of the AdventureWorks database available
here (because that's the database the article tested with).
I had similar results to the author - the 19,127 rows returned by the query took pretty much the same time for temp tables, CTE's and table variable queries.
CTE | Temp Table | Table Variable |
760 | 820 | 810 |
Figures are in milliseconds - average of 10 runs.
And when a WHERE clause limits the results to just one row:
CTE | Temp Table | Table Variable |
6 | 22 | 3 |
Note that in the last result the CTE figure of 6 is a little misleading - the first execution took 60ms, and the next 9 executions took 0ms averaging to be 6ms. Table variables had similar results - but not as accentuated - 16ms for the first execution, then 1ms for 8 executions and 4ms for one of the executions resulting in an average of 3ms. Temp tables was pretty consistent for each execution.
The conclusion I'm drawing there is CTE and Table variables cache their results more readily for smaller result sets - but that's only of benefit when you get cache hits - in a fluid environment the cached results might be purged before you get a cache hit, and then CTE and table variables offer WORSE performance than temp tables even on a small result set!