{"id":780,"date":"2009-10-08T15:20:00","date_gmt":"2009-10-08T15:20:00","guid":{"rendered":"\/blogs\/paul\/post\/Which-index-will-SQL-Server-use-to-count-all-rows.aspx"},"modified":"2013-01-01T21:07:05","modified_gmt":"2013-01-02T05:07:05","slug":"which-index-will-sql-server-use-to-count-all-rows","status":"publish","type":"post","link":"https:\/\/www.sqlskills.com\/blogs\/paul\/which-index-will-sql-server-use-to-count-all-rows\/","title":{"rendered":"Which index will SQL Server use to count all rows"},"content":{"rendered":"

\nThis is a question that came up yesterday on Twitter: will SQL Server always do a table scan to find the result of SELECT COUNT (*) FROM mytable<\/font>? <\/font>\n<\/p>\n

\nThe answer is no. The query processor will use the index with the smallest number of pages – i.e. with the least I\/O cost. <\/font>\n<\/p>\n

\nLet me quickly show you. First I'll create a simple table with no indexes. <\/font>\n<\/p>\n

\n

\n\tCREATE TABLE CTest (c1 INT IDENTITY, c2 BIGINT DEFAULT 1, c3 CHAR (1000) DEFAULT 'a');
\n\tGO
\n\tSET NOCOUNT ON;
\n\tGO
\n\tINSERT INTO CTest DEFAULT VALUES;
\n\tGO 10000<\/font> <\/font>\n\t<\/p>\n<\/blockquote>\n

\nDon't forget to unclick the Include Actual Query Plan button <\/font>\n<\/p>\n

\n \"\" <\/font>\n<\/p>\n

\nbefore running the 10000 inserts otherwise it will take forever (generating 10000 graphical plans) and SSMS will barf with: <\/font>\n<\/p>\n

\n

\n\tThe query has exceeded the maximum number of result sets that can be displayed in the Execution Plan pane. Only the first 250 result sets are displayed in the Execution Plan pane.<\/font> <\/font>\n\t<\/p>\n<\/blockquote>\n

\nNow if we do the SELECT COUNT (*)<\/font>, the plan is: <\/font>\n<\/p>\n

\n \"\" <\/font>\n<\/p>\n

\nThe query processor has no choice but to do a table scan. <\/font>\n<\/p>\n

\nNow I'll add a nonclustered index that will have less pages that the table itself: <\/font>\n<\/p>\n

\n

\n\tCREATE NONCLUSTERED INDEX CTest_1 ON CTest (c2);
\n\tGO<\/font> <\/font>\n\t<\/p>\n<\/blockquote>\n

\nAnd the select plan is now: <\/font>\n<\/p>\n

\n \"\" <\/font>\n<\/p>\n

\nNotice that the Table Scan operator has changed to an Index Scan operator on the new CTest_1<\/font> index. This is because the nonclustered index has fewer pages than the table and so the I\/O cost is lower. <\/font>\n<\/p>\n

\nNow I'll create an index that's even smaller, over the integer column: <\/font>\n<\/p>\n

\n

\n\tCREATE NONCLUSTERED INDEX CTest_2 ON CTest (c1);
\n\tGO<\/font> <\/font>\n\t<\/p>\n<\/blockquote>\n

\nAnd the plan should change to use the smallest index again: <\/font>\n<\/p>\n

\n \"\"<\/font>\n<\/p>\n

\nAnd it does, as I expected.<\/font>\n<\/p>\n

\nLet's look at the relative page counts for each index and the table:<\/font>\n<\/p>\n

\n

\n\tSELECT [index_id], [page_count]
\n\tFROM sys.dm_db_index_physical_stats (DB_ID (), OBJECT_ID ('CTest'), NULL, NULL, 'LIMITED');
\n\tGO<\/font>\n\t<\/p>\n

\n\tindex_id    page_count
\n\t———– ——————–
\n\t0           1436
\n\t2           28
\n\t3           19<\/font>\n\t<\/p>\n<\/blockquote>\n

\nAny time the query processor is working out what plan to use, one of the key factors in plan choice is the I\/O cost.<\/font>\n<\/p>\n

\nHope this helps!<\/font><\/p>\n","protected":false},"excerpt":{"rendered":"

This is a question that came up yesterday on Twitter: will SQL Server always do a table scan to find the result of SELECT COUNT (*) FROM mytable? The answer is no. The query processor will use the index with the smallest number of pages – i.e. with the least I\/O cost. Let me quickly […]<\/p>\n","protected":false},"author":5,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[66,72],"tags":[],"class_list":["post-780","post","type-post","status-publish","format-standard","hentry","category-performance-tuning","category-query-processor"],"yoast_head":"\nWhich index will SQL Server use to count all rows - Paul S. Randal<\/title>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/www.sqlskills.com\/blogs\/paul\/which-index-will-sql-server-use-to-count-all-rows\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Which index will SQL Server use to count all rows - Paul S. Randal\" \/>\n<meta property=\"og:description\" content=\"This is a question that came up yesterday on Twitter: will SQL Server always do a table scan to find the result of SELECT COUNT (*) FROM mytable? The answer is no. The query processor will use the index with the smallest number of pages – i.e. with the least I\/O cost. Let me quickly […]\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.sqlskills.com\/blogs\/paul\/which-index-will-sql-server-use-to-count-all-rows\/\" \/>\n<meta property=\"og:site_name\" content=\"Paul S. Randal\" \/>\n<meta property=\"article:published_time\" content=\"2009-10-08T15:20:00+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2013-01-02T05:07:05+00:00\" \/>\n<meta property=\"og:image\" content=\"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-content\/uploads\/2009\/10\/cplan1.jpg\" \/>\n<meta name=\"author\" content=\"Paul Randal\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Paul Randal\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"2 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"WebPage\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/which-index-will-sql-server-use-to-count-all-rows\/\",\"url\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/which-index-will-sql-server-use-to-count-all-rows\/\",\"name\":\"Which index will SQL Server use to count all rows - Paul S. Randal\",\"isPartOf\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/#website\"},\"primaryImageOfPage\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/which-index-will-sql-server-use-to-count-all-rows\/#primaryimage\"},\"image\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/which-index-will-sql-server-use-to-count-all-rows\/#primaryimage\"},\"thumbnailUrl\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-content\/uploads\/2009\/10\/cplan1.jpg\",\"datePublished\":\"2009-10-08T15:20:00+00:00\",\"dateModified\":\"2013-01-02T05:07:05+00:00\",\"author\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/#\/schema\/person\/ffcec826c18782e1e0adf173826a7fce\"},\"breadcrumb\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/which-index-will-sql-server-use-to-count-all-rows\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.sqlskills.com\/blogs\/paul\/which-index-will-sql-server-use-to-count-all-rows\/\"]}]},{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/which-index-will-sql-server-use-to-count-all-rows\/#primaryimage\",\"url\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-content\/uploads\/2009\/10\/cplan1.jpg\",\"contentUrl\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-content\/uploads\/2009\/10\/cplan1.jpg\"},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/which-index-will-sql-server-use-to-count-all-rows\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Which index will SQL Server use to count all rows\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/#website\",\"url\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/\",\"name\":\"Paul S. Randal\",\"description\":\"In Recovery...\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":\"Person\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/#\/schema\/person\/ffcec826c18782e1e0adf173826a7fce\",\"name\":\"Paul Randal\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/#\/schema\/person\/image\/\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/0b6a266bba2f088f2551ef529293001bd73bf026bc1908b9866728c062beeeb6?s=96&d=mm&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/0b6a266bba2f088f2551ef529293001bd73bf026bc1908b9866728c062beeeb6?s=96&d=mm&r=g\",\"caption\":\"Paul Randal\"},\"sameAs\":[\"http:\/\/3.209.169.194\/blogs\/paul\"],\"url\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/author\/paul\/\"}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"Which index will SQL Server use to count all rows - Paul S. Randal","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/www.sqlskills.com\/blogs\/paul\/which-index-will-sql-server-use-to-count-all-rows\/","og_locale":"en_US","og_type":"article","og_title":"Which index will SQL Server use to count all rows - Paul S. Randal","og_description":"This is a question that came up yesterday on Twitter: will SQL Server always do a table scan to find the result of SELECT COUNT (*) FROM mytable? The answer is no. The query processor will use the index with the smallest number of pages – i.e. with the least I\/O cost. Let me quickly […]","og_url":"https:\/\/www.sqlskills.com\/blogs\/paul\/which-index-will-sql-server-use-to-count-all-rows\/","og_site_name":"Paul S. Randal","article_published_time":"2009-10-08T15:20:00+00:00","article_modified_time":"2013-01-02T05:07:05+00:00","og_image":[{"url":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-content\/uploads\/2009\/10\/cplan1.jpg","type":"","width":"","height":""}],"author":"Paul Randal","twitter_misc":{"Written by":"Paul Randal","Est. reading time":"2 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"WebPage","@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/which-index-will-sql-server-use-to-count-all-rows\/","url":"https:\/\/www.sqlskills.com\/blogs\/paul\/which-index-will-sql-server-use-to-count-all-rows\/","name":"Which index will SQL Server use to count all rows - Paul S. Randal","isPartOf":{"@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/#website"},"primaryImageOfPage":{"@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/which-index-will-sql-server-use-to-count-all-rows\/#primaryimage"},"image":{"@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/which-index-will-sql-server-use-to-count-all-rows\/#primaryimage"},"thumbnailUrl":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-content\/uploads\/2009\/10\/cplan1.jpg","datePublished":"2009-10-08T15:20:00+00:00","dateModified":"2013-01-02T05:07:05+00:00","author":{"@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/#\/schema\/person\/ffcec826c18782e1e0adf173826a7fce"},"breadcrumb":{"@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/which-index-will-sql-server-use-to-count-all-rows\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.sqlskills.com\/blogs\/paul\/which-index-will-sql-server-use-to-count-all-rows\/"]}]},{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/which-index-will-sql-server-use-to-count-all-rows\/#primaryimage","url":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-content\/uploads\/2009\/10\/cplan1.jpg","contentUrl":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-content\/uploads\/2009\/10\/cplan1.jpg"},{"@type":"BreadcrumbList","@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/which-index-will-sql-server-use-to-count-all-rows\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/www.sqlskills.com\/blogs\/paul\/"},{"@type":"ListItem","position":2,"name":"Which index will SQL Server use to count all rows"}]},{"@type":"WebSite","@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/#website","url":"https:\/\/www.sqlskills.com\/blogs\/paul\/","name":"Paul S. Randal","description":"In Recovery...","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/www.sqlskills.com\/blogs\/paul\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Person","@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/#\/schema\/person\/ffcec826c18782e1e0adf173826a7fce","name":"Paul Randal","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/#\/schema\/person\/image\/","url":"https:\/\/secure.gravatar.com\/avatar\/0b6a266bba2f088f2551ef529293001bd73bf026bc1908b9866728c062beeeb6?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/0b6a266bba2f088f2551ef529293001bd73bf026bc1908b9866728c062beeeb6?s=96&d=mm&r=g","caption":"Paul Randal"},"sameAs":["http:\/\/3.209.169.194\/blogs\/paul"],"url":"https:\/\/www.sqlskills.com\/blogs\/paul\/author\/paul\/"}]}},"_links":{"self":[{"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/posts\/780","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/users\/5"}],"replies":[{"embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/comments?post=780"}],"version-history":[{"count":0,"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/posts\/780\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/media?parent=780"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/categories?post=780"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/tags?post=780"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}