An index is a way to speed up performance of a query in SQL Server. An index is a B-Tree structure on a table column or set of columns referred as index key values. This allows SQL Server to search a table based on the index key values swiftly and efficiently.
An index scan reads all the rows in an index – B-tree in the index order whereas index seek traverses a B-tree and walks through leaf nodes seeking only the matching or qualifying rows based on the filter criteria. The optimizer decides which operator to use when executing a query based on multiple factors.
Let’s look at an example. The below query filters Sales.Customer table on storeID and what we get is a clustered index scan.
The SQL Server scans the clustered index page by page and row by row to fetch all rows matching the filter criteria.
Let’s now create a non-clustered index and analyze the execution plan.
The SQL Server optimizer utilizes the available index and fetches only the rows qualifying the filter criteria.
To conclude, index seek is scanning the rows matching the filter criteria whereas index scan is scanning an entire index.
Like us on FaceBook | Join the fastest growing SQL Server group on FaceBook
