15. Execution plans, seeks vs scans and SARGability

• Index Seek — navigates the B-tree to matching rows (good for selective predicates).
• Index/Table Scan — reads everything (fine for small tables or returning most rows).
• Key Lookup — after a non-clustered seek, fetch extra columns from the clustered index; many lookups → add INCLUDE columns.
• SARGable (Search ARGument-able) predicates can use an index seek:

-- ❌ Non-SARGable: function or calculation on the column
WHERE YEAR(OrderDate) = 2026
WHERE LEFT(Name, 3) = 'Abc'
WHERE Salary * 1.1 > 100000
WHERE CONVERT(VARCHAR, Phone) = '98765'          -- implicit/explicit conversion of the column

-- ✅ SARGable
WHERE OrderDate >= '2026-01-01' AND OrderDate < '2027-01-01'
WHERE Name LIKE 'Abc%'                           -- leading wildcard '%abc' is NOT sargable
WHERE Salary > 100000 / 1.1

Read plans right-to-left, top-to-bottom; look at thick arrows, estimated vs actual rows (bad estimates → stale statistics or parameter sniffing), warnings (spills, implicit conversions), and the most expensive operators. SET STATISTICS IO, TIME ON shows logical reads and CPU time.