Oh, neat. I like that.

What do you think about this? Borrowing a little Itzik Magik.

WITH dupe1

AS ( SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS RN ,

a.BusinessName, a.[Address], a.Phone

FROM TestBusiness a

),

dupe2

AS ( SELECT d.BusinessName, d.[Address], d.Phone

FROM dupe1 d

GROUP BY d.BusinessName, d.[Address], d.Phone

HAVING COUNT(*) > 1

)

SELECT *

FROM dupe1 d1

INNER JOIN dupe2 d2

ON d1.BusinessName = d2.BusinessName

AND d1.[Address] = d2.[Address]

AND d1.Phone = d2.Phone

WHERE d1.RN > 1;

The QE hates forcing a seek here, and forcing a hash join (was thinking if the actual tables are much larger, though it may use one anyway if they are) seems to result in 1 more ms CPU time, and one extra logical read.

Compared with your examples, the IO and time output seem roughly the same, though I'd be interested to hear what you think about the far different execution plan.

My tests include your suggested index.

Anyway, looking forward to your response.

Thanks