T-SQL探针残留

我正在测试probe residual,将数据类型不匹配的表连接起来,以查看这对性能有何影响。在这个测试中,我连接了两个表,一个表的数据类型不匹配(nvarchar和varchar),另一个表的数据类型匹配。我使用的代码:

SELECT REPLACE (CONVERT(VARCHAR(255), NEWID()), '-', '') AS ColA,
      ROW_NUMBER() OVER (ORDER BY ( SELECT 1)) AS RowNumb
INTO #Varchar
FROM sys.all_objects AS S
CROSS JOIN sys.all_objects AS S2

SELECT CAST (ColA AS NVARCHAR (255)) AS ColA,
       RowNumb
INTO #Nvarchar
FROM #Varchar

SELECT TOP 1000000 *
INTO #Subset
FROM #Varchar

CREATE INDEX i_VarcharColA ON #Varchar (ColA)
CREATE INDEX i_NvarcharColA ON #Nvarchar (ColA)
CREATE INDEX i_SubsetColA ON #Subset (ColA)

SET STATISTICS IO, TIME ON
SELECT COUNT(*) AS CountStar
FROM #Varchar AS V
INNER JOIN #Subset AS S ON V.ColA = S.ColA -- matching datatypes

SELECT COUNT(*) AS CountStar
FROM #Nvarchar AS V
INNER JOIN #Subset AS S ON V.ColA = S.ColA -- non-matching datatypes

性能有很大的差别。不过,令我吃惊的是,使用不匹配的数据类型实际上会有更好的效果。

我一定忽略了一些事情,但任何见解都会受到极大的赞赏。

谢谢!