Do indexes work if there is formatting/casting in the joins/where conditions?-CodePudding

I have a query performing poorly that joins two unindexed tables. Because I'm joining on telephone numbers coming from different sources, I'm using RIGHT() on the join conditions and doing some string manipulation through subqueries on each one of the tables. Would indexes help performance in this case? Example below

WITH ds_One AS (
  SELECT REPLACE(PhoneNumber,' 44', '0') AS PhoneNumber
  FROM PhoneNumbers
)

,ds_Two AS (
    SELECT REPLACE(PhoneNumber,' 44', '0') AS PhoneNumber
    ,CustomerName
    FROM PhoneNumbers2
)

SELECT two.CustomerName
FROM ds_Two two
INNER JOIN ds_One one
ON RIGHT(one.PhoneNumber,10) = RIGHT(two.PhoneNumber,10)

CodePudding user response：

No. Indexes on PhoneNumber won't help you here.

It is unsargable.

If it was using LEFT instead of RIGHT (and didn't have an initial REPLACE) you could make it somewhat sargable (on one of the tables) by expressing the join condition as LIKE but this doesn't apply here.

You could potentially create a computed column on the expression RIGHT(REPLACE(PhoneNumber,' 44', '0'),10) in both tables and then index that (INCLUDE-ing CustomerName in the index on PhoneNumbers2) and join on that column.

But if this is just a one off task this may not help much if at all (in terms of overall execution time including the time taken for index creation).