Previous | Table of Contents | Next |
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 isnt necessary to keep around. Creating a table in tempdb has a couple advantages:
Table 10.2 outlines the differences between temp tables 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 |
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.
create procedure Startup_build_orders as select * into tempdb..orders from customer..orders return
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 |