關於T-SQL重編譯那點事，內聯函數和表值函數在編譯生成執行計劃的區別

本文出處：http://www.cnblogs.com/wy123/p/6266724.html 

最近在學習 WITH RECOMPILE和OPTION(RECOMPILE)在重編譯上的區別的時候，無意中發現表值函數和內聯表值函數編譯生成執行計劃的區別
下文中將會對此問題展開討論。
簡單地說就是：同樣一句SQL，分別寫成內聯函數和表值函數，然後執行對Function的查詢，發現其執行計劃和執行計劃緩存是不一樣的，
根據某些測試的一些共同規律發現，內聯函數的編譯很有可能與Parameter Embedding Optimization 有關
關於Parameter Embedding Optimization，我在http://www.cnblogs.com/wy123/p/6262800.html寫了一個案例
在發生Parameter Embedding Optimization做編譯優化的時候，跟普通的編譯優化機制還是有很大差異的。

概念解釋:內聯用戶定義函數和表值用戶定義函數

　　SQL Server中的表值函數分為“內聯用戶定義函數”和“表值用戶定義函數”。

內聯用戶定義函數(Inline User-Defined Functions)：
　　不上MDSN上搬概念了，簡單地說，內聯函數的特點就是就是返回類型為table，返回的結果是一個查詢語句
　　如下，dbo.fn_InlineFunction即為內聯用戶定義函數，當然後面要與表值用戶定義函數作比較，就能看出來區別了

create function dbo.fn_InlineFunction
(
    @p_parameter     varchar(500)
)
returns table
as
return
(
    SELECT id,col2
    FROM [dbo].[TestTableValueFunction] 
    where ( col2  = @p_id or @p_id is null)      
)
GO

表值用戶定義函數(Table-Valued User-Defined Functions),
　　與內聯函數區別在於，表值用戶定義函數返回的是一個表變數，在函數體中，通過賦值給這個表變數，然後返回表變數
　　如下dbo.fn_TableValuedFunction即為內聯用戶定義函數，

create function fn_TableValuedFunction
(
    @p_paramter      varchar(500)
)
RETURNS @Result TABLE
(
    id int ,
    value char(5000)
)
as
begin
    
    insert into @Result
    select id,col2
    from [dbo].[TestTableValueFunction] 
    where ( col2  = @p_id or @p_id is null)   
      
    return
end

　　熟悉sqlserver的同學可能已經知道這兩者的區別了，關於內聯用戶定義函數和表值用戶定義函數就先這麼簡單說一下區別
　　雖然內聯函數和表值函數在功能上和使用上是有一些差異的，但是有一部分查詢，用兩種方式都可以實現，也就說兩者在功能上有差異也有交集。

開始本文主題

同樣的SQL語句，使用內聯函數和使用表值函數查詢生成執行計劃的區別

　　按照慣例，先造一個測試表,char(500)的欄位可以是的表以及索引占用空間變大，後面對比測試的效果變得更加明顯。

create table TestTableValueFunction
(
    id int IDENTITY(1,1),
    col2 char(500)
)
GO

INSERT INTO TestTableValueFunction VALUES (NEWID())
GO 1000000

CREATE INDEX idx_col2 on TestTableValueFunction(col2)
GO

　　同樣的查詢條件下，分別用內聯函數和表值函數查詢，查看其性能

　　

　　首先使用內聯函數的方式查詢，用插入數據中的一條值做查詢，最直觀的方式去看SSMS的執行時間，顯示為0秒，本機測試幾乎是瞬間就出來結果了
　　可以看到執行計划走的是原始表TestTableValueFunction上idx_col2索引查找Index Seek

觀察IO，發現發生了8次IO

　　使用表值函數的方式查詢，使用上面同樣的條件做查詢，SSMS顯式耗時4秒（本機測試的，可以忽略測試環境的外界影響因素）
　　但是使用表值函數無法直接觀察查詢的執行計劃和IO信息，這兩個信息後面從計劃緩存中查詢

　　其顯示的IO信息應該也不是原始的SQL的IO，應該是表變數的IO，原始SQL語句的IO和執行計劃信息暫時看不到，後面再說

為什麼同樣的查詢，使用表值函數，性能差異居然有這麼大？

　　對於表值函數，由於無法直接觀察到其實際執行計劃和IO信息，那麼我們去查詢其緩存的執行計劃和IO信息
　　如下，sys.dm_exec_query_stats系統表中查詢到其最近一次執行的IO信息，76997，遠遠大於上面的8次IO
　　查看緩存的執行計劃

　　從緩存中的執行計劃可以看到，其執行計劃為全表掃描

　　到這裡就有意思了，既然是一樣的SQL，寫成內聯函數和表值函數，兩者的執行計劃不一樣，
　　那麼就可以推斷出，SQL Server對內聯函數和表值函數的編譯處理方式是不一樣的。
　　同時，上面的內聯函數是可以知道看到實際執行計劃的，顯示為Index Seek，
　　但是在觀察緩存計劃的時候，是沒有查到的，如下截圖，也就是說內聯函數dbo.fn_InlineFunction對應的SQL的執行計劃是沒有被緩存起來的
　　種種跡象不由的使我想到上一篇關於T-SQL重編譯那點事中，OPTION(RECOMPILE)的The Parameter Embedding Optimization編譯優化機制
　　從內聯函數的SQL的執行計劃發現，編譯過程中是對SQL語句做植入參數優化+簡化，又因為沒有緩存執行計劃，那麼很有可能是發生了重編譯
　　從個這兩點來看，跟OPTION(RECOMPILE)強制重編譯中的The Parameter Embedding Optimization編譯優化機制基本上是吻合的

　　回頭再說表值函數為什麼是全表掃描？參考下圖，正常情況下這種查詢邏輯就是走的全表掃描
　　只不過是內聯函數裡面，編譯優化機制對這種寫法做了專門的優化，才能走一個索引查找的方式。
　　這也正是內聯函數和表值函數在編譯上最大的區別之一。
　　對於為什麼表值函數裡面這種邏輯會在造成全表掃描在上一篇也解釋了，這裡就不啰嗦了。

如上，同樣的T-SQL查詢，在末尾加上OPTION(RECOMPILE)，執行計劃也變成了Index Seek，跟內聯函數的執行計劃一致（都是index Seek），當然內聯函數中是沒有加OPTION(RECOMPILE)的

因此這裡有理由懷疑，內聯函數的編譯，是類似等價於加了OPTION(RECOMPILE)的

　　之前只是瞭解過內聯函數和表值函數在預估方面的區別（不過記得好像是SQL Server2012之後對錶值函數的預估計算方式也做了更新），
　　 除此之外，從來沒有註意到也沒有考慮過兩者在編譯以及計劃緩存方面的區別
　　 工作中見到過有人使用內聯函數做複雜的查詢，並且是查詢條件是(col2 =@p_parameter or @p_parameter is null)這種方式
　　 如果是在存儲過程中，這種方式是會抑制到索引的使用的，之前“理所當然地”認為，寫成內聯函數，肯定也會抑制索引的使用
　　不過從這個測試case來看，內聯函數這種寫法，確實可以正常使用索引

總結
　　本文通過一個簡單的case，來演示了內聯函數和表值函數在編譯上的一些差別，優化器對內聯函數進行專門的優化處理，而不會去對錶值函數做特別的優化。
　　在對內聯函數做特殊優化的時候，雖然沒有明確執行強制重編譯，但等效於存在類似於option(recompile)的基於sql語句級的強制重編譯優化機制。