It gets easier with the title ...

The SENIOR DB developer removing duplicates swore that this was going to be much faster:

DELETE #someLocalTempTable
WHERE     CONVERT(VARCHAR(20),SomePKIDEN_Id)
    + NamesStringColumn
    + CONVERT(VARCHAR(20),SomeFKIDEN_Id)
    + CONVERT(VARCHAR(20),SomeOtherFKIDEN_ID)
   IN (
    SELECT CONVERT(VARCHAR(20),SomePKIDEN_Id)
    + NamesStringColumn
    + CONVERT(VARCHAR(20),SomeFKIDEN_Id)
    + CONVERT(VARCHAR(20),SomeOtherFKIDEN_ID)
    FROM ##AGlobalTempTable
    )

AS OPPOSED TO THIS:

DELETE t
FROM #someLocalTempTable     t (NOLOCK)
JOIN ##AGlobalTempTable     s (NOLOCK)
ON t.SomePKIDEN_Id         = s.SomePKIDEN_Id
    AND t.NamedStringColumn = s.NamedStringColumn
    AND t.SomeFKIDEN_Id     = s.SomeFKIDEN_Id
    AND t.SomeOtherFKIDEN_ID= s.SomeOtherFKIDEN_ID

Fast correlated sub-queries. Nice concept.