What is an Index Range Scan? Let’s say you have a clustered index on the OrderDate column. When you have a predicate that says WHERE OrderDate > 01/01/2022 ORDER BY OrderDate, the optimizer performs a range scan. It is simply scanning the logically ordered data from a specific point. Note that the data is always logically ordered by the clustering key using the linked list. The data may or may not be physically ordered, that is another story for another day. The linked list is used to perform the scan on the supposedly ordered data. But then why does the optimizer show an Index Seek iterator for range scans. Well, there is no separate iterator for range scans and the Index Scan iterator is not the same as range scan. Index seek iterator makes sense. The optimizer first seeks to the point 01/01/2022 in the traversal and then starts scanning from that point. Sounds interesting? Watch the video below.
Get new content directly in your inbox – Click here
You can also visit DataPlatformGeeks (DPG) Video Library to watch more videos and to download video resources (presentation & demo code).
Hope the video was worth your time.