Difference between Temp Tables, Common Table Expressions and Table Variables in SQL Server


Temporary Tables

  • Remain until they are dropped or the connection that they were created in ends
  • Can have primary keys, constraints and indexes
  • Reside in memory (usually) – but can end up written in tempdb
  • Query optimizer can be used to analyze queries on them

Common Table Expressions

  • Are like temporary views
  • Can reference themselves
  • Similar functionality as Temp Tables
  • Written in memory

Table Variables

  • Created when variable declared
  • Exist in memory
  • Destroyed when query finishes
  • They don’t need to and cannot be dropped
  • Less efficient than temporary tables when working with thousands of rows
  • Not as efficient in sub selects or IN (SELECT . . . ) clauses

2 Comments

Filed under SQL

2 responses to “Difference between Temp Tables, Common Table Expressions and Table Variables in SQL Server

  1. will you please able to give some sample code of each..! and which is the preferable in what conditions? Thanks u!

  2. Pingback: Preparing for Exam 70-464: Developing Microsoft SQL Server 2012 Databases | Andrei Kosmynin

Comment on this

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