Previous Table of Contents Next


”Permanent” Temp Tables

Permanent temp tables last until the server is shut down and restarted. These are real tables, created using the same syntax you learned yesterday. The difference between real tables and permanent temp tables is that permanent temp tables are created in tempdb. Because tempdb is flushed automatically every time the server restarts, permanent tables go away when the server shuts down.

Permanent temp tables are most useful for transient data that may be useful for supporting queries, but isn’t necessary to keep around. Creating a table in tempdb has a couple advantages:

•  tempdb is cleared every time the server restarts, so transitory data, or copies of data stored in normal tables, doesn’t clutter up your regular database.
•  tempdb is frequently placed on a very fast I/O device. Sometimes it is placed in the server’s RAM. If all of your data is in memory, and disks need not be used, you can satisfy queries much faster (about seven times faster, depending on a lot of related factors such as disk speed, network throughput, number of processors, locking contention, and so on).

Table 10.2 outlines the differences between temp tables and normal tables.

Table 10.2. Summary of differences between temporary and normal tables.
Local Temp Table Global Temp Table Permanent Temp Table Normal Table
Created in tempdb Created in tempdb Created in tempdb Created in any database
Use # to create Use ## to create Created like a normal table, but in tempdb Created in any database
Reference using # Reference using ## Reference using tempdb..tablename Reference using table name
Last until connection that created them is closed Last until connection that created them is closed, or until all connections have stopped accessing the table, whichever comes last Last until the server reboots Last forever
Accessible from connection that created it only Accessible from any connection unless permissions have explicitly been revoked Accessible from any connection if permissions have been granted Accessible from any connection if permissions have been granted

How to Build a Temporary Table on System Startup

Starting with MS SQL Server version 6.0, there is a new system stored procedure called sp_makestartup. This allows the system administrator to create a stored procedure that gets executed as soon as the server starts, without human intervention. You can use this to create a stored procedure which, in turn, creates a permanent temporary table. The permanent temporary table could be used to support queries in heavy volume multi-user environments.

Here are the steps to accomplish this. Suppose you want to duplicate the orders table in your customer database into tempdb.

1.  Create a stored procedure in the master database. Stored procs are covered on Days 16 and 17. Essentially, they allow you to define a block of SQL code that is permanently stored on the server, and which can be run by asking the server to execute it. Autostart procs must reside in the master database. 
create    procedure    Startup_build_orders    as
select  *
into       tempdb..orders
from        customer..orders
return
2.  Register this procedure as a startup procedure using the sp_makestartup proc: 
exec     sp_makestartup     Startup_build_orders

Now, the next time the server restarts, a copy of the orders table will be built automatically.


Previous Table of Contents Next
Используются технологии uCoz