為什麼 SQL Server 說它不能將 varchar 轉換為數字？

我在 MSSQL Server 2019 ( SQLFiddle )中有以下架構/數據：

CREATE TABLE products(
   idn NUMERIC(9) PRIMARY KEY
);

CREATE TABLE sales(
   idn NUMERIC(9) PRIMARY KEY,
   pid VARCHAR(50) NOT NULL,
   type VARCHAR(10) NOT NULL
);

INSERT INTO products (idn) VALUES (1);
INSERT INTO sales (idn, pid, type) VALUES (1, 1, 'number');
INSERT INTO sales (idn, pid, type) VALUES (2, 'Colgate', 'word');

sales具有混合數據，即VARCHAR和NUMERIC。事務過濾器負責JOIN正確處理。

為什麼以下SELECT失敗（由於某種我無法控制的原因，生成的查詢包含N字元串的文字。）？

SELECT 
   * 
FROM 
   products 
   INNER JOIN sales ON products.idn = sales.pid 
   AND sales.type = N'number' 
WHERE 
   products.idn in (1);

我不明白為什麼投射NVARCHAR到NUMERIC是一個問題：

SELECT CAST (N'1' as NUMERIC);

如果我稍微修改查詢，它可以工作：

SELECT 
   * 
FROM 
   products 
   INNER JOIN sales ON products.idn = sales.pid 
   AND sales.type = N 'number' 
WHERE
   -- Selecting the same data from `sales`.
   sales.pid in (1);

SELECT 
   * 
FROM 
   products 
   INNER JOIN sales ON products.idn = sales.pid
   -- Dropping the `N` prefix.
   AND sales.type = 'number' 
WHERE 
   products.idn in (1);

當嘗試將sales.pid值 ‘Colgate’ 值轉換為 numeric(9) 以評估連接條件時，將在執行時發生轉換錯誤。至於這是否真的發生取決於執行計劃中的評估順序。

下面是來自Unicode 文字執行計劃的銷售表聚集索引掃描謂詞，顯示了在評估“數字”條件之前發生的轉換：

CONVERT_IMPLICIT(numeric(9,0),[tempdb].[dbo].[sales].[pid],0)=(1.) AND CONVERT_IMPLICIT(nvarchar(10),[tempdb].[dbo].[sales].[type],0)=N'number'  

具有非 Unicode 文字的計劃具有相同的形狀，只是掃描運算符中的謂詞順序顛倒了：

[tempdb].[dbo].[sales].[type]='number' AND CONVERT_IMPLICIT(numeric(9,0),[tempdb].[dbo].[sales].[pid],0)=(1.)

儘管非 Unicode 文字（或參數）可以解決問題，但查詢仍然容易受到執行時錯誤的影響。這可以用TRY_CAST, TRY_CONVERT,CASE表達式等來解決，但最好修復數據模型，例如確保只比較相似或兼容的數據類型。

引用自：https://dba.stackexchange.com/questions/267843