~楓花雪岳~: [SQL] 避免隱含式轉換

星期五, 10月 04, 2013

[SQL] 避免隱含式轉換

在論壇上看見這樣的 WHERE 篩選條件

AND CAST(dbo.SalaryMonth.SalaryYear - 1911 AS nvarchar(3)) + RIGHT(CAST(dbo.SalaryMonth.Month + 100 AS nvarchar(3)), 2) = 10209

其問題在於這篇 [SQL] WHERE 中的資料篩選所說不要對日期欄位進行轉換，還有另一個問題是隱含式轉換，所謂隱含式轉換是指兩個不同資料形態進行比較，資料型態彼此相容，因此 Query Optimizer 會自動對資料型態進行轉換，轉換成相同資料型態後才會進行比較，這也會產生效能問題，要盡量避免。

資料型態優先順序（MSDN 內容）

當一個運算子結合兩個不同資料類型的運算式時，資料類型優先順序的規則，會指定將低優先順序的資料類型，轉換為高優先順序的資料類型。如果轉換不是支援的隱含轉換，就會傳回錯誤。如果這兩個運算元運算式的資料類型相同，則作業結果就含有該資料類型。

SQL Server 會使用下列優先順序：

使用者自訂資料類型 (最高)

sql_varian t

xml

datetimeoffset

datetime2

datetime

smalldatetime

date

time

float

real

decimal

money

smallmoney

bigint

int

smallint

tinyint

bit

ntext

text

image

timestamp

uniqueidentifier

nvarchar (包括 nvarchar(max) )

nchar

varchar (包括 varchar(max) )

char

varbinary (包括 varbinary(max) )

binary (最低)

Sample Data

根據看見的 WHERE 條件建立 Data 測試

USE TempDB
GO

IF OBJECT_ID('MonthTable') IS NOT NULL
    DROP TABLE MonthTable

CREATE TABLE MonthTable (twMonth char(5))
CREATE INDEX IX_MonthTable_twMonth ON dbo.MonthTable (twMonth)

;
WITH CTE AS
(
    SELECT CAST('20000101' AS datetime) AS dateList
    UNION ALL
    SELECT DATEADD(MM,1,dateList)
    FROM CTE
    WHERE dateList < CAST('20130901' AS datetime)
)
INSERT INTO MonthTable (twMonth)
SELECT
    RIGHT(REPLICATE('0',3) + CAST(YEAR(dateList) - 1911 AS varchar(3)),3) +
    RIGHT(REPLICATE('0',2) + CAST(MONTH(dateList) AS varchar(2)),2)
FROM CTE
OPTION (MAXRECURSION 0)

測試觀察