#Tables, Table Variables, CTE’s – what’s the difference?


Here is a handy table that explains some of the differences between the three. The full sized version is available here.

I’d say that in the past I’ve used Table Variables about 60 to 70% of the time when I’ve needed some form of temporary data. With Common Table Expressions (CTE) in SQL server 2005 though, I think I’ll be using them a lot less. Note, the above table is for guidance only. I’d recommend that if you need to use Temporary Tables or Table Variables, you test the performance of your code using both.

Note, recompilations hurt performance. In SQL Server 2000, recompilation was handled at a procedure level. In SQL server 2005, this happens at a statement level. Here is a great article on Batch Compilation, Recompilation, and Plan Caching Issues in SQL Server 2005

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s