Temporary Tables vs. Table Variables and Their Effect on SQL Server Performance September 13th, 2007 Original http://www.sqlsolutions.com/articles/ar......
In some cases, SQL 2005 was much faster then SQL 2000 (marked with green). But in many cases, especially with huge amounts of data, procedures that used table variables took much longer (highlighted with red). In 4 cases, I even gave up waiting.
Conclusion:
1. There is no universal rule of when and where to use temporary tables or table variables. Try them both and experiment.
2. In your tests, verify both sides of the spectrum - small amount/number of records and the huge data sets.
3. Be careful with migrating to SQL 2005 when you use complicated logic in your stored procedures. The same code can run 10-100 times slower on SQL server 2005!
|
