本文出处：http://www.cnblogs.com/wy123/p/6262800.html
在考虑重编译T-SQL(或者存储过程)的时候，有两种方式可以实现强制重编译（前提是忽略导致重编译的其他因素的情况下，比如重建索引，更新统计信息等等），
一是基于WITH RECOMPILE的存储过程级别重编译，另外一种是基于OPTION(RECOMPILE)的语句级重编译。
之前了解的比较浅，仅仅认为是前者就是编译整个存储过程中的所有的语句，后者是重编译存储过程中的某一个语句，也没有追究到底是不是仅仅只有这么一点区别。
事实上在某些特定情况下，两者的区别并非仅仅是存储过程级重编译和语句级重编译的区别，
从编译生成的执行计划来看，这两种强制编译的方式内在机制差异还是比较大的。
这里同时引申出来另外一个问题：The Parameter Embedding Optimization（怎么翻译？也没有中文资料中提到The Parameter Embedding Optimization，勉强翻译为“参数植入优化”）
本文通过一个简单的示例来说明这两者的区别(测试环境为SQL Server2014)。这里首先感谢UEST同学提供的参考资料和指导建议。
WITH RECOMPILE 和 OPTION(RECOMPILE)使用上的区别
关于存储过程级别的重编译，典型用法如下，在存储过程参数之后指定“WITH RECOMPILE”
CREATE PROCEDURE TestRecompile_WithRecompile
(
@p_parameter int
)WITH RECOMPILE
AS
BEGIN
SET NOCOUNT ON;
SELECT * FROM TestRecompile WHERE Id = @p_parameter OR @p_parameter IS NULL
END
GO
关于语句级重编译，典型用法如下，在某一条SQL语句的末尾指定OPTION(RECOMPILE)
CREATE PROCEDURE TestRecompile_OptionRecompile
(
@p_parameter VARCHAR(50)
)
AS
BEGIN
SET NOCOUNT ON;
SELECT * FROM TestRecompile WHERE Id = @p_parameter OR @p_parameter IS NULL OPTION(RECOMPILE)
END
GO
按照惯例，先搭建一个测试环境
创建一张TestRecompile的表，也即上面存储过程中用到的表，插入100W行数据，Id字段上建立一个名字为idx_id的索引
CREATE TABLE TestRecompile
(
Id int,
Value varchar(50)
)
GO DECLARE @i int = 0
WHILE @i<=1000000
BEGIN
INSERT INTO TestRecompile VALUES (@i,NEWID())
SET @i = @i+1
END CREATE INDEX idx_Id ON TestRecompile(Id)
GO
WITH RECOMPILE 和 OPTION(RECOMPILE)使用时重编译生成的执行计划的异同
如果说With Recompile存储过程级的重编译和Option Recompile的SQL语句级的重编译效果是一样的话，
由上面的存储过程可知，存储过程中仅仅只有一句SQL代码，那么存储过程级别的重编译和SQL语句级别的重编译都是编译这一句SQL
如果这样的话，两者在输入同样参数的情况下执行计划也应该是一样的，那么到底一样不一样呢？
首先来看TestRecompile_WithRecompile这个存储过程的执行计划，可以看到是一个索引扫描(INDEX SCAN)
然后再来看TestRecompile_OptionRecompile的执行计划，带入同样的参数
至此，可以看出，虽然都用到索引，很明显第一个语句是索引扫描（INDEX SCAN），第二个语句是索引查找（INDEX SEEK）
可以证明：在存储过程级指定 WITH RECOMPILE 强制重编译 和SQL语句级指定的OPTION(RECOMPILE)强制重编译，相同条件下生成的执行计划是不一样的。
为什么WITH RECOMPILE强制重编译 和 OPTION(RECOMPILE)强制重编译得到的执行计划是不一样的？
WITH RECOMPILE强制重编译是每次运行存储过程，都根据当前的参数情况做一次重编译，
首先我们暂时先不纠结为什么第一种方法用不到索引查找（INDEX的SEEK）。
事实上正式因为使用了Id = @p_parameter OR @p_parameter IS NULL这种写法导致的，具体我后面做解释。
那么对于OPTION(RECOMPILE)强制重编译存储过程中同样写法的SQL语句，为什么有能用到索引了呢？
因为在用OPTION(RECOMPILE)强制重编译的时候，这里涉及到一个“Parameter Embedding Optimization”编译问题，
事实上我之前也没有听说过这个名词，直译过来就是“参数植入编译”（不知道恰不恰当）
OPTION(RECOMPILE)强制重编译在一定程度上增强和优化重编译的效果，
参考这里:https://sqlperformance.com/2013/08/t-sql-queries/parameter-sniffing-embedding-and-the-recompile-options，文章中分析的极度牛逼，案例也非常精彩
原文中是这么解释的：
The Parameter Embedding Optimization takes this process a step further: query parameters are replaced with literal constant values during query parsing.
The parser is capable of surprisingly complex simplifications, and subsequent query optimization may refine things even further.
翻译过来大概意思就是：
相比WITH RECOMPILE这种强制重编译的方式，OPTION(RECOMPILE)中的Parameter Embedding Optimization机制更进一步，解析查询的过程中，参数值被字面常量所替代
解析器神奇地把复杂的问题简化。至于怎么简化了，还是强烈建议参考原文，演示的案例相当吊。
至于怎么简化，这里大概做一下解释，原文中的解释更加详细和有趣。
首先，SQL语句是这么写的：SELECT * FROM TestRecompile WHERE Id = @p_parameter OR @p_parameter IS NULL
当“植入参数”之后，也即上文中使用的@p_parameter = 123456，SQL语句变成了SELECT * FROM TestRecompile WHERE Id = 12345 OR 12345 IS NULL
因为OR 12345 IS NULL是永远不成立的，甚至可以认为是将SQL语句直接简化成了SELECT * FROM TestRecompile WHERE Id = 12345 ，这样子的话，在当前情况下，肯定是可以用到索引的。
因此，OPTION(RECOMPILE)强制重编译的SQL在编译并且简化之后，就变成了如下的SQL，这里解释还是感觉有点牵强的，没有原文有说服力。
那么再回头看WITH RECOMPILE强制重编译，WITH RECOMPILE强制重编译的时候，没有能够做到OPTION(RECOMPILE)强制重编译中的“解析器神奇地把复杂的问题简化”
参考这个链接：http://sqlinthewild.co.za/index.php/2009/03/19/catch-all-queries/
对于类似WHERE Id = @p_parameter OR @p_parameter IS NULL这种查询方式，
上述文章中是这么解释的：
The problem with these types of queries is that there is no stable plan.
The optimal plan differs completely depending on what paramters are passed.
The optimiser can tell that and it plays safe. It creates plans that will always work. That’s (one of the reasons) why in the first example it was an index scan, not an index seek.
翻译过来大概意思就是：
这种类型的查询问题在于没有固定的执行计划，
优化方案是基于具体传入进来的参数值的，
优化器只能做到保证安全性（plays safe），他创建的执行计划确保总是可以正常工作的。
我这里补充解释一下 it plays safe在我的理解：
如果@p_parameter 参数非空，走索引Seek完全没有问题。
如果@p_parameter 为null，此时and (Id= @p_parameter or @p_parameter is null )这个条件恒成立，如果再走索引Seek会出现什么结果？
如果继续采用Index Seek的方式执行，语义上变成了是查找Id为null的值，这样的话逻辑上已经错误了。
因此出现这种写法，为了安全起见（上文所谓的plays safe），优化器只能选择一个这种的索引的扫描的方案（所谓的always work的执行计划）
关于OPTION(RECOMPILE)在SQL语句级重编译神奇的魔力，他会根据具体的参数做到真正的重编译，我们在做一个测试：
这一次设置@p_parameter = null，看看是不是又重新编译了一个合理的执行计划，没错，这次它生成了一个全表扫描的执行计划，也是没有问题的。
唯一有瑕疵的地方时，相对WITH RECOMPILE强制重编译的方式，他的执行计划没有用到并行。这也是WITH RECOMPILE和OPTION(RECOMPILE)两种强制重编译生成执行计划的区别
但是不能否认OPTION(RECOMPILE)强制重编译中的Parameter Embedding Optimization这种优化机制的特性
而此时WITH RECOMPILE强制重编译方式的执行计划，在传入参数值为null的时候，生成的是并行的执行计划
现在来解释为什么非常强烈不建议写这种SQL：SELECT * FROM TestRecompile WHERE Id = @p_parameter OR @p_parameter IS NULL
我在之前也写过http://www.cnblogs.com/wy123/p/5958047.html，感觉没有彻底解释清楚索引抑制问题的原因。
开发中常见的一个潜在的多个条件的查询SQL，具体的查询条件是依赖于用户输入的，
比如提供给用户三个查询条件可选的查询条件，用户可以输入一个，两个或者三个，这个太常见了，也不用再解释了
那么我们就要构造出适应这种查询的一种方案
面对这种catch-all-queries的查询方式，其中方案之一就是类似于这种写法
SELECT * FROM TestRecompile
WHERE (parameter1 = @p_parameter1 OR @p_parameter1 IS NULL)
and (parameter2 = @p_parameter2 OR @p_parameter2 IS NULL)
and (parameter3 = @p_parameter3 OR @p_parameter3 IS NULL)
这种最大的问题就是在查询列上有索引，且查询列上接收到的输入参数非空的时候，是会抑制到索引的使用的
上文中演示了，虽然用到了Id 列上的索引，采用的是INDEX SCAN，比全表扫描（TABLE SCAN）强一点点，他跟真正用到INDEX SEEK在效率上讲，完全是两码事，
所以我们在开发的过程中强烈不建议使用 Id = @p_parameter OR @p_parameter IS NULL这种写法，
当然，在不考虑parameter sinffing问题的时候，我们首选参数化动态SQL，即便是非参数化动态SQL（EXEC的方式执行一个拼凑出来的字符串），也比Id = @p_parameter OR @p_parameter IS NULL这种方式好
如果有人进一步问：为什么查询条件中Id = @p_parameter OR @p_parameter IS NULL这种写法会抑制到索引的使用，真的是一个很难解释清楚的问题，解释不清楚也是一件很尴尬的事。
这种逻辑之所以抑制到索引的最优化使用，真如上文分析的，优化器没有真正的用到INDEX SEEK，是为了安全起见（上文所谓的plays safe）考虑
说道到这里我又开始凌乱了，也就是WITH RECOMPILE和OPTION(RECOMPILE)这两种方式的造强制，有一种只可意会不可言传的感觉。
这就是即便是编译的过程中知道具体的参数值，也做到编译出来INDEX SEEK的执行计划的原因
总是我在http://sqlinthewild.co.za/index.php/2009/03/19/catch-all-queries/这里找到了跟我对该问题理解的相似的解释，也算释怀了
20191127补充：
对于类似的where条件：parameter1 = @p_parameter1 OR @p_parameter1 IS NULL的OR后面一个分支：@p_parameter1 IS NULL
However, this time the Query Optimizer is not able to use the density information and instead it is using the standard guess of 30% selectivity for inequality comparisons. That means that the estimated number of rows is always 30% of the total number of records for an inequality operator and,
in this case, 30% of 100000 is 30000
这里的30%我没有找到相关的官方文档，但我在生产环境测试过都是取30%，如果你有找到相关支持文档，麻烦告知~
也就是说它直接使用统计信息的Rows*30%得到估计行数
因此在预估超过30%的情况下，只能走表扫描
如果准确地预估表变量
与with recompile相比，option(recompile)选项可以地预估表变量的行数，再次说明option(recompile)是基于参数置入（Parameter Embedding Optimization）编译的特性
直接看例子吧，应该是很清楚的
with recompile情况下对表变量的预估，
简单解释一下，就是在存储过程中，with recompile强制存储过程重编译的情况下，表变量参数join的时候，对表变量的预估情况如下
option(recompile)强制存储过程重编译的情况下，表变量参数join的时候，对表变量的预估情况如下
可见，option(recompile)强制重编译，不但可以获取与with recompile重编译不一样的执行计划，也可以非常准确地预估到表变量的行数
可以解决默认情况下，表变量总是预估为1行的情况（应该是sqlserver 2012之后有改善，sqlserver 2012之前默认预估为1行）
总结：本文通过一个简单的案例，解释了WITH RECOMPILE和OPTION(RECOMPILE)这种强制重编译方式的区别，以及引申出来的The Parameter Embedding Optimization（第一次听说）优化机制。
很多时候，自己对一些知识只是想当然地去理解和使用，比如随意使用WITH RECOMPILE和OPTION(RECOMPILE)，
粗暴地认为这两种强制重编译的方式区别仅仅在于一个是存储过程级的重编译，一个是SQL语句级的重编译。真正拿着case测试的时候，才发现，还真不一样。
下一篇写一个跟这个机制类似的同样有意思的文章。
http://sqlinthewild.co.za/index.php/2009/03/19/catch-all-queries/
http://www.sommarskog.se/dyn-search-2008.html
同时，再次感谢Uest同学提供的参考资料和指导建议。