{"id":173,"date":"2009-03-05T16:09:27","date_gmt":"2009-03-05T14:09:27","guid":{"rendered":"https:\/\/www.sqlinthewild.co.za\/?p=173"},"modified":"2012-10-03T14:43:55","modified_gmt":"2012-10-03T12:43:55","slug":"when-is-a-seek-actually-a-scan","status":"publish","type":"post","link":"https:\/\/www.sqlinthewild.co.za\/index.php\/2009\/03\/05\/when-is-a-seek-actually-a-scan\/","title":{"rendered":"When is a seek actually a scan?"},"content":{"rendered":"

Most people who know SQL execution plans will say, without reservation, that an index seek on a particular index is better than an index scan on the same index. In the vast majority of cases, that’s true, but there are times when what appears in the execution plan as an index seek is actually an index scan.<\/p>\n

Let me show an example<\/p>\n

\r\n\r\nCREATE TABLE TestingSeeks (\r\nid int identity (1,1) not null,\r\nSomeStr char(6) default '' -- a filler\r\n)\r\nGO\r\n\r\ninsert into TestingSeeks (SomeStr)\r\nselect top (500000) ''\r\nfrom sys.columns c1 cross join sys.columns c2\r\n\r\n<\/pre>\n
We have a table here with an identity column on it, starting at 1 and incrementing by 1 row. Hence, there will be no negative values in the table. I’m going to then put a nonclustered index on that column (the table has no cluster, it’s a heap)<\/p>\n
\r\n\r\nCREATE NONCLUSTERED INDEX idx_Seek ON TestingSeeks (id)\r\n\r\n<\/pre>\n
Fair enough. If I query all the rows in the table and retrieve just the ID column, I’ll get a scan on that index, as is pretty much expected and Statistics IO tells me that 935 pages were read<\/p>\n
<\/p>\n
\"\"<\/p>\n
So a read of the entire index is 935 pages. Now, let me add a filter.<\/p>\n
select id from TestingSeeks\r\n\r\nwhere id>0<\/pre>\n
That predicate is SARGable and there is an appropriate index. Sure enough, we get an index seek here.<\/p>\n
\"\"<\/p>\n
That’s good. Isn’t it?<\/p>\n
Well, not really. That filter’s going to match all the rows in the table. We know there are none with an id less than 1. Statistics IO tells me that 935 pages were read, exactly the same as for the scan. It’s a seek operation, but it’s done exactly the same work as the scan did.<\/p>\n
Moral of the story: A seek doesn’t always read only a portion of the index, a seek on an index is not necessarily doing less work than a scan on the same index and silly tricks intended to force an index seek are not going to make a query run faster.<\/p>\n","protected":false},"excerpt":{"rendered":"
Most people who know SQL execution plans will say, without reservation, that an index seek on a particular index is better than an index scan on the same index. In the vast majority of cases, that’s true, but there are… (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":"","jetpack_publicize_feature_enabled":true,"jetpack_social_post_already_shared":false,"jetpack_social_options":{"image_generator_settings":{"template":"highway","default_image_id":0,"font":"","enabled":false},"version":2}},"categories":[24,25,15,16],"tags":[],"class_list":["post-173","post","type-post","status-publish","format-standard","hentry","category-indexes","category-performance","category-sql-server","category-syndication"],"jetpack_publicize_connections":[],"jetpack_featured_media_url":"","jetpack_shortlink":"https:\/\/wp.me\/p7h6n-2N","jetpack_sharing_enabled":true,"_links":{"self":[{"href":"https:\/\/www.sqlinthewild.co.za\/index.php\/wp-json\/wp\/v2\/posts\/173","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=173"}],"version-history":[{"count":2,"href":"https:\/\/www.sqlinthewild.co.za\/index.php\/wp-json\/wp\/v2\/posts\/173\/revisions"}],"predecessor-version":[{"id":1422,"href":"https:\/\/www.sqlinthewild.co.za\/index.php\/wp-json\/wp\/v2\/posts\/173\/revisions\/1422"}],"wp:attachment":[{"href":"https:\/\/www.sqlinthewild.co.za\/index.php\/wp-json\/wp\/v2\/media?parent=173"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.sqlinthewild.co.za\/index.php\/wp-json\/wp\/v2\/categories?post=173"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.sqlinthewild.co.za\/index.php\/wp-json\/wp\/v2\/tags?post=173"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}