Temporary tables are commonly used in stored
procedures when intermediate results need to be stored in a work table
for additional processing. Local temporary tables created in a stored
procedure are automatically dropped when the stored procedure exits.
Global temporary tables created in a stored procedure still exist after
the stored procedure exits until they are explicitly dropped (see Listing 1) or the user session in which they were created disconnects from SQL Server.
Listing 1. Using Local and Global Temporary Tables in Stored Procedures
set nocount on
go
create proc temp_test2
as
select pub_id, pub_name, city, state
into ##temp
from publishers
where State in ('MA', 'DC', 'CA')
select pub_id, pub_name, city, state
into #temp
from publishers
where State in ('MA', 'DC', 'CA')
go
exec temp_test2
go
select * from ##temp
go
pub_id pub_name city state
------ ---------------------------------------- -------------------- -----
0736 New Moon Books Boston MA
0877 Binnet & Hardley Washington DC
1389 Algodata Infosystems Berkeley CA
9912 Landlocked Books Boston MA
9913 Blackberry's Cambridge MA
select * from #temp
go
Server: Msg 208, Level 16, State 0, Line 1
Invalid object name '#temp'.
|
Note what happens if you try to run the stored procedure again:
exec temp_test2
go
Server: Msg 2714, Level 16, State 6, Procedure temp_test2, Line 3
There is already an object named '##temp' in the database.
Tip
The general consensus is
that there is not much need for using global temporary tables in stored
procedures. The typical reason for using temporary tables in stored
procedures is that you need a work area within the stored procedure
only. You normally wouldn’t want it sticking around after the procedure
finishes. Creating a global temporary table in a stored procedure
requires an explicit drop of the table before the procedure exits if you
no longer need it. If that’s the case, what’s the benefit of using a
global temporary table? Any subprocedures will be able to see and
reference a local temporary table created in the calling procedure, so
global temporary tables are not needed in that case.
Only if you need to create
and populate a work table and have it available after the procedure
exits should you consider using a global temporary table. However, you
have to remember to explicitly drop it at some point before attempting
to run the procedure again. But if an error occurs that aborts
processing of the stored procedure, the explicit drop might not be
executed.
You might want to include a
check for the global temporary table in your stored procedure and drop
it automatically before attempting to create it again, as in the
following code snippet:
create proc myproc
as
if exists (select 1 from tempdb..sysobjects where name = '##global_temp'
and type = 'U')
drop table ##global_Temp
select * into ##global_temp from ...
Temporary Table Performance Tips
All users in SQL Server share the same tempdb database for work tables and temporary tables, regardless of the database in which they are working. This makes tempdb a potential performance bottleneck in any multiuser system. The primary bottleneck in tempdb is disk I/O, but locking contention may also occur between processes on the tempdb system catalogs.
SQL Server 2008 alleviates the
disk I/O problem by logging just enough information to allow rollback
of transactions without logging all the additional information that
would be necessary to recover those transactions. The recovery
information is needed only when recovering a database at system startup
or when restoring from a backup. Because tempdb is rebuilt during SQL Server startup (and no one in his right mind would restore tempdb from a backup), you don’t need to keep this recovery information. When you reduce the logging in tempdb, data modification operations on tables in tempdb can be up to four times faster than the same operations in other databases.
However, locking in tempdb is still a potential performance bottleneck. If you create a table in tempdb
within a transaction, locks are held on rows in the system catalogs.
These locks being held on the system catalogs could lead to locking
contention with other processes trying to read or update the tempdb system catalogs.
To minimize the potential for locking contention on the system tables in tempdb,
you should consider creating your temporary tables before starting a
transaction so that locks are released immediately and not held on the
system catalogs until the end of the transaction. If the table must be
created in a transaction, you should commit your transaction as soon as
possible.
Also, you need to be aware that even if it’s not in a transaction, creating a temporary table by using SELECT INTO holds locks on the system catalogs in tempdb until the SELECT INTO completes. If locking contention in tempdb becomes a problem, you should consider replacing SELECT INTO with CREATE TABLE, followed by an INSERT using a SELECT statement. Although this statement might run a bit more slowly than SELECT INTO, the system table locks are held only for the brief moment it takes for CREATE TABLE to complete.
Another way to speed up
temporary table creation/population is to keep temporary tables as small
as possible so that they are created and populated more quickly. You
should select only the required columns, rather than use SELECT *,
and you should retrieve only the rows from the base table that you
actually need to reference. The smaller the temporary table, the faster
it is to create the table; smaller temporary tables also help speed up
queries against the temporary table.
If a temporary table is of
sufficient size and is going to be accessed multiple times within a
stored procedure, it might be cost effective to create an index on it on
the column(s) that will be referenced in the search arguments of
queries against the temporary table. If the time it takes to create the
index plus the time the queries take to run using the index is less than
the sum total of the time it takes the queries against the temporary
table to run without the index, you probably want to consider creating
an index on the temporary table.
The following example demonstrates the creation of an index on a temporary table:
use bigpubs2008
go
create proc ptemp1 WITH RECOMPILE
as
select title_id, type, pub_id, ytd_sales
into #temp_titles
from titles
create index tmp on #temp_titles(pub_id)
select sum(ytd_sales)
from #temp_titles
where pub_id = '0736'
select min(ytd_sales)
from #temp_titles
where pub_id = '0736'
return
go
Following are some other final tips for using temporary tables in stored procedures:
Don’t use temporary tables to combine result sets together when a UNION or UNION ALL will suffice; internal worktables are faster due to less overhead than temporary tables. UNION ALL is the fastest because no worktable in tempdb is required to merge the result sets.
Drop temporary tables as soon as possible to free up space in tempdb.
Consider using the table data type to avoid using tempdb altogether.
Using the table Data Type
The table data
type can be used to define table variables in stored procedures and
elsewhere in SQL code. Table variables are defined similarly to regular
tables, except you define them using a DECLARE statement, rather than using CREATE TABLE:
DECLARE @table_variable TABLE ({ column_definition | table_constraint }
[ ,...n ])
The following simple example shows how to use a table variable in a stored procedure:
-- proc to get year-to-date sales for all books published since specified date
-- with ytd_sales greater than specified threshold
create proc tab_var_test @pubdate datetime = null,
@sales_minimum int = 0
as
declare @ytd_sales_tab TABLE (title_id char(6),
title varchar(50),
ytd_sales int)
if @pubdate is null
— if no date is specified, set date to last year
set @pubdate = dateadd(month, -12, getdate())
insert @ytd_sales_tab
select title_id, convert(varchar(50), title), ytd_sales
from titles
where pubdate > @pubdate
and ytd_sales > @sales_minimum
select * from @ytd_sales_tab
return
go
exec tab_var_test '6/1/2001', 10000
go
title_id title ytd_sales
-------- -------------------------------------------------- -----------
BU2075 You Can Combat Computer Stress! 18722
MC3021 The Gourmet Microwave 22246
TC4203 Fifty Years in Buckingham Palace Kitchens 15096
You can use table
variables in user-defined functions, stored procedures, and T-SQL
batches. You should consider using table variables instead of temporary
tables whenever possible because they provide the following benefits:
Table variables are memory resident and require no space in tempdb.
When
table variables are used in stored procedures, fewer recompilations of
the stored procedures occur than when temporary tables are used.
Transactions
involving table variables last only for the duration of an update on
the table variable. Thus, table variables require fewer locking and no
logging resources.
A
table variable behaves like a local variable, and its scope is limited
to the stored procedure in which it is declared. It is cleaned up
automatically at the end of the function, stored procedure, or batch in
which it is defined.
A table variable can be used like a regular table in SELECT, INSERT, UPDATE, and DELETE statements. However, a table variable is not allowed in a SELECT INTO statement like the following:
SELECT select_list INTO table_variable ...
You need to keep a
couple of other limitations in mind when considering using table
variables in stored procedures. You cannot create indexes on table
variables by using the CREATE INDEX
command, and no statistics are kept on table variables. You can,
however, define a primary or unique key on the table variable when it is
declared to enforce uniqueness. However, because table variables are
memory resident and typically not very large in size, the inability to
define explicit indexes generally does not lead to any significant performance
issues. In some cases, (for example, if the result set is large and
accessed repeatedly) performance may improve when you use temporary
tables instead because they support indexes and statistics.
