CODEWITHSUNDEEP
sql-serverindexing
Ian Warburton
Ian Warburton

Reputation: 15676

Clustered Index Scan instead of Clustered Index Seek on left join

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?

Upvotes: 2

Views: 4409

Answers (2)

Softlion
Softlion

Reputation: 12605

No index seek because you don't have a where clause. Index seek means you check a range of values in the index. As you have no where clause, there is no other choice but to scan all the index values. So the name "index scan".

It's not a table scan, it's an index scan. If you don't have an index on this column in one of the tables, you'll have table scan for second table + index scan for first table.

Upvotes: 1

anon
anon

Reputation:

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.

Upvotes: 9

Related Questions