全局临时表 - SQL Server vs Oracle

Question

我正在使用Oracle 11g全局临时表，因为我需要一种解决方案来向临时表中添加行以进行连接，并且我只希望在Oracle连接/会话中添加到临时表的行被包括在内。我在Oracle中使用全局临时表是因为我希望该表在会话之间存在，这样每次创建查询时就不必重新创建表。这个方案运作得很好。

我的Oracle表定义如下：

CREATE GLOBAL TEMPORARY TABLE book_id_temp 
( 
   book_id RAW(32)
)ON COMMIT DELETE ROWS;

我在SQL Server 2008-R2中也有相同的数据库结构，并需要在SQL Server中找到类似的解决方案。我想要：

1. 打开一个SQL连接（ADO.NET）
2. 在事务内：
3. -向临时表添加行。
4. -将它们与另一个表连接，选择结果
5. -仅包括此会话期间添加的行。另一个线程可能正在执行相同的临时表。这种情况下，本地临时表可能是最好的选择？
6. 回滚整个事务。

根据我对SQL Server全局临时表的了解，这些表在连接结束后仍然存在，就像常规表和Oracle中的全局临时表一样。但是，数据范围并不清楚。只有创建行的SQL Server会话才能访问它吗，就像在Oracle中一样？ SQL Server全局临时表的可访问性是什么？您有没有建议替代方法来实现我的目标？

- Stealth Rabbi

---

Oracle全局临时表也需要根据操作进行截断...如果可以选择，我会在SQL Server中使用表变量而不是临时表，但您需要解释一下当连接丢失时需要持久性的原因才能得到真正的帮助。为什么不直接创建一个实际的表呢？ - OMG Ponies

我希望表格在会话之间存在，这样每次创建查询时就不必重新创建它。 - Stealth Rabbi

听起来你需要一个本地临时表，而不是全局临时表。您能描述一下您所需的语义吗？ - Damien_The_Unbeliever

@Damien_The_Unbeliever，我已经添加了更详细的需求说明。希望这回答了你的问题。 - Stealth Rabbi

Answer 1

Oracle中的临时表是持久化对象，保存会话本地的临时数据。而SQL Server中的临时表是临时对象。

1. 在SQL Server中，全局临时表存储的数据对所有会话可见。“全局临时表对任何用户和连接都是可见的，只要它们被创建了。”http://msdn.microsoft.com/en-us/library/ms186986.aspx
2. 全局临时表仍然是临时对象，不会永久存在，并且可能需要在使用前进行创建。“全局临时表...当引用表的所有用户从SQL Server实例断开连接时，将被删除。”http://msdn.microsoft.com/en-us/library/ms186986.aspx

我发现本地临时表或表变量是与Oracle的全局临时表最接近的，唯一的区别是每次需要创建它。

通常，在您这种情况下，第3步添加行到临时表，可以通过执行 select ... into #temp_table_name ....（相当于Oracle的 create table ... as select ...）来完成。http://msdn.microsoft.com/en-us/library/ms188029.aspx

此外，您无法在存储过程中执行以下操作：（伪代码。）

begin proc
   call another proc to create local temp table.
   use temp table
end proc

本地临时表在创建它们的存储过程返回时被销毁。

更新2014-10-14：在SQL Server的Parallel Data Warehousev版本中，本地临时表的行为不同。临时表不会在创建它们的存储过程退出时被删除，而是继续存在于整个会话中。此行为适用于：

select @@version
Microsoft SQL Server 2012 - 10.0.5108.1 (X64) Jun 24 2014 20:17:02 Copyright (c) Microsoft Corporation Parallel Data Warehouse (64-bit) on Windows NT 6.2 <X64> (Build 9200: )

- Shannon Severance

Answer 2

SQL Server上的临时表默认是本地的。该表将在会话结束后被删除。如果您执行以下脚本：

create table #Foo (
       FooID  int
      ,FooCode1  varchar (20)
)

insert table #Foo (FooID, FooCode1)
values (1001, 'X')

insert table #Foo (FooID, FooCode1)
values (1002, 'Y')

select f.FooID
      ,f.FooCode1
      ,b.BarID
      ,b.BarCode1
  from #foo f
  join bar b
    on bar.FooID = f.FooID -- (or whatever predicate)

这个查询只会返回与你在本次会话中插入到#Foo表中的匹配行。 #Foo是本地会话的临时表；您可以拥有多个具有自己#Foo临时表的会话，而不必担心命名空间冲突。 当会话关闭时，临时表将被丢弃。 如果您正在使用持久数据库连接（例如客户端-服务器桌面应用程序），则还可以在完成后显式删除#Foo。

- ConcernedOfTunbridgeWells

---

我创建了一个IDbConnection并管理事务（非持久性）并关闭连接。当连接关闭时，会话也关闭，对吗？ - Stealth Rabbi

是的，如果您关闭连接，会话将关闭。 - ConcernedOfTunbridgeWells

Answer 3

如果您在SQL Server中创建全局临时表(##table)，它将“存活”，并可通过其他会话访问，直到该会话关闭。此外，在原始会话关闭之前，您将无法使用相同的名称为不同会话创建该全局临时表，否则您将得到该表已存在的消息。对于您的目的，全局临时表不是一个好的解决方案。

本地临时表(#table)将更好，并将实现您要做的事情。

希望这可以帮助到您。

- user1135379

---

全局临时表的记录只在创建它们的事务中可访问吗？我希望不必在每个查询中都创建表。 - Stealth Rabbi

不，SQL Server全局临时表（##table_name）中的记录对所有会话都是可见的。 - Shannon Severance

Answer 4

如果您在tempdb数据库中手动创建表，您可以实现更多或更少相同的效果：

USE tempdb;

CREATE TABLE foo...

然后解决它们：

select * from tempdb..foo

这些表在会话之间不会被删除。 但是，您需要手动截断它们，没有相当于ON COMMIT DELETE ROWS的选项。

- Chris Shain

---

我的当前 ADO.NET 实现是，在我将记录插入到临时表并执行 Join 后，我在 IDbConnection 上执行 Rollback()，这可能已经足够了。你说表不会被删除，但它的内容呢？我想，如果我从未实际提交事务，那么没有其他活动连接甚至可以访问数据。 - Stealth Rabbi

它可以工作，但效率极低。在SQL Server中进行回滚非常容易出现锁定和缓慢，特别是对于大量数据而言。使用会话级临时表要高效得多，如果您坚持使用全局表，则在完成后将其截断更为明智。 - Chris Shain