What is a seek scan (Operator number 2)?
Seek scan does not scan all the rows to go to a record, it uses indexes and b-tree logic to get to a record. Below is a sample diagram, which explains how B-Tree fundamental works. The below diagram shows how index will work for number from 1-50:- • Let us say you want to search 39. SQL Server will first start from the first node i.e. root node. • It will see that the number is greater than 30, so it moves to the 50 node. • Further in Non-Leaf nodes it compares is it more than 40 or less than 40. As it’s less than 40 it loops through the leaf nodes which belong to 40 nodes. In table scan it scans all rows while in seek scan it scans less number of rows comparatively. For instance to get to the record 39 it only scanned 9 records rather than travelling through all 50 records.
