[SQL] 隱含轉換 - CASE WHEN

Turning 時遇見新的隱含轉換案例，該 case 是兩個 table 透過 left join 後，要在 select 內去進行統計轉換，但該欄位原本是字串欄位，卻轉成整數 (理論上是手誤)，導致隱含轉換發生

 

模擬案例

USE tempdb
GO

DROP TABLE IF EXISTS tblMaster
DROP TABLE IF EXISTS tblDetail

CREATE TABLE tblMaster (ColPK CHAR(11))
CREATE TABLE tblDetail (ID INT IDENTITY(1,1) , ColPK char(11))

SELECT
	CASE
		WHEN D.ColPK IS NOT NULL THEN 1 -- 字串欄位轉為整數
		ELSE D.ColPK
	END AS Test1 ,
	IIF(D.ColPK IS NOT NULL , 1 , D.ColPK) AS Test2
FROM tblMaster AS M
	LEFT JOIN tblDetail AS D ON M.ColPK = D.ColPK