SQL中系统临时表的使用和禁忌 及 Exec(SQLScript) 的使用[转]

SQL中系统临时表的使用和禁忌 及 Exec(SQLScript) 的使用

2008-11-01 14:39:57　来源:不详　作者:佚名 点击: 1489

---

以前在SQL 中写SP 时，如比较复杂时，喜欢中间使用临时表来暂存相关记录，这样的好处有很多，提高效率，提高程序的可读性等。当然后临时表的使用，一般均会使用用户临时表，即 #TempTable， 但有一些情况下，偶尔也会使用系统临时表，即 ##TempTable。

 

 

　　以前在SQL 中写SP 时，如比较复杂时，喜欢中间使用临时表来暂存相关记录，这样的好处有很多，提高效率，提高程序的可读性等。当然后临时表的使用，一般均会使用用户临时表，即 #TempTable， 但有一些情况下，偶尔也会使用系统临时表，即 ##TempTable。

两种临时表的的使用语法差不多，可用几种方法来建立，可 Create ，也可 Select Into 。

当然关键的是系统临时表和用户临时表的区别:(如下)
1)用户临时表:用户临时表的名称以单个数字符号(#)开头;
用户临时表只对创建这个表的用户的Session可见,对其他进程是不可见的.
当创建它的进程消失时这个临时表就自动删除.
2)系统临时表:系统临时表的名称以数字符号(##)开头
全局临时表对整个SQL Server实例都可见,但是所有访问它的Session都消失的时候,它也自动删除.



明白了这些就知道了他们的用途和限制，但有一些地方还是容易出问题，故在此专门列出。



1， 在使用 Exec(SQLScript) 执行Script 时，其间也相当于单独有一个进程处理，故执行期间内如果创建用户临时表的话，在执行完成后也就结束了，即执行完成后，你不可以使用在 SQLScript 中生成的用户临时表，可以用系统临时表代替。

2， 在使用用户临时表时，有一个问题要注意，就是最好在建立时指定其用户为 dbo ，以避免可能的问题；

3， 在使用系统临时表时，一定要考虑到，不可以将其用于多用户使用的环境功能或系统中，否则就可能出现冲突的问题，导致结果不可预料。



如果在多用户使用的环境中使用系统临时表，则可能会出现多个用户同时对同一系统临时表进行处理，从而导致冲突和数据的错误。以前没有注意这一点，我就因此而浪费过不少的时间。



有时需要使用Exec(SQLScript)方式产生数据，但其中只能使用系统临时表，如何处理呢？

1， 可以预先定义好一个用户临时表，然后使用 Insert #TempTable Exec(SQLScript) 的方式，即可将Exec 产生的结果记录加入用户临时表，从而避免使用系统临时表；

2， 从根本上避免使用 Exec() ，可用其它方式代替。



Exec() 的使用是因为有一些 Script 比较复杂，其中需要一些组合字符，如 in ('','','') 或其它可能的情况，在此情况，无法直接使用一般的Script 产生记录，只能先产生一个组合的Script ，然后用Exec 执行。

 

 

 

 

修复: 计划重用授予不正确的查询结果

bug: 57746 (sqlbug_70)

症状

时 EXEC 语句用于创建本地临时表，该语句用于计划可能会错误地缓存和重用即使随后的语句创建临时表具有不同的列。以下各项是此问题的特征： 一个 SELECT I...

时 EXEC 语句用于创建本地临时表，该语句用于计划可能会错误地缓存和重用即使随后的语句创建临时表具有不同的列。

以下各项是此问题的特征：

• 一个 SELECT INTO 用来创建一个表内 EXEC 语句。
• 创建表是本地的临时表。
• 对于不同的表结构重复使用相同的本地临时表名。
• 不同的表结构可以包含数字的列，和数据类型的不同列名称。

回到顶端

解决方案

您可以通过以下方法解决此问题： 每次使用给定名称的临时表，请使用相同的结构。 不要使用 EXEC 语句为一个 SELECT INTO 对于本地临时表执行操作。...

您可以通过以下方法解决此问题：

• 每次使用给定名称的临时表，请使用相同的结构。
• 不要使用 EXEC 语句为一个 SELECT INTO 对于本地临时表执行操作。
• 使用永久表或全局临时表。
• 发出以下命令以清除缓存查询的执行之间:

  DBCC FreeProcCache

  笔记，这将清除出缓存的所有计划，并可能对性能产生不利影响。

回到顶端

状态

Microsoft 已经确认这是在 SQL Server 7.0 中的出现问题。 此问题已得到纠正美国 Service Pack 3 中，对于 Microsof...

Microsoft 已经确认这是在 SQL Server 7.0 中的出现问题。 此问题已得到纠正美国 Service Pack 3 中，对于 Microsoft SQL Server 7.0。 有关详细信息，请单击下面的文章编号，以查看 Microsoft 知识库中相应的文章：

274799  (http://support.microsoft.com/kb/274799/ ) INF： 如何获得有关 Microsoft SQL Server 7.0 和 Microsoft 数据引擎 (MSDE) 1.0 的 Service Pack 3

有关详细信息，请与您的主要支持提供者。

回到顶端

更多信息

下面的示例阐释了这一点： print '-- local temp tables - cached table structure is incorrectly...

下面的示例阐释了这一点：

print '-- local temp tables - cached table structure is incorrectly re-used'
EXEC ('SELECT 1 AS Col1, 2 AS Col2 INTO #temp
UPDATE #temp SET Col2 = 3 
SELECT * from #temp
DROP TABLE #temp')
EXEC ('SELECT 2 AS Col2, 1 AS Col1 INTO #temp
UPDATE #temp SET Col2 = 3 
SELECT * from #temp
DROP TABLE #temp')
SELECT 2 AS Col2, 'xyz' AS Col1, 3 AS cC INTO #temp
UPDATE #temp SET Col2 = 3 
SELECT * FROM #temp
DROP TABLE #temp
go

print '-- global temp tables - correct results'
EXEC ('SELECT 1 AS Col1, 2 AS Col2 INTO ##Global
UPDATE ##Global SET Col2 = 3 
SELECT * from ##Global
DROP TABLE ##Global')
EXEC ('SELECT 2 AS Col2, 1 AS Col1 INTO ##Global
UPDATE ##Global SET Col2 = 3 
SELECT * from ##Global
DROP TABLE ##Global')
SELECT 2 AS Col2, 1 AS Col1 INTO #Global
UPDATE #Global SET Col2 = 3 
SELECT * FROM #Global
DROP TABLE #Global

print '-- permanent tables - correct results'
EXEC ('SELECT 1 AS Col1, 2 AS Col2 INTO pubs..t1
UPDATE pubs..t1 SET Col2 = 3 
SELECT * from pubs..t1
DROP TABLE pubs..t1')
EXEC ('SELECT 2 AS Col2, 1 AS Col1 INTO pubs..t1
UPDATE pubs..t1 SET Col2 = 3 
SELECT * from pubs..t1
DROP TABLE pubs..t1')
SELECT 2 AS Col2, 1 AS Col1 INTO pubs..t1
UPDATE pubs..t1 SET Col2 = 3 
SELECT * FROM pubs..t1
DROP TABLE pubs..t1

print '-- no EXEC involved - correct results'
SELECT 2 AS Col2, 1 AS Col1 INTO #Select
UPDATE #Select SET Col2 = 3 
SELECT * FROM #Select
DROP TABLE #Select
go
SELECT 1 AS Col1, 2 AS Col2 INTO #Select
UPDATE #Select SET Col2 = 3
SELECT * FROM #Select
DROP TABLE #Select
go
dbcc freeproccache
				

附加约束观察到的行为是：

• 数据不会保留表结构的语句之间。
• 如果随后执行是从相同的连接或不同的连接不重要。

回到顶端

---

这篇文章中的信息适用于:

• Microsoft SQL Server 7.0 标准版

回到顶端