Adaptive Server allows you to create and manage multiple temporary databases in addition to the system tempdb, which was the only temporary database in the server in earlier versions of Adaptive Server.
Multiple temporary databases, also referred to as tempdbs, reduce contention on system catalogs and logs in the system tempdb. They allow you to:
Create temporary databases on fast-access devices
Drop a temporary database to reclaim storage
Partition tasks that create temporary objects into using specific tempdbs, which prevents these tasks from interfering with other sessions that need to use temporary database space
The multiple temporary database feature is fully enabled for:
New installations
Installations that upgraded from an Adaptive Server earlier than version 12.5
Databases that are loaded from an Adaptive Server earlier than version 12.5
The tempdb database is the system-created temporary database. Before Adaptive Server version 12.5.0.3, tempdb was the only temporary database in the server. Temporary tables and work tables are created in tempdb.
Adaptive Server allows you to create multiple temporary databases, which you can then use to create temporary objects such as private temporary tables and work tables. Database administrators can bind—that is, create associations between—the “sa” login and applications to specific temporary databases or to the default group of temporary databases using sp_tempdb. The default group is a system-created group that always has at least the system tempdb as its member. You can add other temporary databases to this group.
You
cannot explicitly bind objects to tempdb.
An application bound to the group can be assigned any temporary database from within the group in a round-robin fashion.
User groups and the default temporary
database group are not related.
User-created temporary databases are created by the user, typically the database administrator. These databases are usually created to minimize resource contention (such as system catalog and log contention) in the system tempdb. User-created temporary databases are very similar to the system tempdb in that they are:
Used primarily to create temporary objects
Re-created, rather than recovered, during a system-recovery process
All objects in a temporary database before a shutdown or crash are lost during recovery because temporary databases are overwritten with the model database. Those restrictions that apply to the system tempdb also apply to the user-created temporary databases. See “Rollback and recovery” for more information.
Unlike the system tempdb, you can drop user-created temporary databases.
During login, sessions get assigned to a temporary database based on the existing bindings in effect:
If the binding is to a specific temporary database that is online and available, the session gets assigned to it.
If the binding is to the default group, a temporary database from that group is selected using a round-robin selection policy.
If no binding is specified, a temporary database is selected from the default group.
The temporary database chosen for a session remains in effect for the duration of that session and never gets changed, regardless of any changes to the bindings.
Once a session is assigned a temporary database, all temporary objects created during that session are created in that temporary database. These objects are implicitly dropped when the session or server shuts down. Shareable temporary tables are implicitly dropped when the server shuts down.
Temporary
tables may be dropped explicitly by the session.
Server or shareable temporary tables continue to be created in the system tempdb if they are fully qualified as “tempdb..server_temptab” to include the database and table name. This is done to make sure that existing applications that pass information between sessions using shareable temporary tables continue to work. New applications, however, can use user-created tempdbs to create shareable temporary tables.
Private temporary tables are created per session, and use the “#” symbol at the beginning of their names (for example, #pubs). They cannot be shared across sessions. These temporary tables and work tables reside in the session’s assigned temporary database. There are two types of private temp tables. They are also differentiated by their visible scope and how long they implicitly exist:
Session temp table – created at the batch level outside of a procedure, this type of private temp table is:
Visible to all commands, including procedures that are executed within the creating session
Is implicitly dropped when the session terminates
The following create statement, executed at the batch level, creates a private temp table:
create table #t1(id int, desc varchar(250))
Procedural temp table – created within a procedure, it is:
Visible to the procedure that creates it and any nested procedures it calls.
Implicitly dropped when the procedure that created it exits.
The following creates two procedural temp tables:
create procedure SetupTempTables as create table #pt1( . . . ) create table #pt2( . . . )
Applications can create shareable temporary tables in user-created temporary databases in exactly the same way that they create shareable temporary tables created in the system tempdb. Cooperating processes can communicate through these tables.
Procedural
temporary tables can also be explicitly dropped.
Stored procedures that create or access private temporary tables, do so in the temporary database assigned to the session.
Shareable temporary tables can be created in user-created temporary databases as well as in the system tempdb. All shareable temporary tables can be shared across sessions and are implicitly dropped when the Server reboots.
Unlike the
system tempdb, user-created temporary
databases can be dropped. Any applications that are dependent upon
a dropped user-created temporary database will not work
if shareable temp tables existed.
