I think the performance gains are obtained when the stored procedure updates/interferes with the result set.
In that case, the performance gains can be significative.
I thought SQL Server Central was a place where developers posted original authorship. Clearly if this were original authorship, the author would have done his due diligence and figured out that his procedure code was not working efficiently due in part to TempDB filling up. Moreover, the author would highlighted the margin of diminishing returns with respect to using TempDB versus memory as a means of gaining response times to the caller functions in his alleged plagiarized article. In fact, the author of this article could have made mention as to the source of his findings and perhaps pointed his readers to said source. For example, what title/publisher or what Url, or who the original author was that this author copied, pasted, and called-his-own this age-old technique.
I concur with your statement of "I don't like cursors so I don't use them". It is an empty and unsupported posit. It is my understanding that if your procedure code is not running fast (which by the way is a relative term) due to alleged cursor code, then perhaps the database server’s RDBMS is not getting the horsepower it needs like memory, larger hard-disk, or a stronger processor.
There are cost-benefit trade-offs all over the place. If a developer is cursor-centric in his procedure creations but said procedures do not perform “fast-enough”, what is “fast-enough” ? Sure, a code review by another set of eyes might help point out some glaring in-efficiencies, but if “not fast-enough” means having to rewrite something at a cost that is less than cost of attaining more hardware, then so be it, attempt to reach that margin of diminishing return in terms of speed, rewrite your code.
I liken the analogy to the number of people attempting to enter a very small automobile. If you can only fit five persons but need to fit ten, are you going to cram five more persons or are you going to get a larger automobile?
I was doubtful of the solution posed in this artcle also, so I did my own tests. I got similar results as found by bobjbian. The static cursor beat all comers for larger result sets. As the result set got smaller, the times between the 3 styles got closer. I suppose for very small result sets (<100 rows?) that the temp table might have less overhead than a cursor, but it's only marginal.
No doubt the SP called by the article performed an update to the underlying table causing the cursor to to be updated. So while the table variable may have helped the performance, so too would a STATIC cursor. And the STATIC cursor would have been faster than the table variable.
Actually, this is kind of funny as I've been using this approach for a couple of years and not thought to much of it so as to comment to the world ... Since my background is in software development - namely c, c++, Ruby, Lisp and other syntactically succinct languages, I felt driven to look for a way to get around the clunky syntax of the cursor declaration, as well as the issue that the reference variable appears differently in code than a standard variable in that the @ is not present and the parser uses this "syntactical difference" as a class differentiator. While there is a little more code, it is also more "readable" and therefore leans toward meeting goals of self-documentation.
The "next one" in line with this (and I have not looked to see if there are any postings to this effect already), is using this technique for dynamic iteration of tables using sys information – which has a few different caveats. If there is any desire for this, let me know and I can post example code and ancillary discussion. One benefit to this is constructing queries on the fly where you do not want to have to hard code in order to get the efficiency of using the column names (as opposed to using the * operator, which causes performance degradation in testing). This enables you to create “template” procedures more readily. There are other advantages as well.