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:
- Space issues when large queries execute
- Performance issues when large queries execute
- 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.
|
1 2 3 4 5 6 7 8 9 10 11 |
<span style="color: #008000;">-- Database selection does not matter USE tempdb; GO -- Create regular table in tempdb CREATE TABLE A ( MyId INT PRIMARY KEY CLUSTERED, MyValue VARCHAR(20) ) GO</span> |
I am going to use an INSERT statment to populate the table with sample data.
|
1 2 3 4 5 6 7 8 |
<span style="color: #008000;">-- Add data to regular table in tempdb INSERT INTO A (MyId, MyValue) VALUES (1, 'Continental'), (2, 'Edsel'), (3, 'Lincoln'), (4, 'Mercury'), (5, 'Ram') GO</span> |
To really make sure the data is there, I am going to use a SELECT statement to view the contents of the table.
|
1 2 3 4 |
<span style="color: #008000;">-- Show data from table A SELECT MyId, MyValue FROM A GO</span> |
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.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
<span style="color: #008000;">-- Remove record 3 DELETE FROM A WHERE MyId = 3; GO -- Update record 5 UPDATE A SET MyValue = 'Dodge Ram' WHERE MyId = 5; GO -- Show up dated data from table A SELECT MyId, MyValue FROM A GO</span> |
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.
|
1 2 3 4 |
<span style="color: #008000;">-- Just look for table since we are in tempdb IF EXISTS (SELECT * FROM tempdb.sys.objects T WHERE T.TYPE = 'U' AND T.name LIKE 'A') DROP TABLE A GO</span> |
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.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 |
<span style="color: #008000;">-- Use the A.W. database USE AdventureWorks2008R2 GO -- Regular table in tempdb (MC) - last sales order by customer in 2008 SELECT CustomerID AS MyCustomer, MAX(OrderDate) AS MyDate INTO tempdb.dbo.MC FROM Sales.SalesOrderHeader WHERE YEAR(OrderDate) = 2008 GROUP BY CustomerID -- Get customer name, sales id, sub-total and order date SELECT P.FirstName, P.LastName, SH.SalesOrderID, SH.SubTotal, SH.OrderDate FROM Sales.SalesOrderHeader AS SH INNER JOIN tempdb.dbo.MC ON SH.CustomerID = MC.MyCustomer AND SH.OrderDate = MC.MyDate INNER JOIN Sales.Customer C ON MC.MyCustomer = C.CustomerID INNER JOIN Person.Person P ON C.PersonID = P.BusinessEntityID ORDER BY SH.OrderDate ASC -- Switch to tempdb USE tempdb; GO -- Remove regular static table IF EXISTS (SELECT * FROM tempdb.sys.objects T WHERE T.TYPE = 'U' AND T.name LIKE 'MC') DROP TABLE MC GO</span> |
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).
Love your blog!
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!
I like this web blog its a master peace ! .
Merely wanna remark on few general things, The website design and style is perfect, the written content is very superb : D.