Temporary Databases

You cannot use either with default_location or for proxy_update parameters with the create temporary database command.

Doing so generates an error, such as the following two examples:

1> create temporary database tb1 with default_location 
     "remSERVER.mydb.."

Msg 102, Level 15, State 7:
Server 'ebi_SUS_AS125x_SUN32', Line 1:
Incorrect syntax near 'create temporary database'.

1> create temporary database tb1 with default_location
     "remSERVER.mydb.." for proxy_update

Msg 102, Level 15, State 7:
Server 'ebi_SUS_AS125x_SUN32', Line 1:
Incorrect syntax near 'create temporary database'.

The temporary status of a database, which is set during the creation of the temporary database, is indicated by value 0x00000100 (256 decimal) of the status3 field of a sysdatabases entry.

In addition to all options inherited from model, a temporary database, like the system tempdb, has the following database options set:

select into/bulkcopy
trunc log on chkpt

As with system tempdb, the guest user is added to the temporary database, and create table permission is granted to PUBLIC.

Unused pages are not cleared during creation of the temporary database, since a temporary database is re-created every time the server is restarted.