MSSQL Table Variable Vs Temp Tables

Hello World!

So I had been living in misconception-land and like a lot of people, I though Table variables in MSSQL were oh-so-great ’cause they did not involve a physical IO in tempdb. As it turns out this is completely WRONG! Both table variables and temp tables use tempdb. (SQL Server 2014 differs, more on this later)

While discussing this with a colleague, I thought that this is the best opportunity to write this “note-to-self” so that once and for all, I do not keep using table variables based on the misunderstanding above – more importantly, I do not want to be the source of wrong information to someone working his/her way up in the world of SQL.

There is no general rule that guides the choice of one over the other, but I found this excellent discussion on  Stackoverflow that lists some decision points that can help one analyze the problem at hand and make an informed choice. To summarize, my understanding is that:

  1. The amount of data and the operations (including the number of those operations) really decide whether a table variable or temp table solution is optimal
  2. For large volume of data, temp tables provide better performance as the query optimizer can generate better plans
  3. If indexing is required, it will have to be temp tables (although SQL 2014 provides some specific types of indexing on table variables as well)
  4. If you need to send a table as a parameter to a stored procedure, the type of parameter would be table – you are going to have read only access to this table

Further reading:

Advertisements
Tagged with:
Posted in .NET, MSSQL

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

  • Comic for July 25, 2017
    Dilbert readers - Please visit Dilbert.com to read this feature. Due to changes with our feeds, we are now making this RSS feed a link to Dilbert.com.
%d bloggers like this: