SQL Server TempDB

TempDB is used for many operations, such as user-created temporary objects, internal temporary objects and version stores and certain features like online re-indexing, multiple active record sets (MARS) and others. Because of all these uses, TempDB should be installed on a fast drive (like SSD) and even set to multiple disk drives. You should also allocate enough spaces to TempDB because if it can halt query operations if it runs out of space.

The tempdb system database is a global resource that is available to all users connected to the instance of SQL Server and is used to hold the following:

• Temporary user objects that are explicitly created, such as: global or local temporary tables, temporary stored procedures, table variables, or cursors.
• Internal objects that are created by the SQL Server Database Engine, for example, work tables to store intermediate results for spools or sorting.
• Row versions that are generated by data modification transactions in a database that uses read-committed using row versioning isolation or snapshot isolation transactions.
• Row versions that are generated by data modification transactions for features, such as: online index operations, Multiple Active Result Sets (MARS), and AFTER triggers.

Operations within tempdb are minimally logged. This enables transactions to be rolled back. tempdb is re-created every time SQL Server is started so that the system always starts with a clean copy of the database. Temporary tables and stored procedures are dropped automatically on disconnect, and no connections are active when the system is shut down. Therefore, there is never anything in tempdb to be saved from one session of SQL Server to another. Backup and restore operations are not allowed on tempdb.

The tempdb system database is very similar to a user database. The main difference is that data in tempdb does not persist after SQL Server shuts down.

Tempdb mostly lives in memory, but it’s important to have it on a fast disc,so that if you do have to access disc, your performance is good. 

There are two types of temporary tables that you’ll use: local and global, and these have a specific designation. Local tables are designated with one pound sign and global temporary tables are designated with two pound signs. If you create a table that doesn’t have a pound sign, or two pound signs, in front of it within tempdb, it will not be dropped when your session is dropped. Unless you explicitly drop it, it will remain in tempdb until the SQL Server instance is restarted, or it’s explicitly dropped.