What use TempDB in SQL Server?
- Temporary user objects, such as global (##temp) or local (#temp) temporary tables, temporary indexes and stored procedures, table variables, tables returned in table-valued functions or cursors.
- Internal objects are created by database engine to process a SQL server statement
o Work tables for cursor or spool operations and temporary large object (LOB) storage, such as varchar(max), nvarchar(max), varbinary(max) text, ntext, image, xml) data type variables and parameters.
o Work files for hash join or hash aggregate operations.
o intermediate results for particular GROUP BY, ORDER BY, or UNION queries.
- Row versioning values for online index processes, Multiple Active Result Sets (MARS) sessions, AFTER triggers and index operations (SORT_IN_TEMPDB).
- Row versions that are generated by data modification transactions in a database that uses snapshot or read committed using row versioning isolation levels.
