SQL tempDB 如何工作？

How tempDB works?

提问by peakit

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

我试图理解tempDB以下是我脑海中浮现的疑虑。

1. What is the lifetime of data in tempDB? Say a query is doing some Order Byand uses tempDBfor 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 tempDBor will they be deleted?
2. Are there any visibletables created inside the tempDBby 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?

1. 数据的生命周期是tempDB多少？假设查询正在执行某些操作Order By并tempDB用于执行该操作。此查询完成后，其他人也会执行使用tempDB. 第二个查询会找到第一个查询写入的记录tempDB还是会被删除？
2. Sql 引擎内部是否创建了任何可见的表tempDB？我如何知道由于此查询而创建了哪个临时表？是否有任何命名约定遵循 Sql 引擎来命名这些临时表？

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

我是新手，tempDB所以请原谅我问了这么愚蠢的（如果有的话）问题:-)

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

如果有人能给我指出一个可以帮助我了解 tempDB 的好资源，那将是非常好的。

回答by HLGEM

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.

临时表存储在 tempdb 中，直到连接被删除（或者在最后一个使用它的连接被删除时全局临时表的情况下）。您还可以（这是一个很好的做法）在使用 drop table 语句完成使用表后手动删除该表。

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.

不，如果他们是本地临时表，其他人无法看到您的临时表（他们可以查看和使用全局临时表）多人可以运行使用相同临时表名称的命令，但他们不会在本地临时表中重叠，所以你可以有一个名为 #test 的表，其他 10,000 个用户也可以，但每个用户都有自己的结构和数据。

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

您通常不想在 tempdb 中查找临时表。可以检查是否存在，但这是我唯一一次直接引用 tempdb。只需使用您的临时表名称。下面检查是否存在的示例

  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.

您可以通过在名称前加上 #（对于本地表，您将使用 999.9% 的时间）和 ## 为全局临时表加上您想要的其余名称来命名临时表。

回答by CraigTP

There's a few MSDN articles that are probably the best source of information on the tempDB database in SQL Server.

有一些 MSDN 文章可能是有关 SQL Server 中 tempDB 数据库的最佳信息来源。

tempdb Database

临时数据库

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:

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

tempdb 系统数据库是一个全局资源，可供连接到 SQL Server 实例的所有用户使用，用于保存以下内容：

• 显式创建的临时用户对象，例如：全局或局部临时表、临时存储过程、表变量或游标。
• 由 SQL Server 数据库引擎创建的内部对象，例如，用于存储假脱机或排序的中间结果的工作表。
• 由使用行版本控制隔离或快照隔离事务的读取提交的数据库中的数据修改事务生成的行版本。
• 由数据修改事务为功能生成的行版本，例如：在线索引操作、多活动结果集 (MARS) 和 AFTER 触发器。

tempdb 中的操作最少记录。这使事务能够回滚。每次启动 SQL Server 时都会重新创建 tempdb，以便系统始终以数据库的干净副本启动。临时表和存储过程在断开连接时自动删除，并且在系统关闭时没有活动连接。因此，tempdb 中的任何内容都不会从 SQL Server 的一个会话保存到另一个会话。tempdb 上不允许进行备份和还原操作。

There's also tempdb and Index Creation, this blog postalong with Working with tempdb in SQL Server 2005which states:

还有tempdb 和 Index Creation，这篇博客文章以及在 SQL Server 2005 中使用 tempdb指出：

The SQL Server system database, tempdb, has undergone a number of changes in SQL Server 2005. There are new tempdb usages and internal optimizations in SQL Server 2005; tempdb architecture is mostly unchanged since SQL Server 2000.

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

SQL Server 系统数据库 tempdb 在 SQL Server 2005 中经历了许多变化。SQL Server 2005 中有新的 tempdb 用法和内部优化；tempdb 体系结构自 SQL Server 2000 以来几乎没有变化。

tempdb 系统数据库与用户数据库非常相似。主要区别在于，在 SQL Server 关闭后，tempdb 中的数据不会保留。

回答by Maximilian Mayerl

1. The temporary tables created in TempDB are dropped when the query is completed.

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

1. 查询完成后，TempDB 中创建的临时表将被删除。

2. 我对此不确定（我必须尝试一下），但我认为理论上在 TempDB 中创建的所有表都是可见的，尽管只有创建表的用户有权访问它。