Temporary Tables – Part 4

Today, I am going to talk about regular tables in tempdb. Please check out the Microsoft white paper on working with tempdb for a detailed discussion.

The advantage of creating a table in tempdb is that it is persistent until the server is rebooted. Upon startup of SQL Server, tempdb is created again from scratch. One use of this technique is to have startup procedures create tables in tempdb that are used by an application program. One can leverage the fact that operations within tempdb are minimally logged.

The major downside of using tempdb is that everything else including the database engine does. This includes work tables used by queries, the version store for optimistic concurrency, and any other temporary objects. I want to cover this topic since there are many programs out using regular tables in tempdb.

Some of the issues that you might encounter with using tempdb are the following:

  1. Space issues when large queries execute
  2. Performance issues when large queries execute
  3. System tables locked by queries

Ways to mitigate these issues is to make sure your tempdb is size appropriately with the number of data file groups equal to the number of processors. Trace flag 1118 can be used to grab segments of memory instead of pages which will further reduce issues.

With the about cautions listed out, I am going to use the car ownership table for person A to demonstration how to work with regular tables in tempdb. Please see the tiny AUTOS database I created in earlier article.

The code snipet below creates regular tables in tempdb that persist until SQL Server is rebooted.

I am going to use an INSERT statment to populate the table with sample data.

To really make sure the data is there, I am going to use a SELECT statement to view the contents of the table.

Regular tables in tempdb would not be very useful if we can not perform UPDATE and DELETE operations on the records. The code below deletes the third record from the table, updates the fifth record with new data, and shows the results.

Last but not least, it is just good practice to clean up after yourself. The code below looks for the
table in tempdb. If it exists, it drops the table.

I will be using the Adventure Works Sample database supplied by Microsoft during the rest of this talk.

A common query pattern is to get the most recent id by date. Joining these results to the original table will bring back the most recent record by date.

The SELECT query below stores the last sales order by customer id as a regular table in tempdb called MC. The main query joins to this table to return customer name, sales id, sub-total and order date. This might be a query for the SALES team to cold call customers that have not ordered in the past x months.

Creating regular tables in tempdb allows them to persist between sessions. This is a benefit if several programs access the common table. The minimal logging feature makes them attractive. However, a word of caution since tempdb is the workspace of the database engine.

Unless performance is extremely critical, I suggest using a staging database that uses SIMPLE recovery. This allows the log file to be truncated at check points while the impact on tempdb, and the overall database system is minimized. Load data into heaps, tables without indexes, and then add indexes for fastest execution times.

Next time, I will be talking about Common Table Expressions (CTE).

Related posts

4 Thoughts to “Temporary Tables – Part 4”

  1. A formidable share, I simply given this onto a colleague who was doing a little bit analysis on this. And he in fact purchased me breakfast as a result of I found it for him.. smile. So let me reword that: Thnx for the treat! But yeah Thnkx for spending the time to debate this, I feel strongly about it and love reading extra on this topic. If possible, as you turn into experience, would you thoughts updating your blog with extra details? It is extremely helpful for me. Massive thumb up for this blog submit!

  2. I like this web blog its a master peace ! .

  3. Merely wanna remark on few general things, The website design and style is perfect, the written content is very superb : D.

Leave a Comment