Index Scan vs. Index Seek: Understanding the Performance Impact

A few months into my job in Kavaklidere, I encountered a report that was running unexpectedly slow. Since the dataset was large, a performance issue like this could impact multiple queries and slow down overall system performance. My first instinct was to check the execution plan, and that was when I encountered Index Scan vs. Index Seek. Understanding the difference between these two operations proved essential in optimizing query performance.

Index Scan: The "Let's Check Everything" Approach

While troubleshooting the slow report, I identified an Index Scan in the execution plan. The query was supposed to fetch order details quickly, yet it was inefficient. Upon closer inspection, I noticed that the query contained the following condition, which led to an inefficient scan:

SELECT * FROM Orders WHERE CAST(OrderID AS VARCHAR) = '1001';

So, I decided to dive deeper into the differences between Index Scan & Index Seek.

An Index Scan happens when SQL Server decides to read every row in an index. Think of it like scrolling through your entire chat history just to find that one message your friend sent weeks ago. It works, but it’s not exactly efficient.

When Does SQL Use an Index Scan?

• When there’s no efficient filtering condition (e.g., WHERE clause on a non-indexed column).

• When an index exists but isn’t useful for seeking (like a non-clustered index missing key columns).

• When SQL Server has to apply a function to the indexed column (e.g., CAST(ProductID AS NVARCHAR(50))).

Example of an Index Scan: Searching for Product Variants

SELECT * FROM Products
WHERE CAST(ProductID AS NVARCHAR(50)) = '1001';

• In this query, SQL Server must scan every row and apply CAST() before filtering, making an index nearly useless.

Performance Impact:

• More I/O operations (reads more data than necessary).

• Slower queries, especially on large product catalogs.

• High CPU usage when processing a large dataset.

Index Seek: The "Go Directly to What You Need" Approach

An Index Seek is SQL Server’s equivalent of using Ctrl+F in a document instead of manually scrolling—instant results, no wasted effort.

When Does SQL Use an Index Seek?

• When filtering on an indexed column with high selectivity (e.g., WHERE OrderID = 98765).

• When the query can take advantage of the B-tree structure of an index.

• When the column is not modified by a function in the query.

Example of an Index Seek: Fetching an Order by ID

SELECT * FROM Orders
WHERE OrderID = 12345;

Why is this good? SQL Server jumps straight to OrderID = 12345 using the index, avoiding a full scan.

Performance Impact:

• Less I/O, since only relevant rows are accessed.

• Faster execution, especially in high-traffic e-commerce databases.

• Lower CPU usage, letting SQL Server breathe easy.

How to Avoid Index Scans and Improve Performance

1- Use Index-Friendly Filters

• Bad: WHERE CAST(ProductID AS NVARCHAR(50)) = '1001'

• Good: WHERE ProductID = 1001

• Best: Ensure ProductID is stored as the correct data type from the start.

2- Index the Right Columns

• Identify which columns are used in WHERE, JOIN, and ORDER BY.

• Use covering indexes when multiple columns are needed.

• If needed, create a computed column and then create an index on it.

3- Check Execution Plans

Run your query with Actual Execution Plan in SSMS (Ctrl + M). If you see Index Scan, rethink your indexing strategy.

Indexing on Computed Columns: A Special Case

Sometimes, you may need to filter or join on expressions like CAST(ProductID AS NVARCHAR(50)) or concatenated columns like CustomerID + OrderDate. In such cases, SQL Server might force an Index Scan unless you take the right approach.

How to Optimize? Use a Computed Column + Index

Instead of doing this (which causes an Index Scan):

SELECT * FROM Orders WHERE CAST(CustomerID AS NVARCHAR(50)) = '1001';

Do this instead:

ALTER TABLE Orders 
ADD CustomerIDText AS CAST(CustomerID AS NVARCHAR(50)) PERSISTED;
CREATE INDEX IX_Orders_CustomerIDText ON Orders (CustomerIDText);

Now, your query:

SELECT * FROM Orders WHERE CustomerIDText = '1001';

This approach enables Index Seek, reducing query execution time and improving performance.

Final Thoughts

Understanding the difference between Index Scan and Index Seek helped me resolve a major performance issue in a real-world scenario. Since then, optimizing queries for Index Seek has been a fundamental part of my workflow. While Index Scan has its place, knowing when and how to avoid it can make a significant impact on database efficiency.