tempdb

This article describes the

system database, a global resource available to all users

connected to a Database Engine instance in SQL Server, Azure SQL Database, or Azure SQL

Managed Instance.

The

system database is a global resource that holds:

that are explicitly created. They include:

Global or local temporary tables and indexes on these tables

Temporary stored procedures

Large variables, including table variables

Tables returned in table-valued functions

Cursors

User objects that can be created in a user database can also be created in

,

however they are created without a durability guarantee, and are dropped when the

Database Engine instance restarts.

that the database engine creates. They include:

Work tables to store intermediate results for spools, cursors, sorts, and temporary large

object (LOB) storage.

Work files for hash match or hash aggregate operations.

Intermediate sort results for operations such as creating or rebuilding indexes (if

is specified), or certain

,

, or

queries.

Each internal object uses a minimum of nine pages: an IAM page and an eight-page

extent. For more information about pages and extents, see the

Page and extent

architecture guide.

, which are collections of data pages that hold the data rows that support

row versioning. The version stores contain:

Row versions that are generated by data modification transactions in a database that

uses row versioning-based

or

isolation transactions.

Row versions that are generated by data modification transactions for features, such as

online index operations, Multiple Active Result Sets (MARS), and

triggers.

tempdb tempdb tempdb
SORT_IN_TEMPDB
GROUP BY
ORDER BY
UNION
READ COMMITTED
SNAPSHOT
AFTER