Using Temporary Tables | ppk on JavaScript, 1/e

Temporary tables provide an efficient method for loading data into a temporary work area, where you can loop through the data and evaluate it. This is especially useful for queries like the query I used in the section 'Disconnecting Users,' earlier in this chapter.

CREATE table #tmpUsers(  spid int,  eid int,  status varchar(30),  loginname varchar(50),  hostname varchar(50),  blk int,  dbname varchar(50),  cmd varchar(30)) INSERT INTO #tmpUsers EXEC sp_who

This query loads the results of sp_who into a temporary table, where I can later use a cursor to loop through the records to perform an action (like issue a KILL statement) on connections that meet certain criteria. The single pound sign (#) before the table name means it is a local temporary table, and therefore is only visible from the session that created it. Using a double-pound sign before the table name means you're using a global temporary table. Global temporary tables are accessible from any session.

Temporary tables use the tempdb database to hold their data, which makes accessing them quicker. To keep session local tables from stepping on each other, a numeric suffix is appended to the end of the name internally. You do not need to know the full name of the tables in your queries. A local temporary table is kept alive as long as the connection that created it is in scope. They are automatically dropped when any of the following actions occur:

The stored procedure completes
The session that created the table is closed
A drop statement is manually issued

Note
You can see the internal name for your table in the sysobjects table in the tempdb database.

Global temporary tables are visible from any session on the database. They are kept alive as long as the session that created them is still alive and no other sessions are using the tables. Whether the temp table is local or global, experiment with placing indexes on the temporary tables.

Table Data Types for Temporary Tables

Usage of temporary tables can be sped up substantially by using the new table SQL Server data type. The table data type is kept in scope during the execution of a stored procedure or query and is automatically dropped thereafter. Using these types requires less locking and recompilation, since they have a tight, well-defined scope. To use the data type, simply place the create table syntax in the same line where you declare the table variable, as shown here:

SET NOCOUNT ON DECLARE @tmpdata table (  categoryid int,  categorynm varchar(50)) INSERT INTO @tmpdata SELECT CategoryID, CategoryName FROM Categories SELECT * FROM @tmpdata

The table variable can be called numerous times throughout the query. Use the table data type whenever you can, in order to avoid using temporary tables.

Note

One reason the table data type is so much faster is that it doesn't occupy space in the tempdb database as its temporary table counterpart. Instead, the table data type uses the SQL Server's memory to store data.

There are a few instances where you can't use the table data type. For example, you can't insert the values of a stored procedure's execute into the table data type. You also cannot do SELECT INTO or INSERT INTO statements. For instance, if you try to run the following procedure:

INSERT INTO <table_variable name> EXEC <stored_procedure name>

You generate the following error:

Server: Msg 197, Level 15, State 1, Line 7 EXECUTE cannot be used as a source when inserting into a table variable.

Using the tempdb Database

The tempdb database is used heavily in applications. The tempdb is used as a workspace, much like Windows uses Pagefile.sys. The tempdb is also used when you create temporary tables. If your tempdb is not optimally configured, it could be a major bottleneck in your system.

To ensure proper performance, stripe your tempdb across multiple drives. Always ensure that the tempdb database is sized large enough so that the database is not having to grow while being queried. SQL Server installs the tempdb database in the same location as the other system databases. If you want to move it to another drive, issue the following command:

USE master go ALTER DATABASE tempdb MODIFY FILE (NAME = tempdev, FILENAME = 'F:\tempdb.mdf') go ALTER DATABASE tempdb MODIFY FILE (NAME = templog, FILENAME = 'F:\templog.ldf') Go

The name shown in the preceding code is the name that is assigned to the tempdb database files at setup. The Filename option in the query is the new location and name of the database files. After you execute code, you need to restart your SQL Server and delete the old files. The query outputs the following results:

File 'tempdev' modified in sysaltfiles. Delete old file  after restarting SQL Server. File 'templog' modified in sysaltfiles. Delete old file  after restarting SQL Server.

Tip

Make sure that your tempdb is sized properly so it doesn't need to grow and shrink automatically. You can do this by adjusting the default size in Enterprise Manager. This may be a spot where multiple SQL Server instances may help, where you can have a separate independent tempdb database per instance.