Database Reference
In-Depth Information
Chapter 12
Temporary Tables
Temporary tables are an essential part of SQL Server. SQL Server will sometimes create them during query execution
to store working tables and intermediate result sets. At other times, they are created by developers.
In this chapter, we will discuss a few different types of temporary tables that can be created by users: local and
global temporary tables, table variables, user-defined table types, and table-valued parameters.
Temporary Tables
We create
temporary tables
to store short-term information, such as intermediate results and temporary data during
data processing. Temporary tables live in tempdb, and they behave very similarly to regular tables. There are a few
minor differences, however, which we will discuss later in the chapter.
There are two kinds of the temporary tables: local and global.
Local temporary tables
are named starting with
the
#
symbol, and they are visible only in the session in which they were created and in the modules called from that
session. When multiple sessions simultaneously create local temporary tables with the same name, every session will
have its own instance of the table.
When we create a temporary table in a stored procedure, for example, we are able to access it in that specific
stored procedure as well as in the stored procedures that we call from that stored procedure.
■
You can access a temporary table created in a stored procedure from the triggers defined in some tables
if the stored procedure performs the action that fires those triggers. However, this is clearly a bad idea, as the data
modification operation will fail if a temporary table has not been created.
Caution
Listing 12-1 provides an example that demonstrates a temporary table scope.
Listing 12-1.
Local temporary table scope and visibility
create table #SessionScope(C1 int not null)
go
create proc dbo.P1
as
begin
-- Success: #SessionScope is visible because it's created
-- in the session scope
select * from #SessionScope
