Adrian, I have nothing against those two approaches you listed. At a glance, they appear perfectly workable. Please understand that no techniques were deliberately excluded, other than by my desire to keep the article focused. Frankly, turning the topic into a comprehensive exploration of all possible coding techniques, or all potential variables is an effort I'm not prepared to make at this time.

I believe I mentioned in the article that other approaches were possible and that the intent was not to exhaustively compare all the possible solutions to one particular problem. Anyone seeking a "best" way to solve a particular problem can simply pose the problem in one of the forums and may stir up some lively debate.

The three approaches in the article illustrated that relative performance can vary with volume. Performance also varies due to a great many other factors such as indexing, partitioning, and parallelism; but longer articles than this one have been written that address those topics. I chose three techniques that told the story I felt new SQL developers needed to understand. The point was that SQL peformance is more than simply a matter of using one particular coding technique. Evidently I failed to communicate that as well as I hoped.

One thing though. I am learning to avoid the use of phrases like "normally", or "most people use", because that suggests that somewhere out there someone has taken an industry-wide survey about how people code in SQL. I know I haven't taken or even heard of such a survey. If anything, this article should encourage newcomers to test for themselves any technique they read or hear about. After all, confusion over unexpected results is something that drives us to learn and grow and deepen our understanding.

-------------------------------

Joe, your hambone story reminds me of something I read about legacy code containing undocumented quirks that were temporary workarounds for problems that were long since solved. No one ever goes back to clean them out and so they are perpetuated.

In another thread today, the OP finally found that the reason he wasn't getting the expected results from an index over a temporary table was that the DBA had shut off statistics for tempdb. He solved his problem by using a hint, but we encouraged him to go back and question the DBA's decision. Without statistics, he will be forever doomed to use hints, and even if they turn the statistics back on, those hints are liable to remain in place.