THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

John Paul Cook

Local Temporary Table Persistence

Everybody knows local temporary tables have scope only within a connection. Drop the connection and it goes away. The connection is at the server level, not at the database level. People commonly say things like "I'm connected to the database" when they are actually connected to the server. In SSMS, you right-click on a specific database and select the option to open a new query window. Since you initiation the connection from a specific database name in the Object Explorer, it does seem like the database owns the connection. But the database doesn't own the connection in any way.

When you have a connection to a user database and create a local temporary table, the temporary table is created in tempdb. It's true that queries issued against your user database can access the temporary table, but they aren't related to each other, they are only sharing the same server connection. You can drop your database completely, but it does not affect any temporary objects you created in tempdb.

Creating a different user database doesn't change anything if all of the work is done within the same query window in SSMS. Although you are attempting to create a temporary table from a different user database, the fact remains that tempdb is the same and is being referenced from the same connection. Referring to the code sample below, the fact that it seems like you issued the second create table statement "from" database DEMO2 is irrelevant. The temporary table is issued from the server connection, not from the database you issued the USE statement against.

A user database named DEMO1 is created  and a USE statement sets the query context to the DEMO1 database. Next, a temporary table is created. After that, the user database is dropped. Because your connection to tempdb persists, your temporary table persists. The point is that although your connection's context is the DEMO1 database, the temporary table really doesn't have anything to do with DEMO1. Although it arguably appears to have been created from demo1, it was created on the server in tempdb. The USE DEMO1 statement is irrelevant, it doesn't affect temporary objects in tempdb. The DEMO1 database doesn't in any manner have ownership of temporary objects in tempdb. Only the connection to the server owns the temporary objects. If you want to be able to create that local temporary table a second time without seeing an error message, you'll either have to drop it first or open a completely new query window in SSMS. If you don't, you'll see something like this:

Msg 2714, Level 16, State 6, Line 2
There is already an object named '#demo' in the database.
 

Thanks to a reader for pointing out corrections to this post. Here's some code you can play with to see for yourself.

use master
go

create database demo1 on primary
( name = demo1, filename = 'c:\program files\microsoft sql server\mssql10.gdt\mssql\data\demo1.mdf' 
-- alter filenames for your environment
 log on
( name = demo1_log, filename = 'c:\program files\microsoft sql server\mssql10.gdt\mssql\data\demo1_log.ldf' )
go

use demo1
go

create table #demo (a int) -- succeeds
go

use master
go

alter database demo1 set single_user with rollback immediate
drop database demo1
go

create database demo2 on primary
( name = demo2, filename = 'c:\program files\microsoft sql server\mssql10.gdt\mssql\data\demo2.mdf' )
log on
( name = demo2_log, filename = 'c:\program files\microsoft sql server\mssql10.gdt\mssql\data\demo2_log.ldf' )
go

use demo2
go

create table #demo (a int)  -- fails
go

use master
go

alter database demo2 set single_user with rollback immediate
drop database demo2
go

Published Thursday, October 09, 2008 12:32 PM by John Paul Cook

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

jerryhung said:

I guess it may be easier to think that ALL temp tables reside in tempdb database, not releated to the user database at all

I believe same for Global temp tables (##temp)

which is why this would work in any User database

IF OBJECT_ID('tempdb.dbo.#tempTable') IS NOT NULL

   DROP TABLE #tempTable

CREATE TABLE #tempTable (id smallint)

October 10, 2008 11:58 AM
 

John Paul Cook said:

Yes, Jerry, you are correct - all temporary objects reside exclusively in tempdb. I didn't mention global temporary objects because persistence of global depends on more than a single connection. I was trying to keep things simple. But the concepts are the same - temporary objects are not related to a user database even though they are referenced by statements executing against a user database.

October 10, 2008 2:46 PM

Leave a Comment

(required) 
(required) 
Submit

About John Paul Cook

John Paul Cook is a Technology Solutions Professional for Microsoft's data platform and works out of Microsoft's Houston office. Prior to joining Microsoft, he was a Microsoft SQL Server MVP. He is experienced in Microsoft SQL Server and Oracle database application design, development, and implementation. He has spoken at many conferences including Microsoft TechEd and the SQL PASS Summit. He has worked in oil and gas, financial, manufacturing, and healthcare industries. John is also a Registered Nurse who graduated from Vanderbilt University with a Master of Science in Nursing Informatics and is an active member of the Sigma Theta Tau nursing honor society. He volunteers as a nurse at safety net clinics. Contributing author to SQL Server MVP Deep Dives and SQL Server MVP Deep Dives Volume 2. Opinions expressed in John's blog are strictly his own and do not represent Microsoft in any way.

This Blog

Syndication

Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement