Clustered Index Scan instead of Clustered Index Seek on left join

Question

SELECT *
FROM
    tbl_transaction t
LEFT JOIN
    tbl_transaction_hsbc ht
ON 
    t.transactionid = ht.transactionid

transactionid on both tables is the primary key so why no index seek?

Answer 1

Maybe it's the SELECT * ... and maybe because you're returning the entire table, there is no advantage to seeking. What do you want a seek to do, seek incrementally to every row? A scan is much more efficient.

I realize you've probably read or been told to avoid scans at all cost. I think there needs to be more context associated with that. Sometimes a scan is the right answer and the most efficient path to the data. If there query is slow, perhaps you could show an actual execution plan, and we can help pinpoint the problem. But the answer isn't going to be forcing this query to use a seek.