SQL Server 2008 : Using Temporary Tables in Stored Procedures

- How To Install Windows Server 2012 On VirtualBox
- How To Bypass Torrent Connection Blocking By Your ISP
- How To Install Actual Facebook App On Kindle Fire
4/28/2011 4:25:12 PM
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
create proc temp_test2
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')

exec temp_test2

select * from ##temp

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

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
Server: Msg 2714, Level 16, State 6, Procedure temp_test2, Line 3
There is already an object named '##temp' in the database.


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
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
create proc ptemp1 WITH RECOMPILE
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'


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

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

exec tab_var_test '6/1/2001', 10000

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.

Top 10
- Microsoft Visio 2013 : Adding Structure to Your Diagrams - Finding containers and lists in Visio (part 2) - Wireframes,Legends
- Microsoft Visio 2013 : Adding Structure to Your Diagrams - Finding containers and lists in Visio (part 1) - Swimlanes
- Microsoft Visio 2013 : Adding Structure to Your Diagrams - Formatting and sizing lists
- Microsoft Visio 2013 : Adding Structure to Your Diagrams - Adding shapes to lists
- Microsoft Visio 2013 : Adding Structure to Your Diagrams - Sizing containers
- Microsoft Access 2010 : Control Properties and Why to Use Them (part 3) - The Other Properties of a Control
- Microsoft Access 2010 : Control Properties and Why to Use Them (part 2) - The Data Properties of a Control
- Microsoft Access 2010 : Control Properties and Why to Use Them (part 1) - The Format Properties of a Control
- Microsoft Access 2010 : Form Properties and Why Should You Use Them - Working with the Properties Window
- Microsoft Visio 2013 : Using the Organization Chart Wizard with new data
- First look: Apple Watch

- 3 Tips for Maintaining Your Cell Phone Battery (part 1)

- 3 Tips for Maintaining Your Cell Phone Battery (part 2)
programming4us programming4us