How does tempDB work?

Question

I am trying to understand tempDB , and the following are doubts that arise in my head.

• What is the data lifetime in tempDB ? Let's say that the query executes several Order By and uses tempDB to do this. Upon completion of this request, someone else runs a request that uses tempDB . Will the second query look for records written by the first query in tempDB , or will they be deleted?
• Do Sql Engine have visible tables created inside tempDB ? How do I know which temporary table was created due to this query? Is there any naming convention followed by an Sql mechanism for naming these temporary tables?

I'm new to tempDB , so please forgive me for such stupid (if at all) questions tempDB

It will be very nice if someone can point me to a good resource that can help me learn about tempDB.

Answer 1

The Temp table is stored in tempdb until the connection is deleted (or in the case of global temporary tables, when the last connection to it is deleted). You can also (and this is good practice) manually drop the table when you finish using it with the drop-down table instruction.

No, others cannot see your temporary tables if they are local temporary tables (they can see and use global temp tables). Several people can run commands that use the same temp table name, but they will not overlap in the local temp table and therefore you can have a table named #test and therefore 10,000 other users, but each of them has its own structure and data.

You do not want to look for temporary tables at all in tempdb. You can check for availability, but this is the only time I have referenced tempdb directly. Just use the temp table name. Example below check for availability

  IF OBJECT_ID('TempDB.dbo.#DuplicateAssignments') IS NOT NULL BEGIN DROP TABLE #DuplicateAssignments END 

You name the temporary tables, preceded by the name # (for local tables — you would use 999.9% of the time) and ## for global temporary tables, and then the rest of the name you want.

Answer 2

There are several MSDN articles that are probably the best source of tempDB database information in SQL Server.

tempdb database

The tempdb database is a global resource that is accessible to all users who are connected to an instance of SQL Server and is used for the following:

• Temporary user objects that are explicitly created, such as global or local temporary tables, temporary stored procedures, variable tables, or cursors.
• Internal objects created by the SQL Server Database Engine, for example, worksheets for storing intermediate results for coils or sorting.
• The version of the rows that are generated by data change transactions in a database that uses reads using row isolation or snapshot isolation transactions.
• Versions of rows that are generated by data modification transactions for a function, such as: online operation index, multiple active result Sets (MARS), and AFTER.

The operations inside tempdb are minimal log. This allows rollback. tempdb is recreated every time SQL Server starts that the system always starts with a clean copy of the database. temporary tables and stored procedures are automatically turned off when disconnected, and no connections are active when the system is turned off. Therefore, there is never in tempdb saved from one SQL Server session to another. Backup and restore operations are not allowed on tempdb.

There's also tempdb and index creation , this blog post along with Working with tempdb in SQL Server 2005 , which states:

The SQL Server system base, tempdb, has undergone several changes in SQL Server 2005. SQL Server 2005 introduced new tempdb methods and internal optimization; The tempdb architecture is largely unchanged from SQL Server 2000.

The tempdb database is very similar to a user database. The main difference is that data in tempdb does not persist after closing SQL Server.

Answer 3

• Temporary tables created in TempDB are deleted when the query completes.

• I'm not sure about this (I would have to try), but I think that theoretically ALL tables created in TempDB are visible, although only the user who created the table has permission to access it.