sqlserver 存储过程中使用临时表到底会不会导致重编译

　　插入语句（insert into #t select id,name from test1 where id<@i）和查询语句（select * from #t），

　　因为#t表还没有被创建，因为这两句并没有被编译，

　　编译的时候的执行计划并没有完全完成，

　　当这个存储过程执行的时候，临时表才被创建，此时才真正的开始编译临时表对象的语句，这个编译的过程是执行的时候完成的，而不是纯粹的编译阶段完成的

　　所以这是Deferred Complie，也即是运行时才进行的编译，就是所谓的延迟编译（Deferred Complie）。

2，第二个问题，重新运行临时表的时候，按道理，因为创建了临时表，必然导致架构的更改，为什么没有重编译？

　　这个是因为，存储过程中使用了临时表，对临时表的使用是引用其“名称”（比如这里的#t），而非ID（从临时数据库中查询sys.sysobjects）

　　虽然多个会话同时运行这个SP的话，每个会话都会生成一个临时表，每个会话生成的临时表的ID都是不同的，

　　但是要注意的是，存储过程中并没有直接使用临时表对象的ID，而是临时表名字本身，

　　第一次运行之后，缓存的执行计划与第二次运行时一样的，所以第二次运行这个SP可以重用这个第一次生成的执行计划，

上面说了，在某些情况下，存储过程中使用临时表会导致重编译，这是在什么情况下发生的呢？

因为在某些情况下，要先生成临时表，然后以动态sql的方式去执行一段有临时表参与的sql，此时对于临时表的引用是引用其ID，而不是名称

这个要归结于对于临时表的调用方式，当存储过程中定义了临时表，用exec或者是sp_executesql的方式调用的时候，这两种执行sql的方式相当于新建了会话，

此时因为不同回话之间，同一个临时表生成的ID是不同的，此时才会导致存储过程中发生sechme change的重编译

上代码

create proc testRecompile2(@i int)
as
begin
	create table #t (id int,name varchar(50))
	insert into #t select id,name from test1 where id<=@i
	exec(‘select * from #t‘)
end

DBCC FREEPROCCACHE
--第一次运行，代入参数1
exec testRecompile2 1
--第二次运行，代入参数2
exec testRecompile2 2

在存储过程中创建了临时表，以exec或者sp_executesql的方式执行临时表的sql的时候，才会发生因为schema change导致的重编译，

因为这两种方式执行sql，相当于新建会话去执行sql，此时对于临时表的引用，是引用临时表生成的ID，不同会话之间的临时表对象的ID是不同的，所以无法重用执行计划，会发生重编译

另外，对于统计信息变更导致的重编译，就不多说了，这个不仅仅会发生在临时表上，普通的物理表上也会因为统计信息变更导致重编译，不止是临时表，唯一的区别就是，导致临时表与物理表统计信息变更的阀值是不一样的

我们知道

这个也很容易验证，临时表统计信息更新的阀值依赖于临时表中数据的变化幅度，这个阀值如下

If n < 6, Recompilation threshold = 6.

If 6 <= n <= 500, Recompilation threshold = 500.

If n > 500, Recompilation threshold = 500 + 0.20 * n.

还拿第一个测试的SP做示例，分别两次执行如下SP，

--第一次运行，代入参数1
exec testRecompile 1
--第二次运行，代入参数2
exec testRecompile 2
因为第一次参数1 ，第二次参数是2，不满足If n < 6, Recompilation threshold = 6.所以不会因为统计信息变更导致重编译
但是当执行 exec testRecompile 8的时候，达到这个这个阀值，If n < 6, Recompilation threshold = 6.SP就会因为统计信息变更而重编译，这个很简单，我就不做截图了

sqlserver 存储过程中使用临时表到底会不会导致重编译

标签：