fastest way to compare two columns with different data types

Question

I have two tables that I need to join over a linked server, but I have a problem with the source data that I am stuck with for now.

The column names that I need to join on are account_number and member_number respectively.

My issue is that account_number is a varchar(10) and is always padded with leading zeros, but member_number is a varchar(12) (don't ask why, the last 2 are never used) but is not padded with leading zeros.

If we say that account_number is in A and member_number is in B, I have come up with the following solutions:

SELECT * FROM 
  A INNER JOIN B 
  ON CAST(A.account_number AS BIGINT) = CAST(B.member_number AS BIGINT)

and

SELECT * FROM 
  A INNER JOIN B 
  ON A.account_number = RIGHT('0000000000'+B.member_number, 10)

The problem is that they are super slow!

It must be the fact that the functions are forcing table scans, but I'm not sure what else to do about this. Is there any way to do this comparison that is faster? Maybe with some variation of like or something?

Answer 1

The fastest way is to create a computed column so they are of the same types and then build an index on that column. Something like:

alter table b add account_number as ( RIGHT('0000000000'+B.member_number, 10) );

create index b_acount_number on b(account_number);

Then run the query as:

SELECT *
FROM A INNER JOIN
     B 
     ON A.account_number = b.account_number;

That is probably the fastest you can get.