How tempDB works?

sql sql-server tempdb
peakit picture peakit · Oct 20, 2009 · Viewed 52.1k times · Source

I am trying to understand the tempDB and following are the doubts popping in my mind.

  1. What is the lifetime of data in tempDB? Say a query is doing some Order By and uses tempDB for performing that. After this query finishes, someone else also executes a query which utilizes the tempDB. Will the second query find records written by first query in the tempDB or will they be deleted?
  2. Are there any visible tables created inside the tempDB by the Sql Engine? How can I know which temporary table is created because of this query? Is there any naming convention followed by the Sql engine for naming these temporary tables?

I am new to tempDB so please pardon me for asking such silly (if at all) questions :-)

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

Answer

HLGEM picture HLGEM · Oct 20, 2009

Temp table is stored in tempdb until the connection is dropped (or in the case of a global temp tables when the last connection using it is dropped). You can also (and it is a good practice to do so) manually drop the table when you are finished using it with a drop table statement.

No, others cannot see your temp tables if they are local temp tables (They can see and use global temp tables) Multiple people can run commands which use the same temp table name but they will not be overlapping in a local temp table and so you can have a table named #test and so can 10,000 other users, but each one has its own structure and data.

You don't want to generally look up temp tables in tempdb. It is possible to check for existence, but that is the only time I have ever referenced tempdb directly. Simply use your temp table name. Example below of checking for existence

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

You name temp tables by prefacing the name with # (for local tables the ones you would use 999.9% of the time) and ## for global temp tables, then the rest of the name you want.

Related questions

Temp Table collation conflict - Error : Cannot resolve the collation conflict between Latin1* and SQL_Latin1*

I can't update temp table. This is my query CREATE TABLE #temp_po(IndentID INT, OIndentDetailID INT, OD1 VARCHAR(50), OD2 VARCHAR(50), OD3 VARCHAR(50), ORD VARCHAR(50), NIndentDetailID INT, ND1 VARCHAR(50), ND2 VARCHAR(50), ND3 VARCHAR(50), NRD VARCHAR(50), Quantity DECIMAL(15,3)) INSERT INTO #temp_…

Read More
Failure SQL query insufficient disk space

Msg 1101, Level 17, State 10, Line 12 Could not allocate a new page for database 'TEMPDB' because of insufficient disk space in filegroup 'DEFAULT'. Create the necessary space by dropping objects in the filegroup, adding additional files to the filegroup, or setting autogrowth …

Read More
Why are temporary tables not removed from tempdb in SQL Server?

I have created one stored procedure with 7 temporary tables and each temp table is dropped at the end of their own work. I am calling the SP from one web service and same web service we are used for different …

Read More