[SQL][問題處理]資料類型優先順序 ( Data Type Precedence )
前一陣子在社群內介紹了 SARG 之後，也陸陸續續的收到一些朋友的問題，剛好自己在工作上也遇到一些實際的案例，就先把這個部份給整理一下做成一個小的 Lab，希望對大家有幫助。
首先先做個簡單的資料庫，裡面放了一些測試資料，這個範例會產生 10,000,000 ( 一千萬筆 ) 紀錄，因此如果您的測試環境硬碟速度不夠快的話，那麼大約會需要將近 20~30 分鐘。
-- 刪除範例資料庫
IF DB_ID('SARGDEMO') IS NOT NULL DROP DATABASE [SARGDEMO];
GO
-- 建立資料庫
CREATE DATABASE [SARGDEMO]
GO
-- 變更復原模式為簡單
ALTER DATABASE [SARGDEMO] SET RECOVERY SIMPLE;
GO
USE [SARGDEMO]
GO
-- 建立資料表
CREATE TABLE [BigTable]
(
A1 INT,
A2 NVARCHAR(10),
A3 VARCHAR(10),
A4 NCHAR(200),
)
GO
DECLARE @I INT;
DECLARE @J INT;
DECLARE @K INT;
SET @J = 0;
-- 產生一千萬筆的資料，為了避免交易記錄檔過大，每一萬筆資料放在一個 Transaction 內
SET NOCOUNT ON;
WHILE @J < 1000
BEGIN
SET @I = 0;
BEGIN TRAN
WHILE @I < 10000
BEGIN
SET @K = @J*10000+@I ;
INSERT INTO [BigTable] ( A1,A2,A3,A4 ) VALUES ( @K, RIGHT('0000000000'+LTRIM(STR(@K)),10), RIGHT('0000000000'+LTRIM(STR(@K)),10), NEWID())
SET @I += 1;
END
COMMIT
SET @J += 1;
END
GO
接著我分別針對 A1 , A2 , A3 這三個欄位，都各建立一個索引
USE [SARGDEMO]
GO
-- 針對欄位 A1 建立一組 INDEX
CREATE NONCLUSTERED INDEX IX_BigTable_A1 ON [BigTable](A1);
GO
-- 針對欄位 A2 建立一組 INDEX
CREATE NONCLUSTERED INDEX IX_BigTable_A2 ON [BigTable](A2);
GO
-- 針對欄位 A3 建立一組 INDEX
CREATE NONCLUSTERED INDEX IX_BigTable_A3 ON [BigTable](A3);
GO
-- 檢查索引的統計資訊
DBCC SHOW_STATISTICS(BigTable,IX_BigTable_A1)
GO
DBCC SHOW_STATISTICS(BigTable,IX_BigTable_A2)
GO
DBCC SHOW_STATISTICS(BigTable,IX_BigTable_A3)
GO
建立完測試資料，可以開始我們的測試。正常來說，如果我們要找資料，如果要找的資料所在的欄位剛好有建立索引，那麼 SQL Server 就會使用索引去查資料了。但這裡有個很微妙的狀況，當你要找的欄位其資料型態和查詢值的資料型態是不一致的狀況下，則 SQL Server 會預先做型態的轉換，以便資料比對。這樣說可能有點模糊，下面我們用個實例來表示
假設我們的查詢條件如下，在這個例子中欄位 A1 的型態是 INT，比對的資料 1234321 也是 INT，因此這個時候 SQL Server 不需要做資料型態轉換，就可以直接來查詢了
SELECT * FROM BigTable WHERE A1 = 1234321
但假設我們沒有注意到，寫 SQL 的時候寫成如下的方式，那麼這樣也是可以正常執行的。
SELECT * FROM BigTable WHERE A1 = '1234321'
這個時候要比對的資料雖然是字串型態，此時 SQL Server 會做資料類型轉換，我們可以從查詢計畫中看到，SQL Server 會先把字串 '1234321' 轉換為整數的 1234321 ，接著再透過索引去查詢資料。因此在這樣的狀況下，要比對的資料類型雖然沒有匹配，但 SQL Server 會自動幫我們做資料類型轉換處理，似乎沒有甚麼問題。
但如果今天我們要查詢的欄位是 Nchar 型態的狀況下，那狀況會怎麼樣呢 ? 我們先用一個正常的方式
SELECT * FROM BigTable WHERE A2 = N'0001234321'
此時如果我們把上述的 SQL 指令給改一下，寫成如下的方式
SELECT * FROM BigTable WHERE A2 = 1234321
則總執行成本則由之前不到 0.01 提升到 43
之所以會有這麼大的變化，主要是執行計畫中由原本的「索引搜尋」變成了「索引掃描」。而會變成這樣的原因，我們可以從下圖中看出，因為當類型不匹配的時候，SQL Server 幫我們將欄位的值做一個隱性的轉換成為 INT 類型，轉換好之後才去跟我們的檢索條件去比對，而這樣的處理需要針對這個 A2 的欄位一筆資料一筆資料的處理，使得 SQL Server 就無法從直接透過索引檢索，變成去使用這個索引去做每一筆資料的轉換，因此相對地就花上較多的時間。
那為什麼 SQL Server 不是將我們檢索條件由數值先轉換成為字串呢 ? 這個主要是因為 SQL Server 的「資料類型優先順序」 ( Data Type Precedence )，在 SQL Server 內當不同型別在比對的時候，SQL Server 會根據這個優先順序，以順序高的為主，將順序低的型別轉換成為順序高的，再進行資料比對。因此我們上面的例子中，因為 INT 型態比字串類型的優先順序來的高，因此會先把字串轉換成為數值，再進行比較，才會發生第一個案例是沒有影響的，但第二個案例中效能卻有明顯的差異。
上述的案例是我們比較常見的也容易發現的問題，大家也都比較容易發現到資料類型轉換的問題，但最近我們還遇到另外一種是我們比較容易忽略的。在這個案例中，主要是因為資料表是舊系統所建立的，而有部分程式利用 .Net 來做開發，因此就產生了 char 與 nchar 之間隱性轉換所引發的問題。為了測試這個問題，以下我們將要針對 A3 這個欄位來做查詢。
-- A3 的欄位型態是 varchar
SELECT * FROM BigTable WHERE A3 = '0000013579'
SELECT * FROM BigTable WHERE A3 = N'0000013579'
從執行計畫中看起來，雖然 nchar & nvarchar 型態的優先順序比 char & varchar 來的高，但似乎就算沒有注意沒有甚麼差別，SQL Server 都可以處理也沒有甚麼效能上的差異。
但實際上是我們被這樣的結果給矇蔽了，誤以為只要是字串型態就都沒有問題了。因此如果我們調整一下語法，來看看這樣的狀況是否會改變呢 ? 在下面的指令中，我們調整一下原本的寫法，將原本的 = 改成為 between 的運算
SELECT * FROM BigTable WHERE A3 Between '0000013579' AND '0000014680'
SELECT * FROM BigTable WHERE A3 Between N'0000013579' AND N'0000014680'
從下圖的執行計畫中就可以明顯看出，如果欄位是 char 或者是 varchar 的狀況下，當我們又畫蛇添足的去加上 N 把資料設定為 Unicode 的話，那麼對效能也是會有很大的差異。從這個結果中我們可以看到 SQL Server 不會去使用我們既有的索引，還要我們去多建立一個新的索引，且因為我們是使用 select * 的語法，因此建議的索引還要我們去 INCLUDE 其他幾個欄位。
而如果我們就算在這個時候真的根據 SQL Server 所建議的，去新增加這一組新的 INDEX，雖然看起來執行成本從 500 降到 40，也有使用索引搜尋。
但這樣的結果雖然看起來不錯，但還是遠不如直接用正確的型態，而且這樣還要多花上許多的空間和時間來維護和存放索引。因此，如果在下 SQL 指令前多注意一下欄位類型和資料類型，那麼會遠比建立多餘的索引來的有效多了。