{"id":1782,"date":"2016-01-26T16:30:00","date_gmt":"2016-01-26T14:30:00","guid":{"rendered":"https:\/\/www.sqlinthewild.co.za\/?p=1782"},"modified":"2016-01-19T18:59:06","modified_gmt":"2016-01-19T16:59:06","slug":"does-an-index-scan-always-read-the-entire-index","status":"publish","type":"post","link":"https:\/\/www.sqlinthewild.co.za\/index.php\/2016\/01\/26\/does-an-index-scan-always-read-the-entire-index\/","title":{"rendered":"Does an index scan always read the entire index?"},"content":{"rendered":"

No.<\/p>\n

That\u2019s a bit short for a blog post, so let me explain. First, the difference between a seek and a scan.<\/p>\n

A seek is an operation which navigates down the index\u2019s b-tree looking for a row or for the start\/end of a range of rows. A seek requires a predicate and that predicate must be of the form that can be used as a search argument (SARGable).<\/p>\n

A scan is a read of the leaf level of an index, possibly also reading the intermediate pages as well.<\/p>\n

The key there is that a seek requires a predicate. If there\u2019s no predicate, we cannot have a seek and hence must have a scan.<\/p>\n

Let\u2019s look at a couple of examples. I\u2019m going to use a simple Numbers table as it\u2019s perfectly adequate for what we\u2019re doing here.<\/p>\n

CREATE TABLE Numbers (\r\n  Number INT NOT NULL PRIMARY KEY CLUSTERED\r\n);\r\n\r\nINSERT INTO Numbers (Number)\r\nSELECT TOP (1000000) ROW_NUMBER() OVER (ORDER BY (SELECT 1))\r\n  FROM msdb.sys.columns c1 CROSS JOIN msdb.sys.columns c2;<\/pre>\n
With the table created, I want to look at how many pages that clustered index has. It won\u2019t be a huge number, the table is very small.<\/p>\n
SELECT OBJECT_NAME(object_id) AS TableName, index_level, page_count, record_count, avg_record_size_in_bytes\r\n  FROM sys.dm_db_index_physical_stats(DB_ID(), OBJECT_ID('Numbers'),1,NULL, 'Detailed');<\/pre>\n
\"IndexPages\"<\/a><\/p>\n
Three level deep clustered index, 1608 pages at the leaf and a total of 1614 pages in all levels.<\/p>\n
Let\u2019s start with something basic:<\/p>\n
SET STATISTICS IO ON;\r\nGO\r\n\r\nSELECT * FROM Numbers;<\/pre>\n
\"FullTableScan\"<\/a><\/p>\n
Table ‘Numbers’. Scan count 1, logical reads 1615, physical reads 0<\/p><\/blockquote>\n
That read the entire index, every page at every level. The extra page was likely the IAM page for that index. That\u2019s kinda what we expect a scan to be.<\/p>\n
For contrast, let\u2019s try an obvious seek.<\/p>\n
SELECT * FROM Numbers WHERE Number = 137;<\/pre>\n
\"Seek\"<\/a><\/p>\n
Table ‘Numbers’. Scan count 0, logical reads 3, physical reads 0.<\/p><\/blockquote>\n
A single-row seek operation does three reads, which makes sense since we have a three-level deep clustered index.<\/p>\n
Now, what about this?<\/p>\n
SELECT TOP (1) * FROM Numbers;<\/pre>\n
It can\u2019t be a seek operation, there\u2019s no predicate. The only way this can be implemented is with a scan.<\/p>\n
\"Top1Scan\"<\/a><\/p>\n
It is indeed a scan, but did it read the entire table?<\/p>\n
Table ‘Numbers’. Scan count 1, logical reads 3, physical reads 0.<\/p><\/blockquote>\n
No. A scan of the entire index is over 1600 pages. This query read three. It\u2019s a scan, but it\u2019s a scan which stopped after reading one page of the leaf (the other two are likely the root and intermediate pages, used to locate the first page in the leaf).<\/p>\n
The scan read one row and then stopped, because that\u2019s all that was needed. It did that, because there was a 1 row row-goal added to the query. For more details on row goals, see Paul White\u2019s article on the subject<\/a>.<\/p>\n
There\u2019s other cases where a scan won\u2019t read the entire index leaf level too.<\/p>\n
Aggregations, well MIN and MAX of an indexed column:<\/p>\n
SELECT MIN(Number) FROM Numbers;<\/pre>\n
\"IndexScanMin\"<\/a><\/p>\n
Table ‘Numbers’. Scan count 1, logical reads 3, physical reads 0.<\/p><\/blockquote>\n
EXISTS:<\/p>\n
IF EXISTS(SELECT 1 FROM Numbers)\r\nSELECT 1;<\/pre>\n
\"ExistsScan\"<\/a><\/p>\n
Table ‘Numbers’. Scan count 1, logical reads 3, physical reads 0.<\/p><\/blockquote>\n
In conclusion, a seek operation requires a predicate. Without a predicate, a query has to be evaluated with a scan, but a scan doesn\u2019t always mean that the entire table is read.<\/p>\n","protected":false},"excerpt":{"rendered":"
No. That\u2019s a bit short for a blog post, so let me explain. First, the difference between a seek and a scan. A seek is an operation which navigates down the index\u2019s b-tree looking for a row or for the… (Read more)<\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"jetpack_post_was_ever_published":false,"_jetpack_newsletter_access":"","_jetpack_dont_email_post_to_subs":false,"_jetpack_newsletter_tier_id":0,"_jetpack_memberships_contains_paywalled_content":false,"_jetpack_memberships_contains_paid_content":false,"footnotes":"","jetpack_publicize_message":"New Blog Post: Does an index scan always read the entire index?","jetpack_publicize_feature_enabled":true,"jetpack_social_post_already_shared":true,"jetpack_social_options":{"image_generator_settings":{"template":"highway","default_image_id":0,"font":"","enabled":false},"version":2}},"categories":[15,16,17],"tags":[],"class_list":["post-1782","post","type-post","status-publish","format-standard","hentry","category-sql-server","category-syndication","category-t-sql"],"jetpack_publicize_connections":[],"jetpack_featured_media_url":"","jetpack_shortlink":"https:\/\/wp.me\/p7h6n-sK","jetpack_sharing_enabled":true,"_links":{"self":[{"href":"https:\/\/www.sqlinthewild.co.za\/index.php\/wp-json\/wp\/v2\/posts\/1782","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.sqlinthewild.co.za\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.sqlinthewild.co.za\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.sqlinthewild.co.za\/index.php\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.sqlinthewild.co.za\/index.php\/wp-json\/wp\/v2\/comments?post=1782"}],"version-history":[{"count":5,"href":"https:\/\/www.sqlinthewild.co.za\/index.php\/wp-json\/wp\/v2\/posts\/1782\/revisions"}],"predecessor-version":[{"id":1787,"href":"https:\/\/www.sqlinthewild.co.za\/index.php\/wp-json\/wp\/v2\/posts\/1782\/revisions\/1787"}],"wp:attachment":[{"href":"https:\/\/www.sqlinthewild.co.za\/index.php\/wp-json\/wp\/v2\/media?parent=1782"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.sqlinthewild.co.za\/index.php\/wp-json\/wp\/v2\/categories?post=1782"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.sqlinthewild.co.za\/index.php\/wp-json\/wp\/v2\/tags?post=1782"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}