在今天的文章里，我想談下對(duì)于即席SQL語(yǔ)句（ad-hoc SQL statements），SQL Server使用的簡(jiǎn)單參數(shù)化（Simple Parameterization）的一些特性和副作用。首先，如果你的SQL語(yǔ)句包含這些，簡(jiǎn)單參數(shù)化不會(huì)發(fā)生：

• JOIN
• IN
• BULK INSERT
• UNION
• INTO
• DISTINCT
• TOP
• GROUP BY
• HAVING
• COMPUTE
• Sub Queries

一般來(lái)說(shuō)，如果你處理所謂的安全執(zhí)行計(jì)劃（Safe Execution Plan），SQL Server自動(dòng)參數(shù)化你的SQL語(yǔ)句：不管提供的參數(shù)值，查詢總必須通向一樣的執(zhí)行計(jì)劃。如果你的執(zhí)行計(jì)劃里有書(shū)簽查找，這就是不可能的例子。因?yàn)?a target="_blank">臨界點(diǎn)定義了是否進(jìn)行書(shū)簽查找還是全表/聚集索引掃描。

自動(dòng)參數(shù)化并不那么酷！

如果SQL Server能自動(dòng)參數(shù)化你的SQL語(yǔ)句，你還是要考慮下SQL Server引入的自動(dòng)參數(shù)化SQL語(yǔ)句的一些副作用。我們來(lái)看一個(gè)具體的例子。下列查詢創(chuàng)建一個(gè)表，執(zhí)行一個(gè)會(huì)被SQL Server自動(dòng)參數(shù)化的簡(jiǎn)單SQL語(yǔ)句。

-- Create a simple table 
CREATE TABLE Orders 
( 
    Col1 INT IDENTITY(1, 1) PRIMARY KEY NOT NULL, 
    Price DECIMAL(18, 2) 
) 
GO 
 
-- This query gets auto parametrized, because it is a simple query with a safe (consistent) plan 
SELECT * FROM Orders 
WHERE Price = 5.70 
GO 
 
-- Analyze the Plan Cache 
SELECT 
    st.text,  
    qs.execution_count,  
    cp.cacheobjtype, 
    cp.objtype, 
    cp.*, 
    qs.*,  
    p.*  
FROM sys.dm_exec_cached_plans cp 
CROSS APPLY sys.dm_exec_query_plan(cp.plan_handle) p 
CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) st 
LEFT JOIN sys.dm_exec_query_stats qs ON qs.plan_handle = cp.plan_handle 
WHERE st.text LIKE '%Orders%' 
GO 

然后當(dāng)你查看計(jì)劃緩存時(shí)，你會(huì)看到SQL Server能為你自動(dòng)參數(shù)化SQL語(yǔ)句：

(@1 numeric(3,2))SELECT * FROM [Orders] WHERE [Price]=@1 

但什么是選擇的作為參數(shù)的數(shù)據(jù)類型？最小可能的那個(gè)！在這里是NUMERIC(3,2)！如果現(xiàn)在你執(zhí)行下列2個(gè)查詢：

-- Execute a slightly different query 
SELECT * FROM Orders 
WHERE Price = 8.70 
GO 
 
-- Execute a slightly different query 
SELECT * FROM Orders 
WHERE Price = 124.50 
GO 

SQL Server能重用為第1個(gè)使用8.7值SQL語(yǔ)句的參數(shù)化SQL語(yǔ)句的執(zhí)行計(jì)劃。但用124.50值的第2個(gè)SQL語(yǔ)句呢？對(duì)于這個(gè)SQL語(yǔ)句緩存的計(jì)劃不能被重用，因?yàn)?24.50值不符合NUMERIC(3,2)。在這個(gè)情況下，SQL Server用NUMERIC(5,2)數(shù)據(jù)類型生成你SQL語(yǔ)句的新參數(shù)化版本。你剛用你的SQL語(yǔ)句的額外的參數(shù)化版本污染了你的計(jì)劃緩存！當(dāng)你執(zhí)行下列語(yǔ)句會(huì)變得更糟：

-- Execute a slightly different query 
SELECT * FROM Orders 
WHERE Price = 1204.50 
GO 

這個(gè)會(huì)再次給你新的用NUMERIC(6,2)數(shù)據(jù)類型的新參數(shù)化版本——計(jì)劃緩存里另一個(gè)版本！當(dāng)我展示這個(gè)行為的時(shí)候，很多人都建議我應(yīng)該用逆序來(lái)執(zhí)行剛才的SQL語(yǔ)句。我們通過(guò)首先清空計(jì)劃緩存來(lái)試下。

-- Clear the Plan Cache 
DBCC FREEPROCCACHE 
GO 
 
-- Execute a slightly different query 
SELECT * FROM Orders 
WHERE Price = 1204.50 
GO 
 
-- Execute a slightly different query 
SELECT * FROM Orders 
WHERE Price = 124.50 
GO 
 
-- Execute a slightly different query 
SELECT * FROM Orders 
WHERE Price = 8.70 
GO 

然后當(dāng)你看計(jì)劃緩存時(shí)，沒(méi)有任何改變：SQL Server還生成了3個(gè)不同的參數(shù)化SQL語(yǔ)句——每次都用最小可能的數(shù)據(jù)類型。你怎么做沒(méi)有一點(diǎn)關(guān)系，即你執(zhí)行你SQL語(yǔ)句的順序：在自動(dòng)參數(shù)化期間，SQL Server總會(huì)選擇最小可能的數(shù)據(jù)類型。當(dāng)你依賴SQL Server這個(gè)特性時(shí)，好好考慮下。

VARCHAR如何呢？SQL Server自動(dòng)參數(shù)化包含字符值（例如VARCHAR）的SQL語(yǔ)句時(shí)，事情會(huì)好點(diǎn)。假設(shè)有下列表定義和下列2個(gè)查詢：

-- Create another table to demonstrate this problem 
CREATE TABLE Orders3 
( 
    Col1 INT IDENTITY(1, 1) PRIMARY KEY NOT NULL, 
    Col2 VARCHAR(100) 
) 
GO 
 
-- Clears the Plan Cache 
DBCC FREEPROCCACHE 
GO 
 
-- A VARCHAR/CHAR column is always auto parametrized to a VARCHAR(8000) 
SELECT * FROM Orders3 
WHERE Col2 = 'Woody' 
GO 
 
-- A VARCHAR column is always auto parametrized to a VARCHAR(8000) 
SELECT * FROM Orders3 
WHERE Col2 = 'Tu' 
GO