TempDB, one of the system databases of SQL Server Database Engine is very special. Special, because it is re-created every time SQL Server is restarted. This is nothing but a place for temporary user objects and internal objects explicitly created by SQL Server itself.
When we create a new user database in SQL Server, the model database is used as a template for the creation. The same thing applies for TempDB too, it will inherit the database options from model database except the recovery model, because TempDB always uses Simple Recovery Model.
TempDB Objects:
a) User Objects: We can create Local and Global temporary tables that reside in TempDB. # and ## are the prefixes for local and global temporary tables respectively. Other objects that are stored in TempDB are table variables and table-valued functions.
b) Internal Objects: The internal objects in TempDB are not visible using the normal tools. The catalog views also do not store there metadata, because its stored only in memory. The 3 basic types of internal objects are work tables, work files, and sort units.
Work tables are created by SQL Server during these activities:
- To hold intermediate results during a large query (Spooling)
- Running DBCC CHECKDB or DBCC CHECKTABLE
- Working with XML or varchar(MAX) variables
- Processing SQL Service Broker objects
- Working with static cursors
When SQL Server processes a query that uses a hash operator (either for joining and aggregating data) then Work files are used.
In addition to a query containing an ORDER BY clause, when a sort operation takes place, the Sort units are created. Certain types of joins might require SQL Server sort the data before performing the join. Sort units are created in TempDB to hold the data as it is being sorted.
Conclusion:
Internal Objects is that area, which is not exposed to the regular querying side. But, its important to know whats happening inside IT.
