The tempdb Database in SQL Server

[VelMurugan]

The tempdb Database in SQL Server

The tempdb database stores temporary data and data objects. The temporary data objects include temporary tables, temporary stored procedures, and any other objects you want to create temporarily.

The longevity of data objects in the temporary database depends on the type of object created. Ultimately, all temporary database objects are removed when the SQL Server service is restarted.

The tempdb database is re-created, and all objects and data added since the last restart of SQL Server are lost.

tempdb can also be used for some of SQL Server's internal operations. Large sort operations are performed in tempdb before the result set is returned to the client.

Certain index operations can be performed in tempdb to offload some of the space requirements.

SQL Server also uses tempdb to store row versions that are generated from database modifications in databases that use row versioning or snapshot isolation transactions.

Source : sqlthegreat