Performance on SQL Join I have a query which takes ages to run mainly due to a join between 2 tables on a PK and FK.

The Key is prefixed with 2 letters (which is always the same, RN) followed by 7 digits
So RN1234567 for example.

However if I strip out the first 2 letters (  right(tbl1.id,7) = right(tbl2.id,7)  )and join only on the digits the query speed improves greatly. Why is this?
Your statistics need updating. 

See Statistics, row estimations and the ascending date column. 

Recent dates are not proportionately represented in the statistics and so SQL Server underestimates the number of rows that will match the &gt;= 2012-12-31 predicate (as shown by the Estimated no of rows of 1 vs actual 13922). So it chooses a plan with nested loops and a seek on the other table. 

The use of right(tbl1.id,7) = right(tbl2.id,7) prevents a seek being used so you get the hash join more suitable for the actual number of rows involved.

If you update  the statistics on that column it should choose a more appropriate plan naturally. You might also consider using Trace Flags 2389 &amp; 2390 to make this issue less likely in the future.