2

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);

enter image description here

MDCCL
  • 8,530
  • 3
  • 32
  • 63
user1716729
  • 683
  • 7
  • 16

0 Answers0