在这之前我们先了解CTE，临时表，表变量的基本概念
一、
临时表：临时表有两种类型：本地表和全局表。在与首次创建或引用表时相同的 SQL Server 实例连接期间，本地临时表只对于创建者是可见的。当用户与 SQL Server 实例断开连接后，将删除本地临时表。全局临时表在创建后对任何用户和任何连接都是可见的，当引用该表的所有用户都与 SQL Server 实例断开连接后，将删除全局临时表。
二、
CTE：CTE(Common Table Expression) ，即公用表表达式，可以认为是在单个 SELECT、INSERT、UPDATE、DELETE 或 CREATE ⅥEW 语句的执行范围内定义的临时结果集。CTE 与派生表类似，具体表现在不存储为对象，并且只在查询期间有效。与派生表的不同之处在于，CTE 可自引用，还可在同一查询中引用多次。
三、
表变量：说白了和表没什么区别，只不过是以变量的形式存在，你像访问普通的表一样去访问，去做增删改查的操作。最重要的是为什么有了临时表，还要整个表变量呢，存在即合理，表变量确实有其优点，例如它不需要在存储过程结束的时候释放（DROP），导致存储过程编译更少等等。
CTE相对比较简单一点，
非递归的CTE从物理来说，有点类似普通的视图的概念，在编译的时候优化器会把它扩展开来。 比如下面的,QO会把1转化为2
1，WITH O_CTE ([name] , [object_id] )
AS
(SELECT [name] , [object_id] FROM [sys].[all_objects])
SELECT top 11 O.[name] , O.[object_id]
FROM O_CTE O INNER JOIN [sys].[all_columns] C ON O.[object_id]=C.[object_id] ;
GO
-- transformation
2，SELECT top 11 O.[name] , O.[object_id]
FROM ( SELECT [name] , [object_id] FROM [sys].[all_objects])O INNER JOIN [sys].[all_columns] C ON O.[object_id]=C.[object_id] ;
GO
表变量与临时表很复杂，所以误解相当的多,首先说一下相关的误解的东西。
1),存储位置.一句话:表变量与临时表在存储位置上没有本质区别。
2，表变量与临时表跟tempdb关系。表变量与临时表在tempdb 实例化，并且最初在BPOOL中产生，并且在checkpoint发生之前，它们就已经存在于tempdb 中了，虽然它还没有刷入磁盘，但是它们就已经属于并且存在于tempdb中了。 当然在checkpoint后生之后，BPOOL中的数据被输入磁盘，但是不管有没有发生checkpoint，数据有没有被输入磁盘，换句话说，无论表变量与临时表只存是在BPOOL中 ，还是同时存在于BPOOL与磁盘（manual checkpoint），或者只存在于磁盘（lazy writer后），表变量与临时表都存在于tempdb中。
上面的描述同样适用于用户数据库中的用户表，就好像是在用户数据库USERDB中用户表usertable生成1000条记录，那它肯定是首先在BPOOL中生成的，但是在checkpoint之前，它也是不会被刷入磁盘的，那这个1000条记录属于数据库USERDB吗？当然拉。
3，表变量不参与事物吗？那为什么还要产生日志呢？表变量并非不参与事物，而不参与用户事物，系统事物还是参与的，所以会产生日志。
4），表变量不参与锁机制吗？表变量当然会参与锁机制。
DBCC TRACEOFF(1200,-1)
GO
DECLARE @TV TABLE (CL int)
DBCC TRACEON(1200,-1,3604)
INSERT INTO @TV (CL) VALUES (1)
DBCC TRACEOFF(1200,-1)
GO
5，表变量会不会重编译，而临时表会重编译吗？当然不是，临时表的重编译是需要条件的。
比如下面的，你执行多次Proc1，但是你只会看到一次的SP:Recompile，这说话，临时表的计划被重用了。
create procedure Proc1
as
begin
create table #t1(a int, b int);
insert into #t1 values(1,2);
select * from #t1;
end
exec Proc1
再没有达到某些限定的阀值之前，是不需要重编译的，具体公式如下。
If n < 6, Recompilation threshold = 6.
If 6 <= n <= 500, Recompilation threshold = 500.
If n > 500, Recompilation threshold = 500 + 0.20 * n.
另外在执行计划中， Estimated Rowcount是一个非常重要的东西，因为它直接决定了执行计划的选择，下面来问大家一些问题，后面会有问题的答案，大家可以先想想再看答案。
----------------------------------------------------------------------------------------
问题
----------------------------------------------------------------------------------------
先创建表并插入数据
SET NOCOUNT ON;
DECLARE @tb TABLE (
[C1] [nvarchar](50) NOT NULL,
[C2] [date] NOT NULL,
[C3] [nchar](1) NOT NULL,
[C4] bit NULL,
[C5] bit NOT NULL);
INSERT INTO @tb
SELECT '1',getdate(),'1','1','1'
--生成1024条测试记录
declare @i int
set @i=0
while @i<10
begin
INSERT INTO @tb
SELECT * from @tb
set @i=@i+1
end
SET STATISTICS PROFILE ON;
SELECT* from @tb
--1.Estimated Rowcount为1，这个很明显。
SELECT* from @tb OPTION(RECOMPILE)
--为什么 Estimated Rowcount为1024？
SELECT* from @tb where [C1] like '1' OPTION(RECOMPILE)
--为什么 Estimated Rowcount为102.4？
SELECT* from @tb where [C1] Not like '1' OPTION(RECOMPILE)
--为什么 Estimated Rowcount为921.6？
SELECT* from @tb where [C1] between '1' and '2' OPTION(RECOMPILE)
--为什么 Estimated Rowcount为92.16？
SELECT * from @tb where [C1] not between '1' and '2' OPTION(RECOMPILE)
--为什么 Estimated Rowcount为522.24？
SELECT* from @tb where [C4]=1 OPTION(RECOMPILE)
--为什么 Estimated Rowcount为337.92？
SELECT* from @tb where [C5]=1 OPTION(RECOMPILE)
--为什么 Estimated Rowcount为512？
SELECT* from @tb where [C2]< getdate() OPTION(RECOMPILE)
--为什么 Estimated Rowcount为307.2？
SET STATISTICS PROFILE OFF;
----------------------------------------------------------------------------------------
答案
----------------------------------------------------------------------------------------
RECOMPILE使得@tb在运行insert into之后重新评估@tb的行数，因为这个时候已经insert完毕，所以能准确的得到它的行数。
因为总共插入了1024行，所以@tb的total count 为1024"。
SELECT* from @tb OPTION(RECOMPILE)
--为什么 Estimated Rowcount为1024？
因为总共就插入了1024行，插入之后再次评估它的行数，就能得它比较准确的值。
SELECT* from @tb where [C1] like '1' OPTION(RECOMPILE)
--为什么 Estimated Rowcount为102.4？
对于不模糊的like，Estimated Rowcount=total count *10%=1024*10%=102.4
Estimated RowcountSELECT* from @tb where [C1] Not like '1' OPTION(RECOMPILE)
--为什么 Estimated Rowcount为921.6？
Not like 的 Estimated Rowcount=total count -like Estimated Rowcount=1024-102.4=921.6
SELECT* from @tb where [C1] between '1' and '2' OPTION(RECOMPILE)
--为什么 Estimated Rowcount为92.16？
对于between 的Estimated Rowcount=total count *9%=1024*9%=92.16
SELECT * from @tb where [C1] not between '1' and '2' OPTION(RECOMPILE)
--为什么 Estimated Rowcount为522.24？
not between 的Estimated Rowcount为total count *51%=522.24
SELECT* from @tb where [C5]=1 OPTION(RECOMPILE)
--为什么 Estimated Rowcount为512？
[C5]的类型为 bit NOT NULL，所以有2种值，所以Estimated Rowcount=total count *1/2=512
SELECT* from @tb where [C4]=1 OPTION(RECOMPILE)
--为什么 Estimated Rowcount为337.92？
[C4]的类型为 bit NULL，所以有3种值的可能，所以Estimated Rowcount=total count *1/3=337.92
SELECT* from @tb where [C2]< getdate() OPTION(RECOMPILE)
--为什么 Estimated Rowcount为307.2？
一般对于 >或者<Estimated Rowcount为 Estimated Rowcount*30%=307.2p