Temporary Tables in SQL - a Training Blog
Part four of a four-part series of blogs

If you want training in how to use temporary tables in SQL, read on! This blog shows how to create temporary tables and how to drop them safely whether or not they exist. Finally, the blog lists the pros and cons of using temporary tables in SQL.

  1. Temporary Tables in SQL Server - a Training Blog
  2. Where Temporary Tables are Stored - TEMPDB Database
  3. Dropping Temporary Tables - Object_Id or BEGIN TRY
  4. Pros and Cons of Temporary Tables (this blog)

This article is part of a complete SQL tutorial.  Another way to learn SQL is to attend a Wise Owl SQL training course.

Posted by Andy Brown on 20 October 2011

You need a minimum screen resolution of about 700 pixels width to see our blogs. This is because they contain diagrams and tables which would not be viewable easily on a mobile phone or small laptop. Please use a larger tablet, notebook or desktop computer, or change your screen resolution settings.

Pros and Cons of Temporary Tables

As our final part of this blog covering training on temporary tables, what are the advantages and disadvantages of using temporary tables?

Advantages of Temporary Tables

There are 3 main advantages of using temporary tables in SQL:

Advantage Notes
Simplicity of coding Temporary tables behave just like normal ones; you can sort, filter and join them as if they were permanent tables.
Speed Because SQL Server has less logging and locking overheads for temporary tables (after all, you're the only person who can see or use the temporary table you've created), they execute more quickly.
Access rights/security You can create a temporary table and insert, delete and update its records without worrying about whether you have sufficient rights to change data in permanent tables, or whether you might be accidentally doing so.

Disdvantages of Temporary Tables

The disadvantages of using temporary tables include:

Advantage Notes
Not as fast as table variables Although using temporary tables is quicker than using permanent ones, there is still an overhead involved compared to using table variables (an alternative technique, which I might blog about some day!).
Can not update in functions You can not use INSERT, UPDATE or DELETE statements against temporary tables in user-defined functions (you can with table variables). This is NOT a big disadvantage, however!
This blog has 0 threads Add post