tempDB 如何工作? [英] How tempDB works?

问题描述

我正在尝试理解 tempDB，以下是我脑海中浮现的疑问.

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

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

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?

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

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

如果有人能给我指出一个可以帮助我了解 tempDB 的好资源，那就太好了.

推荐答案

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

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.

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

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.

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

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  

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

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.

这篇关于tempDB 如何工作?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！