Temporary Tables – Part 2 – Ramblings of a Crafty DBA

Today, I am going to talk about local and global temporary tables.

A local temporary table is defined by using the CREATE TABLE syntax with the name of the table prefixed by one number sign #. The scope of the local temporary table is the current session or SPID.

A global temporary table is defined by using the CREATE TABLE syntax with the name of the table prefixed by two number signs ##. The table is visible to all sessions. The scope of the global temporary table is the current session or the last session that dereferences the table.

Both local and global tables are stored in the tempdb database. Since multiple process might be calling a stored procedure with a local temporary table, SQL Server post fixes the name of the table with a unique tag to keep them unique.

I am going to use the car ownership table for person A to demonstration how to work with local temporary tables. Please see the tiny AUTOS database I created in earlier article.

The code snipet below creates a local temporary table with scope related to my SSMS query window.

<span style="color: #008000;">-- Database selection does not matter
USE model;
GO

-- Create local temp table
CREATE TABLE #A
(
  MyId INT PRIMARY KEY CLUSTERED,
  MyValue VARCHAR(20)
)
GO</span>

-- Database selection does not matter

USE model;

-- Create local temp table

CREATE TABLE #A

(

MyId INT PRIMARY KEY CLUSTERED,

MyValue VARCHAR(20)

)

GO

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

<span style="color: #008000;">-- Add data to local temp table
INSERT INTO #A (MyId, MyValue) VALUES
  (1, 'Continental'),
  (2, 'Edsel'),
  (3, 'Lincoln'),
  (4, 'Mercury'),
  (5, 'Ram')
GO</span>

-- Add data to local temp table

INSERT INTO #A (MyId, MyValue) VALUES

(1, 'Continental'),

(2, 'Edsel'),

(3, 'Lincoln'),

(4, 'Mercury'),

(5, 'Ram')

GO

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

<span style="color: #008000;">-- Show data from table A
SELECT MyId, MyValue
FROM #A
GO
</span>

-- Show data from table A

SELECT MyId, MyValue

FROM #A

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

<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>

-- Remove record 3

DELETE FROM #A WHERE MyId = 3;

-- Update record 5

UPDATE #A

SET MyValue = 'Dodge Ram'

WHERE MyId = 5;

-- Show up dated data from table A

SELECT MyId, MyValue

FROM #A

GO

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.

<span style="color: #008000;">-- Table names are stored with system post fix at end
IF EXISTS (SELECT * FROM tempdb.sys.objects T WHERE T.TYPE = 'U' AND T.name LIKE '#A%')
  DROP TABLE #A
GO</span>

-- Table names are stored with system post fix at end

IF EXISTS (SELECT * FROM tempdb.sys.objects T WHERE T.TYPE = 'U' AND T.name LIKE '#A%')

DROP TABLE #A

GO

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 local temporary table 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.

<span style="color: #008000;">-- Use the A.W. database
USE AdventureWorks2008R2
GO

-- Local temp table (MC) - last sales order by customer in 2008 
SELECT 
  CustomerID AS MyCustomer,
  MAX(OrderDate) AS MyDate
INTO
  #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 #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  


-- Table names are stored with system post fix at end
IF EXISTS (SELECT * FROM tempdb.sys.objects T WHERE T.TYPE = 'U' AND T.name LIKE '#MC%')
  DROP TABLE #MC
GO</span>

-- Use the A.W. database

USE AdventureWorks2008R2

-- Local temp table (MC) - last sales order by customer in 2008

SELECT

CustomerID AS MyCustomer,

MAX(OrderDate) AS MyDate

INTO

#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 #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

-- Table names are stored with system post fix at end

IF EXISTS (SELECT * FROM tempdb.sys.objects T WHERE T.TYPE = 'U' AND T.name LIKE '#MC%')

DROP TABLE #MC

GO

The code snipets above that drop tables have one fatal bug. If the code is executed from two query windows at the same time, one window will fail since it will try to drop a table that does not exist. One fix to this bug is to add the SPID to the end of the table name. Since each session (SPID) is now unique at a given time, the correct result will come back from the EXISTS query.

In short, local and global temporary tables have been around for a long time. Since older versions of the SQL Server engine do not support constructs such as table variables or common table expressions, it is wise to know how to use this technique. Next time, I will talk about table variables.

Temporary Tables – Part 2

Related posts

One Thought to “Temporary Tables – Part 2”

Leave a Comment Cancel reply

Related posts

Don’t pass over synonyms

Is my string a number?

Replacing Data – Part 2

One Thought to “Temporary Tables – Part 2”

Leave a Comment Cancel reply