Index Scan vs Index Seek in SQL Server
💡 Concept Name
Index Scan and Index Seek are operations used by SQL Server when accessing indexes. They determine how efficiently SQL Server can retrieve data.
📘 Quick Intro
SQL Server chooses between an Index Seek or Index Scan depending on query predicates and index structure. A Seek is efficient and specific, while a Scan reads through all or most index rows—affecting performance.
🧠 Analogy / Short Story
Imagine finding a contact in your phone. A **Seek** is like typing the exact name in search—fast and targeted. A **Scan** is like scrolling your entire contact list—still effective but much slower if you have thousands of entries.
🔧 Technical Explanation
- Index Seek: Uses index structure to directly locate rows matching a WHERE clause. Efficient and selective.
- Index Scan: Reads all or most rows in the index, even if few match the condition.
- Seeks occur when there is a **SARGable** predicate (Search ARGument Able).
- Scans may occur due to non-SARGable queries, missing indexes, or large result sets.
- Execution plans visually show Seek (magnifying glass) or Scan (table symbol).
🎯 Purpose & Use Case
- ✅ Use Index Seek when querying selective rows with indexed columns.
- ✅ Use Index Scan when fetching most rows or no suitable index exists.
- ✅ Tune queries for Seeks to improve performance.
- ✅ Avoid functions on indexed columns in WHERE to enable Seek.
💻 Real Code Example
-- Index Seek: Efficient, uses index
SELECT FirstName, LastName
FROM Employees
WHERE EmployeeID = 1001;
-- Index Scan: Reads all rows
SELECT FirstName, LastName
FROM Employees
WHERE YEAR(HireDate) = 2022;
❓ Interview Q&A
Q1: What is the difference between Index Seek and Index Scan?
A: Seek is targeted and efficient, while Scan reads the entire index or table, which can be slower.
Q2: What causes SQL Server to use Index Scan?
A: Non-SARGable conditions, missing indexes, or when most rows are returned.
Q3: How can you ensure a Seek happens?
A: Use SARGable WHERE conditions, avoid wrapping indexed columns in functions, and ensure proper indexing.
Q4: What does SARGable mean?
A: A condition that can take advantage of an index search, e.g., column = value.
Q5: Which is better for performance: Seek or Scan?
A: Index Seek is generally better as it reads fewer rows.
Q6: Can a query with a Scan still perform well?
A: Yes, especially if the dataset is small or most rows are needed anyway.
Q7: How do you identify a Scan vs Seek?
A: View the execution plan—Seek uses a magnifying glass icon; Scan shows a table icon.
Q8: Do filtered indexes help enable Seek?
A: Yes, if the WHERE clause aligns with the filter predicate.
Q9: What happens if no index exists on the queried column?
A: SQL Server will perform a full table or index scan.
Q10: Can composite indexes improve seek performance?
A: Yes, if the WHERE clause uses the leading column(s) of the index.
📝 MCQs
Q1. What operation reads only necessary rows via index?
- Index Scan
- Index Seek
- Table Scan
- Full Join
Q2. Which operation scans the entire index?
- Seek
- Lookup
- Index Scan
- Hash Match
Q3. What makes a query SARGable?
- Function calls
- Indexed column used in simple comparison
- GROUP BY
- HAVING clause
Q4. Which symbol in execution plan shows Index Seek?
- Table icon
- Clustered icon
- Magnifying glass
- Book icon
Q5. What causes an Index Scan even with indexes present?
- ORDER BY
- JOIN clause
- Function on indexed column
- Aliased columns
Q6. Which is more efficient for selective queries?
- Index Seek
- Index Scan
- Table Scan
- Sort
Q7. What does a non-SARGable predicate cause?
- Seek
- Scan
- Deadlock
- Index Rebuild
Q8. Where are temporary tables stored in SQL Server?
- model
- msdb
- master
- tempdb
Q9. What clause prevents Seek if misused?
- JOIN
- GROUP BY
- HAVING
- WHERE with function on indexed column
Q10. What helps ensure Seek for date queries?
- Use YEAR(HireDate)
- Use direct comparison (e.g. HireDate >= '2024-01-01')
- Use DATEPART
- Use BETWEEN on function results
💡 Bonus Insight
Even with good indexing, SQL Server may default to Scan based on estimated row counts. Use query hints, filtered indexes, and SARGable patterns to improve likelihood of Seek usage. Always validate using the execution plan.
📄 PDF Download
Need a handy summary for your notes? Download this topic as a PDF!