临时数据库如何工作？
我试图理解 tempDB，以下是我脑海中出现的疑问。
tempDB中数据的生命周期是多长？假设一个查询正在执行一些Order By操作，并使用tempDB来执行该操作。此查询完成后，其他人也会执行使用tempDB的查询。第二个查询会在tempDB中找到第一个查询写入的记录还是将其删除？- Sql 引擎在
tempDB中是否创建了任何可见表？我如何知道由于该查询而创建了哪个临时表？ Sql引擎是否遵循任何命名约定来命名这些临时表？
我是 tempDB 的新手，所以请原谅我问这样愚蠢的（如果有的话）问题:-)
如果有人能给我指出一个可以帮助我的好资源，那就太好了了解 tempDB。
I am trying to understand the tempDB and following are the doubts popping in my mind.
- What is the lifetime of data in
tempDB? Say a query is doing someOrder Byand usestempDBfor performing that. After this query finishes, someone else also executes a query which utilizes thetempDB. Will the second query find records written by first query in thetempDBor will they be deleted? - Are there any visible tables 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?
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.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（3）
临时表存储在 tempdb 中，直到连接被删除（或者对于全局临时表，当最后一个使用它的连接被删除时）。当您使用完表后，您还可以（这是一个很好的做法）通过 drop table 语句手动删除该表。
不，如果临时表是本地临时表，其他人无法看到您的临时表（他们可以查看和使用全局临时表）多个人可以运行使用相同临时表名称的命令，但它们不会在本地临时表中重叠，因此您可以有一个名为 #test 的表，其他 10,000 个用户也可以，但每个用户都有自己的结构和数据。
您通常不想在 tempdb 中查找临时表。可以检查是否存在，但这是我唯一一次直接引用 tempdb。只需使用您的临时表名称即可。下面检查是否存在的示例
您可以通过在名称前面加上 #（对于本地表，您 999.9% 的时间都会使用的表）和 ##（对于全局临时表）来命名临时表，然后是您想要的名称的其余部分。
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
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.
有几篇 MSDN 文章可能是有关 SQL Server 中 tempDB 数据库的最佳信息来源。
tempdb 数据库
还有tempdb 和索引创建、此博文 以及在 SQL Server 2005 中使用 tempdb 其中状态：
There's a few MSDN articles that are probably the best source of information on the tempDB database in SQL Server.
tempdb Database
There's also tempdb and Index Creation, this blog post along with Working with tempdb in SQL Server 2005 which states:
查询完成后，TempDB 中创建的临时表将被删除。
我对此不确定（我必须尝试一下），但我认为理论上在 TempDB 中创建的所有表都是可见的，尽管只有创建该表的用户有权访问它。
The temporary tables created in TempDB are dropped when the query is completed.
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.