Experts,
I am trying to learn more about the CONVERT_IMPLCIT and Probe Residual. Can someone explain to me why the following test script cause Probe Residual?
My understanding is if both join predicate column are of the same datatype and is defined as "NOT NULL" then it should not cause any Residual
Reference and Test script
Join Performance, Implicit Conversions, and Residuals
CREATE TABLE #BuildBigInt
(
col1 BIGINT NOT NULL
);
CREATE TABLE #ProbeBigInt
(
col1 BIGINT NOT NULL
);
GO
DECLARE @I INTEGER = 1;
INSERT #BuildBigInt
(col1)
VALUES
(CONVERT(BIGINT, RAND(1) * 9999999));
WHILE @I < 5000
BEGIN
INSERT #BuildBigInt
(col1)
VALUES
(CONVERT(BIGINT, RAND() * 9999999));
SET @I += 1;
END;
INSERT #ProbeBigInt
(col1)
VALUES
(CONVERT(BIGINT, RAND(2) * 9999999));
WHILE @I < 5000000
BEGIN
INSERT #ProbeBigInt
(col1)
VALUES
(CONVERT(BIGINT, RAND() * 9999999));
SET @I += 1;
END;
--test query
SELECT
COUNT_BIG(*)
FROM #BuildBigInt AS bbi
JOIN #ProbeBigInt AS pbi ON
pbi.col1 = bbi.col1
OPTION (MAXDOP 1);
