什么是隱式數(shù)據(jù)類型轉(zhuǎn)換： 當我們在語句的where 條件等式的左右提供了不同數(shù)據(jù)類型的列或者變量，SQL Server在處理等式之前，將其中一端的數(shù)據(jù)轉(zhuǎn)換成跟另一端數(shù)值的數(shù)據(jù)類型一致，這個過程叫做隱式數(shù)據(jù)類型轉(zhuǎn)換。 比如 char(50)=varchar(50), char(50)=nchar
什么是隱式數(shù)據(jù)類型轉(zhuǎn)換：
當我們在語句的where 條件等式的左右提供了不同數(shù)據(jù)類型的列或者變量，SQL Server在處理等式之前，將其中一端的數(shù)據(jù)轉(zhuǎn)換成跟另一端數(shù)值的數(shù)據(jù)類型一致，這個過程叫做隱式數(shù)據(jù)類型轉(zhuǎn)換。
比如 char(50)=varchar(50), char(50)=nchar(50), int=float, int=char(20) 這些where 條件的等式都會觸發(fā)隱式數(shù)據(jù)類型轉(zhuǎn)換。
但是，對于某些數(shù)據(jù)類型轉(zhuǎn)換過程中，可以轉(zhuǎn)換的方向只是單向的。例如：
如果你試圖比較INT和FLOAT的列，INT數(shù)據(jù)類型必須被轉(zhuǎn)換成FLOAT型”CONVERT(FLOAT,C_INT) = C_FLOAT”.
如果你試圖比較char和nchar的列，char數(shù)據(jù)類型必須被轉(zhuǎn)換成unicode型”CONVERT(nchar,C_char) = C_nchar”
因此，我們在.net 或者java的程序中，經(jīng)常容易遇到以下類型的性能問題：
CREATE TABLE [TEST_TABLE] (
[TAB_KEY] [varchar] (5)?NOT NULL ,
[Data] [varchar] (10)?NOT NULL ,
CONSTRAINT [TEST_TABLE_PK] PRIMARY KEY?CLUSTERED
(
[TAB_KEY]
)?ON [PRIMARY]
) ON [PRIMARY]
GO
declare @p1 int
set @p1=0
exec sp_prepexec @p1 output,N’@P0 nvarchar(4000)’,N’select TAB_KEY,Data from TEST_TABLE?where TAB_KEY = @P0′,N’0′
select @p1
在這個例子中，表的定義TAB_KEY是varchar型的字段，而程序傳遞的參數(shù)類型為Unicode類型的nvarchar(4000). 當語句執(zhí)行到where TAB_KEY = @P0，SQL Server會按照如下方式執(zhí)行：
select TAB_KEY,Data from TEST_TABLE?where convert(nvarchar(5),TAB_KEY)=@p0
語句一旦變成這樣，TAB_KEY上創(chuàng)建的clustered index就無法快速的查找索引并返回對應的行了。
在這里順便提一下為什么java程序默認傳遞unicode類型的參數(shù). 如果使用的是Microsoft JDBC provider，那么這里有個參數(shù)sendStringParametersAsUnicode，默認是true. 這個參數(shù)是用來控制傳遞的參數(shù)是不是unicode類型的。如果我們的SQL Server表的數(shù)據(jù)類型都不是unicode，需要在connection string中聲明這個參數(shù)是false。http://msdn.microsoft.com/en-us/library/ms378988.aspx
我們使用以下腳本往測試表中插入幾千行數(shù)據(jù)，然???檢查執(zhí)行計劃：
declare @i int
set @i=0
while(1=1)
begin
insert into TEST_TABLE values(@i,’a’)
set @i=@i+1
end
SQl2008、2005、2000 execution plan
Rows???????? Executes??? StmtText???????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????
———— ———– —————————————————————————————————————————————————————
1??????????? 1?????????? select TAB_KEY,Data from TEST_TABLE?where TAB_KEY = @P0???????????????????????????????????????????????????????????????????????????????????????????????????????
1??????????? 1???????????? |–Clustered Index Scan(OBJECT:([aaa].[dbo].[TEST_TABLE].[TEST_TABLE_PK]), WHERE:(CONVERT_IMPLICIT(nvarchar(5),[aaa].[dbo].[TEST_TABLE].[TAB_KEY],0)=[@P0]))?
在SQL 2000和2005，該語句得到的執(zhí)行計劃是同樣的， SQL Server對測試表TEST_TABLE做了clustered index 掃描，即全表掃描，然后返回一行數(shù)據(jù)。在這里我們很清楚的看到有個CONVERT_IMPLICIT發(fā)生，并且將TAB_KEY轉(zhuǎn)換成了nvarchar(5),由于索引上的列發(fā)生了數(shù)據(jù)類型轉(zhuǎn)換，導致索引保存的數(shù)據(jù)無法直接用來做比較，因此SQL Server需要將所有行的TAB_KEY掃描轉(zhuǎn)換后跟@p0做比較得到需要返回的數(shù)據(jù)。
當我們在SQL 2008 中做同樣的測試是，我們發(fā)現(xiàn)執(zhí)行計劃變了！
Rows??????? Executes???? StmtText??????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????? ????????????????????
———– ———— ——————————————————————————————————————————————————————————————————————————————————————————————–
1?????????? 1??????????? select TAB_KEY,Data from TEST_TABLE?where TAB_KEY = @P0??????????????????????????????????????????????????????????????????????????????????????????????????? ?????????????????????????????????????????????????????????????????????????????????????????????????????????????????1?????????? 0
1?????????? 1????????????? |–Nested Loops(Inner Join, OUTER REFERENCES:([Expr1005], [Expr1006], [Expr1004]))??????????????????? ????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????
1?????????? 1?????????????????? |–Compute Scalar(DEFINE:(([Expr1005],[Expr1006],[Expr1004])=GetRangeThroughConvert([@P0],[@P0],(62))))?????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????
1?????????? 1??????? ???????????|??? |–Constant Scan??????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????? ????????????????????????????????
1?????????? 1?????????????????? |–Clustered Index Seek(OBJECT:([aa].[dbo].[TEST_TABLE].[TEST_TABLE_PK]), SEEK:([aa].[dbo].[TEST_TABLE].[TAB_KEY] > [Expr1005] AND [aa].[dbo].[TEST_TABLE].[TAB_KEY]
這個執(zhí)行計劃看起來變得復雜了很多，我們注意到，這里出現(xiàn)了一個操作叫做GetRangeThroughConvert(),在這里，SQL Server由于不能直接對varchar(5)的列用nvarchar(4000)的值進行seek，因此，SQL Server必須將nvarchar轉(zhuǎn)換成varchar。但是由于這個轉(zhuǎn)換可能導致數(shù)據(jù)丟失，SQL Server采用了另一種做法，首先擴展了一個varchar類型的范圍，確保可以轉(zhuǎn)換成我們目標的nvarchar值的varchar數(shù)據(jù)落在這個范圍之內(nèi)，然后使用這個范圍去對index直接做seek。得到了返回的滿足范圍的少量數(shù)據(jù)以后，對這個范圍內(nèi)的少量數(shù)據(jù)進行數(shù)據(jù)類型轉(zhuǎn)換，然后用來和nvarchar的值比較，最終準確的返回結(jié)果集。在這樣一個過程中，SQL Server采用了一種迂回的方式使用了index seek而避免了表掃描。
Compute Scalar(DEFINE:(([Expr1005],[Expr1006],[Expr1004])=GetRangeThroughConvert([@P0],[@P0],(62))))?–在這里計算出來范圍的兩個邊界值，然后將語句重寫為以下模式：
select TAB_KEY, Data from TEST_TABLE?where TAB_KEY>[Expr1005]?and?TAB_KEY
其中“TAB_KEY>[Expr1005]?and?TAB_KEY
因此，在SQL 2008中我們就不會再次面對由于客戶程序定于參數(shù)的類型和數(shù)據(jù)表的數(shù)據(jù)類型不一致而帶來的無法使用index的問題。
