CODEWITHSUNDEEP
sql-server
Bhuvanesh Waran
Bhuvanesh Waran

Reputation: 642

What is the purpose of tempdb in SQL Server?

I need a clarification about tempdb in SQL Server and need some clarifications on following things

  1. What is the purpose of its?
  2. Can we create a own tempdb and how to make refer the own tempdb to own database?

Upvotes: 3

Views: 9298

Answers (3)

Jande
Jande

Reputation: 1705

FROM MSDN

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:

  1. Temporary user objects that are explicitly created, such as: global or local temporary tables, temporary stored procedures, table variables, or cursors.
  2. Internal objects that are created by the SQL Server Database Engine, for example, work tables to store intermediate results for spools or sorting.
  3. Row versions that are generated by data modification transactions in a database that uses read-committed using row versioning isolation or snapshot isolation transactions.
  4. 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.

Upvotes: 3

TomTom
TomTom

Reputation: 62159

1: It is what it says. A temporary storage. FOr example when you ask for DISTINCT results, SQL Server must remember what rows it already sent you. Same with a temporary table.

2: Makes no sense. Tempdb is not a database but a server thing - ONE TempDB regardless how many database. You can change where it is and how it is (file number, size) but it is never related to one database (except obviously if you only have one database on a SQL Server instance). Having your own Tempdb is NOT how SQL Server works. And while we are at it - no need to ever make a backup (of tempdb). When SQL Server starts, Tempdb is reinitialized as empty.

And, btw., this would be obvious if you would bother with such things as being borderline competent. Which includes for me reading the documentation of every major technology I work with once. You should consider this to be something to adopt because it is the only way to know what you are doing.

Upvotes: -1

TheGameiswar
TheGameiswar

Reputation: 28938

TempdB is a system database and we cant create system databases .Tempdb is a global resource for all databases ,which means temp tables,table variables,version store for user databases...all will use tempdb..This is a pretty basic explanation for tempdb.Refer to below links on how it is used for other purposes like database emails,..

https://msdn.microsoft.com/en-us/library/ms190768.aspx

Upvotes: 2

Related Questions