SQL Server(存储过程)临时表与滞后名称解析

原创 2005年05月05日 11:58:00

    开发中,我们经常用Create Procedure命令创建存储过程,而在创建过程时实际发生的是,Query Analyzer检查其语法,检查完毕并正确后将其插入系统表syscomments中,而在过程中引用的对象名称在该过程被执行之前不被解析,这个技术叫做滞后名称解析。然而,这个技术却并不是和我们想象的一样,它也有鞭长莫及的地方。下面来看一个过程:

CREATE PROC testp @var int

AS

IF @var=1

    CREATE TABLE #mytemp(k1 int identity,c1 int)

ELSE

    CREATE TABLE #mytemp(k1 int identity,c1 varchar(2))

INSERT #mytemp DEFAULT VALUES

SELECT c1 FROM #mytemp

GO

当编译该过程时,出现以下错误:

也许你会问,@var不可能既等于1又不等于1,那问什麽会出现以上的错误呢大家不要头晕,现在是在检查语法的时候,检查语法是逐行向下的,编译器不会管你的@var等不等于1,它只检查语法错误!可能你又要说:上面不是说对象名称滞后解析吗?临时表就是一个对象啊,而#mytemp是它的名称,既然是滞后解析,那就是忽略该名称不解析,怎麽可能会出现这种错误,别急,问题就出在这里对比看一下下面的过程:

CREATE PROC testp2 @var int

AS

IF @var=1

    CREATE TABLE tempdb..mytemp(k1 int identity,c1 int)

ELSE

    CREATE TABLE tempdb..mytemp(k1 int identity,c1 varchar(2))

INSERT mytemp DEFAULT VALUES

SELECT c1 FROM mytemp

GO

奇怪的事情发生了,创建这个过程顺利地通过,这里只是把临时表替换成了一个永久性表,并没做其他任何改变,难道SQL Server在乎创建的是临时表还是永久性表,呵呵?发生的事情似乎是这样的,在将过程插入syscomments之前(存储过程编译后存放于系统表syscomments) SQL Server参照临时表解析CREATE TABLE,也许你会说将临时表换成一个table类型的变量就不会出现这样的问题,可是事实是不行,数据类型table同样受此局限。似乎是从SQL Server 7.0开始,支持永久性表的滞后名称解析,但不支持临时表的滞后名称解析。但不管是哪一种情况,第一个代码都无法执行,下面采取一个迂回策略来解决这个问题,请看:

CREATE PROC testp @var int

AS

CREATE TABLE #mytemp(k1 int identity)

IF @var=1

    ALTER TABLE #mytemp ADD c1 int

ELSE

    ALTER TABLE #mytemp ADD c1 varchar(2)

INSERT #mytemp DEFAULT VALUES

EXEC('SELECT c1 FROM #mytemp')

GO

在这里只创建表一次,然后修改它,注意`EXEC('SELECT c1 FROM #mytemp')

这条语句是必要的,因爲新添加的列对于添加它的过程并非立即可见,如果去掉EXEC执行该过程时,将会显示这样的错误:

再説一次,新添加的列对于添加它的过程并非立即可见然而以上的代码却带来了一个性能问题,因爲任何创建临时表并近一步处理它的存储过程都将导致该过程的执行计划重新编译,这对于高吞吐量环境中的大型过程而言,性能会大打折扣以下过程将解决这个问题:

CREATE PROC test4

AS

INSERT #temp DEFAULT VALUES

SELECT c1 FROM #temp

GO

 

CREATE PROC test3

AS

CREATE TABLE #temp (k1 int identity,c1 varchar(2))

EXEC dbo.test4

GO

 

CREATE PROC test2

AS

CREATE TABLE #temp (k1 int identity,c1 int)

EXEC dbo.test4

GO

 

CREATE PROC test @var int

AS

IF @var=1

    EXEC dbo.test2

ELSE

    EXEC dbo.test3

GO

复杂性提高了,但是还是可以解决一些问题,另外,之所以在第二和第三个过程中冗余地调用第四个过程,是因爲临时表一旦超过其作用域就自动被删除!

在存储过程中编写正确的事务处理代码(SQL Server 2000 & 2005)

在 SQL Server 中数据库事务处理是个重要的概念,也稍微有些不容易理解,很多 SQL 初学者编写的事务处理代码存往往存在漏洞,本文介绍了三种不同的方法,举例说明了如何在存储过程事务处理中编写正...

sql 存储过程的的好处和缺点

此内容均来自互联网 存储过程(Stored Procedure)是一组为了完成特定功能的SQL 语句集,经编译后存储在数据库。中用户通过指定存储过程的名字并给出参数(如果该存储过程带有参数)来执行它...

SQL Server数据库的存储过程中定义的临时表,真的有必要显式删除(drop table #tableName)吗?

问题背景 在写SQL Server存储过程中,如果存储过程中定义了临时表, 有些人习惯在存储过程结束的时候一个一个显式地删除过程中定义的临时表(drop table #tName),有些人又没...

sql server 存储过程中使用变量表,临时表的分析(续)

原文: sql server 存储过程的优化.(变量表,临时表的简单分析) sql server 存储过程中使用变量表,临时表的分析(续)  最近,我有一朋友,对...
  • yenange
  • yenange
  • 2013年08月16日 17:13
  • 1030

sql server 存储过程的优化.(变量表,临时表的简单分析)

1.变量表: declare @t table ( id nvarchar(50), supno nvarchar(50), eta datetime ) insert  @t s...

sql server 存储过程中使用变量表,临时表的分析

sql server 存储过程中使用变量表,临时表的分析(续)       最近,我有一朋友,对我说他的数据库中的很多存储过程,执行都是超时.让我替他看看是什么原因.我一看,原来他的存储...

SQL Server中在存储过程中使用游标修改表中数据

在SQLServer中的存储过程中使用游标修改另外一张表中的数据,关键在于理清达到目标所需要完成的先后顺序,本文用一个非常简单的例子做出说明...

sql server 2005数据库同步 表,存储过程,视图.

以下实现复制步骤(以快照复制为例) 运行平台SQL SERVER 2005 一、准备工作: 1.建立一个 WINDOWS 用户,设置为管理员权限,并设置密码,作为发布快照文件的有效访问用户。 2.在S...

SQL Server判断数据库、表、存储过程、函数是否存在

--1.判断数据库是否存在 IF EXISTS ( SELECT * FROM sys.databases WHERE NAME = '[数据库名]' ...
内容举报
返回顶部
收藏助手
不良信息举报
您举报文章:SQL Server(存储过程)临时表与滞后名称解析
举报原因:
原因补充:

(最多只允许输入30个字)