{"id":495,"date":"2012-05-10T04:43:37","date_gmt":"2012-05-10T04:43:37","guid":{"rendered":"\/blogs\/joe\/post\/Selectivity-Guesses-in-absence-of-Statistics.aspx"},"modified":"2013-12-29T19:18:30","modified_gmt":"2013-12-30T03:18:30","slug":"selectivity-guesses-in-absence-of-statistics","status":"publish","type":"post","link":"https:\/\/www.sqlskills.com\/blogs\/joe\/selectivity-guesses-in-absence-of-statistics\/","title":{"rendered":"Selectivity Guesses in absence of Statistics"},"content":{"rendered":"<p>Let\u2019s say you have a heap table with 1,000,000 rows in it.\u00a0 Let\u2019s also say that your automatic creation of statistics are disabled, as well as updates to the statistics (and in this scenario, there are NO existing indexes or statistics).<\/p>\n<p>What kind of selectivity guess would the optimizer make for a query like the following? (I copied over the FactInternetSales table with no indexes and put 1,000,000 rows in it \u2013 tested on SQL Server 2012, 11.0.2316)<\/p>\n<div class=\"csharpcode\">\n<pre class=\"alt\"><span class=\"kwrd\">SELECT<\/span> SalesOrderNumber\r\n<span class=\"kwrd\">FROM<\/span> dbo.FactInternetSales<\/pre>\n<pre class=\"alt\"><span class=\"kwrd\">WHERE<\/span> ProductKey = 537;<\/pre>\n<\/div>\n<style type=\"text\/css\"><!--\n.csharpcode, .csharpcode pre\n{\n\tfont-size: small;\n\tcolor: black;\n\tfont-family: consolas, \"Courier New\", courier, monospace;\n\tbackground-color: #ffffff;\n\t\/*white-space: pre;*\/\n}\n.csharpcode pre { margin: 0em; }\n.csharpcode .rem { color: #008000; }\n.csharpcode .kwrd { color: #0000ff; }\n.csharpcode .str { color: #006080; }\n.csharpcode .op { color: #0000c0; }\n.csharpcode .preproc { color: #cc6633; }\n.csharpcode .asp { background-color: #ffff00; }\n.csharpcode .html { color: #800000; }\n.csharpcode .attr { color: #ff0000; }\n.csharpcode .alt \n{\n\tbackground-color: #f4f4f4;\n\twidth: 100%;\n\tmargin: 0em;\n}\n.csharpcode .lnum { color: #606060; }\n--><\/style>\n<p>First of all, the <em>actual<\/em> plan on my system was as follows:<\/p>\n<p><a href=\"https:\/\/www.sqlskills.com\/blogs\/joe\/wp-content\/uploads\/windows-live-writer\/query-guesses\/11a88cc2\/snaghtml1904f4f.png\"><img fetchpriority=\"high\" decoding=\"async\" style=\"background-image: none; padding-left: 0px; padding-right: 0px; display: inline; padding-top: 0px; border: 0px;\" title=\"SNAGHTML1904f4f\" alt=\"SNAGHTML1904f4f\" src=\"https:\/\/www.sqlskills.com\/blogs\/joe\/wp-content\/uploads\/windows-live-writer\/query-guesses\/5bc397a7\/snaghtml1904f4f_thumb.png\" width=\"512\" height=\"237\" border=\"0\" \/><\/a><\/p>\n<p>If I look at the properties of the table scan, I see the actual number of rows was 23,042 versus the estimated of 31,622.8.\u00a0 (<em>As an aside \u2013 execute SELECT POWER(1000000.0,.75) and you\u2019ll see how the estimated number of rows was derived<\/em>).\u00a0 I also see that the table cardinality is known (and correct) at 1,000,000.<\/p>\n<p><a href=\"https:\/\/www.sqlskills.com\/blogs\/joe\/wp-content\/uploads\/windows-live-writer\/query-guesses\/68bd77b8\/snaghtml19c0871.png\"><img decoding=\"async\" style=\"background-image: none; padding-left: 0px; padding-right: 0px; display: inline; padding-top: 0px; border: 0px;\" title=\"SNAGHTML19c0871\" alt=\"SNAGHTML19c0871\" src=\"https:\/\/www.sqlskills.com\/blogs\/joe\/wp-content\/uploads\/windows-live-writer\/query-guesses\/39f7bf16\/snaghtml19c0871_thumb.png\" width=\"514\" height=\"455\" border=\"0\" \/><\/a><\/p>\n<p>What if I have a predicate referencing another column, like OrderDateKey?\u00a0 This too results in an estimate of 31,622.8 rows.<\/p>\n<p>The following table shows a sampling of various queries and associated search conditions, along with their associated row estimates:<\/p>\n<table width=\"469\" border=\"1\" cellspacing=\"1\" cellpadding=\"2\">\n<tbody>\n<tr>\n<td valign=\"top\" width=\"244\">Query<\/td>\n<td valign=\"top\" width=\"87\">Estimated Rows<\/td>\n<td valign=\"top\" width=\"132\">Percentage (based on 1,000,000 rows)<\/td>\n<\/tr>\n<tr>\n<td valign=\"top\" width=\"244\">SELECT SalesOrderNumber<\/p>\n<p>FROM dbo.FactInternetSales<\/p>\n<p>WHERE ProductKey = 537;<\/td>\n<td valign=\"top\" width=\"87\">31,622.8<\/td>\n<td valign=\"top\" width=\"132\">3.16228%<\/td>\n<\/tr>\n<tr>\n<td valign=\"top\" width=\"244\">SELECT SalesOrderNumber<\/p>\n<p>FROM dbo.FactInternetSales<\/p>\n<p>WHERE OrderDateKey = 20040224;<\/td>\n<td valign=\"top\" width=\"87\">31,622.8<\/td>\n<td valign=\"top\" width=\"132\">3.16228%<\/td>\n<\/tr>\n<tr>\n<td valign=\"top\" width=\"244\">SELECT SalesOrderNumber<\/p>\n<p>FROM dbo.FactInternetSales<\/p>\n<p>WHERE ProductKey BETWEEN 537 AND 600;<\/td>\n<td valign=\"top\" width=\"87\">90,000<\/td>\n<td valign=\"top\" width=\"132\">9%<\/td>\n<\/tr>\n<tr>\n<td valign=\"top\" width=\"244\">SELECT SalesOrderNumber<\/p>\n<p>FROM dbo.FactInternetSales<\/p>\n<p>WHERE ProductKey BETWEEN 537 AND 537;<\/td>\n<td valign=\"top\" width=\"87\">90,000<\/td>\n<td valign=\"top\" width=\"132\">9%<\/td>\n<\/tr>\n<tr>\n<td valign=\"top\" width=\"244\">SELECT SalesOrderNumber<\/p>\n<p>FROM dbo.FactInternetSales<\/p>\n<p>WHERE TotalProductCost &lt; 100.00;<\/td>\n<td valign=\"top\" width=\"87\">300,000<\/td>\n<td valign=\"top\" width=\"132\">30%<\/td>\n<\/tr>\n<tr>\n<td valign=\"top\" width=\"244\">SELECT SalesOrderNumber<\/p>\n<p>FROM dbo.FactInternetSales<\/p>\n<p>WHERE TotalProductCost &gt; 100.00;<\/td>\n<td valign=\"top\" width=\"87\">300,000<\/td>\n<td valign=\"top\" width=\"132\">30%<\/td>\n<\/tr>\n<tr>\n<td valign=\"top\" width=\"244\">SELECT SalesOrderNumber<\/p>\n<p>FROM dbo.FactInternetSales<\/p>\n<p>WHERE TotalProductCost &gt; 100.00 AND<\/p>\n<p>TotalProductCost &lt; 200.00;<\/td>\n<td valign=\"top\" width=\"87\">90,000<\/td>\n<td valign=\"top\" width=\"132\">9%<\/td>\n<\/tr>\n<tr>\n<td valign=\"top\" width=\"244\">SELECT SalesOrderNumber<\/p>\n<p>FROM dbo.FactInternetSales<\/p>\n<p>WHERE TotalProductCost &gt;= 100.00 AND<\/p>\n<p>TotalProductCost &lt;= 200.00;<\/td>\n<td valign=\"top\" width=\"87\">90,000<\/td>\n<td valign=\"top\" width=\"132\">9%<\/td>\n<\/tr>\n<tr>\n<td valign=\"top\" width=\"244\">SELECT SalesOrderNumber<\/p>\n<p>FROM dbo.FactInternetSales<\/p>\n<p>WHERE TotalProductCost LIKE 100.00;<\/td>\n<td valign=\"top\" width=\"87\">100,000<\/td>\n<td valign=\"top\" width=\"132\">10%<\/td>\n<\/tr>\n<tr>\n<td valign=\"top\" width=\"244\">SELECT SalesOrderNumber<\/p>\n<p>FROM dbo.FactInternetSales<\/p>\n<p>WHERE SalesOrderNumber LIKE &#8216;%A%&#8217;;<\/td>\n<td valign=\"top\" width=\"87\">539,232<\/td>\n<td valign=\"top\" width=\"132\">53.9232%<\/td>\n<\/tr>\n<tr>\n<td valign=\"top\" width=\"244\">SELECT SalesOrderNumber<\/p>\n<p>FROM dbo.FactInternetSales<\/p>\n<p>WHERE SalesOrderNumber LIKE &#8216;A%&#8217;;<\/td>\n<td valign=\"top\" width=\"87\">269,616<\/td>\n<td valign=\"top\" width=\"132\">26.9616%<\/td>\n<\/tr>\n<tr>\n<td valign=\"top\" width=\"244\">SELECT SalesOrderNumber<\/p>\n<p>FROM dbo.FactInternetSales<\/p>\n<p>WHERE SalesOrderNumber LIKE &#8216;%A&#8217;;<\/td>\n<td valign=\"top\" width=\"87\">269,616<\/td>\n<td valign=\"top\" width=\"132\">26.9616%<\/td>\n<\/tr>\n<tr>\n<td valign=\"top\" width=\"244\">SELECT SalesOrderNumber<\/p>\n<p>FROM dbo.FactInternetSales<\/p>\n<p>WHERE TotalProductCost &gt; (ProductStandardCost + 10.00);<\/td>\n<td valign=\"top\" width=\"87\">300,000<\/td>\n<td valign=\"top\" width=\"132\">30%<\/td>\n<\/tr>\n<tr>\n<td valign=\"top\" width=\"244\">SELECT SalesOrderNumber<\/p>\n<p>FROM dbo.FactInternetSales<\/p>\n<p>WHERE ProductKey IN (1);<\/td>\n<td valign=\"top\" width=\"87\">31,622.8<\/td>\n<td valign=\"top\" width=\"132\">3.16228%<\/td>\n<\/tr>\n<tr>\n<td valign=\"top\" width=\"244\">SELECT SalesOrderNumber<\/p>\n<p>FROM dbo.FactInternetSales<\/p>\n<p>WHERE ProductKey IN (1,2);<\/td>\n<td valign=\"top\" width=\"87\">62,245.6<\/td>\n<td valign=\"top\" width=\"132\">6.22456%<\/td>\n<\/tr>\n<tr>\n<td valign=\"top\" width=\"244\">SELECT SalesOrderNumber<\/p>\n<p>FROM dbo.FactInternetSales<\/p>\n<p>WHERE ProductKey = 1 OR<\/p>\n<p>ProductKey = 2;<\/td>\n<td valign=\"top\" width=\"87\">62,245.6<\/td>\n<td valign=\"top\" width=\"132\">6.22456%<\/td>\n<\/tr>\n<tr>\n<td valign=\"top\" width=\"244\">SELECT SalesOrderNumber<\/p>\n<p>FROM dbo.FactInternetSales<\/p>\n<p>WHERE ProductKey IN (1,2,3);<\/td>\n<td valign=\"top\" width=\"87\">91,900<\/td>\n<td valign=\"top\" width=\"132\">9.19%<\/td>\n<\/tr>\n<tr>\n<td valign=\"top\" width=\"244\">SELECT SalesOrderNumber<\/p>\n<p>FROM dbo.FactInternetSales<\/p>\n<p>WHERE ProductKey = 1 OR<\/p>\n<p>ProductKey = 2 OR<\/p>\n<p>ProductKey = 3;<\/td>\n<td valign=\"top\" width=\"87\">91,900<\/td>\n<td valign=\"top\" width=\"132\">9.19%<\/td>\n<\/tr>\n<tr>\n<td valign=\"top\" width=\"244\">SELECT SalesOrderNumber<\/p>\n<p>FROM dbo.FactInternetSales<\/p>\n<p>WHERE ProductKey = 1 AND<\/p>\n<p>ProductKey = 2 AND<\/p>\n<p>ProductKey = 3;<\/td>\n<td valign=\"top\" width=\"87\">10,746.1<\/td>\n<td valign=\"top\" width=\"132\">1.07461%<\/td>\n<\/tr>\n<tr>\n<td valign=\"top\" width=\"244\">SELECT SalesOrderNumber<\/p>\n<p>FROM dbo.FactInternetSales<\/p>\n<p>WHERE ProductKey = 1 AND<\/p>\n<p>ProductKey = 2;<\/td>\n<td valign=\"top\" width=\"87\">13,335.2<\/td>\n<td valign=\"top\" width=\"132\">1.33352%<\/td>\n<\/tr>\n<tr>\n<td valign=\"top\" width=\"244\">SELECT SalesOrderNumber<\/p>\n<p>FROM dbo.FactInternetSales<\/p>\n<p>WHERE (TaxAmt * UnitPrice) &gt; 10.00;<\/td>\n<td valign=\"top\" width=\"87\">300,000<\/td>\n<td valign=\"top\" width=\"132\">30%<\/td>\n<\/tr>\n<tr>\n<td valign=\"top\" width=\"244\">SELECT SalesOrderNumber<\/p>\n<p>FROM dbo.FactInternetSales<\/p>\n<p>WHERE ProductKey NOT IN (1);<\/td>\n<td valign=\"top\" width=\"87\">968,377<\/td>\n<td valign=\"top\" width=\"132\">96.8377%<\/td>\n<\/tr>\n<tr>\n<td valign=\"top\" width=\"244\">SELECT SalesOrderNumber<\/p>\n<p>FROM dbo.FactInternetSales<\/p>\n<p>WHERE ProductKey &lt;&gt; 1;<\/td>\n<td valign=\"top\" width=\"87\">968,377<\/td>\n<td valign=\"top\" width=\"132\">96.8377%<\/td>\n<\/tr>\n<tr>\n<td valign=\"top\" width=\"244\">SELECT SalesOrderNumber<\/p>\n<p>FROM dbo.FactInternetSales<\/p>\n<p>WHERE ProductKey NOT IN (1,2);<\/td>\n<td valign=\"top\" width=\"87\">13,335.2<\/td>\n<td valign=\"top\" width=\"132\">1.33352%<\/td>\n<\/tr>\n<tr>\n<td valign=\"top\" width=\"244\">SELECT SalesOrderNumber<\/p>\n<p>FROM dbo.FactInternetSales<\/p>\n<p>WHERE ProductKey NOT IN (1,2,3);<\/td>\n<td valign=\"top\" width=\"87\">10746.1<\/td>\n<td valign=\"top\" width=\"132\">1.07461%<\/td>\n<\/tr>\n<tr>\n<td valign=\"top\" width=\"244\">SELECT SalesOrderNumber<\/p>\n<p>FROM dbo.FactInternetSales<\/p>\n<p>WHERE ProductKey &lt;&gt; 1 AND<\/p>\n<p>ProductKey &lt;&gt; 2 AND<\/p>\n<p>ProductKey &lt;&gt; 3;<\/td>\n<td valign=\"top\" width=\"87\">10746.1<\/td>\n<td valign=\"top\" width=\"132\">1.07461%<\/td>\n<\/tr>\n<tr>\n<td valign=\"top\" width=\"244\">SELECT SalesOrderNumber<\/p>\n<p>FROM dbo.FactInternetSales<\/p>\n<p>WHERE EXISTS (SELECT 1);<\/td>\n<td valign=\"top\" width=\"87\">1,000,000<\/td>\n<td valign=\"top\" width=\"132\">100%<\/td>\n<\/tr>\n<tr>\n<td valign=\"top\" width=\"244\">SELECT SalesOrderNumber<\/p>\n<p>FROM dbo.FactInternetSales<\/p>\n<p>WHERE NOT EXISTS (SELECT 1);<\/td>\n<td valign=\"top\" width=\"87\">0<\/td>\n<td valign=\"top\" width=\"132\">0%<\/td>\n<\/tr>\n<tr>\n<td valign=\"top\" width=\"244\">SELECT SalesOrderNumber<\/p>\n<p>FROM dbo.FactInternetSales<\/p>\n<p>WHERE ProductKey IS NOT NULL;<\/td>\n<td valign=\"top\" width=\"87\">1,000,000<\/td>\n<td valign=\"top\" width=\"132\">100%<\/td>\n<\/tr>\n<tr>\n<td valign=\"top\" width=\"244\">SELECT SalesOrderNumber<\/p>\n<p>FROM dbo.FactInternetSales<\/p>\n<p>WHERE ProductKey IS NULL;<\/td>\n<td valign=\"top\" width=\"87\">0<\/td>\n<td valign=\"top\" width=\"132\">0%<\/td>\n<\/tr>\n<tr>\n<td valign=\"top\" width=\"244\">ALTER TABLE dbo.FactInternetSales<\/p>\n<p>ALTER COLUMN ProductKey int NULL;<\/p>\n<p>SELECT SalesOrderNumber<\/p>\n<p>FROM dbo.FactInternetSales<\/p>\n<p>WHERE ProductKey IS NOT NULL;<\/td>\n<td valign=\"top\" width=\"87\">968,377<\/td>\n<td valign=\"top\" width=\"132\">96.8377%<\/td>\n<\/tr>\n<tr>\n<td valign=\"top\" width=\"244\">ALTER TABLE dbo.FactInternetSales<\/p>\n<p>ALTER COLUMN ProductKey int NULL;<\/p>\n<p>SELECT SalesOrderNumber<\/p>\n<p>FROM dbo.FactInternetSales<\/p>\n<p>WHERE ProductKey IS NULL;<\/td>\n<td valign=\"top\" width=\"87\">31,622.8<\/td>\n<td valign=\"top\" width=\"132\">3.16228%<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<p>There are numerous other scenarios I could have put in this table, but you probably get the point by now that the search conditions help define the which \u201cguess\u201d calculation is used in absence of statistics.\u00a0 When there are additional details beyond table cardinality, such as the nullability of a column or constraints, SQL Server can often leverage it accordingly.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>Let\u2019s say you have a heap table with 1,000,000 rows in it.\u00a0 Let\u2019s also say that your automatic creation of statistics are disabled, as well as updates to the statistics (and in this scenario, there are NO existing indexes or statistics). What kind of selectivity guess would the optimizer make for a query like the [&hellip;]<\/p>\n","protected":false},"author":7,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[42,28],"tags":[],"class_list":["post-495","post","type-post","status-publish","format-standard","hentry","category-cardinality-estimation","category-performance"],"yoast_head":"<!-- This site is optimized with the Yoast SEO plugin v21.9.1 - https:\/\/yoast.com\/wordpress\/plugins\/seo\/ -->\n<title>Selectivity Guesses in absence of Statistics - Joe Sack<\/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\/joe\/selectivity-guesses-in-absence-of-statistics\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Selectivity Guesses in absence of Statistics - Joe Sack\" \/>\n<meta property=\"og:description\" content=\"Let\u2019s say you have a heap table with 1,000,000 rows in it.\u00a0 Let\u2019s also say that your automatic creation of statistics are disabled, as well as updates to the statistics (and in this scenario, there are NO existing indexes or statistics). What kind of selectivity guess would the optimizer make for a query like the [&hellip;]\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.sqlskills.com\/blogs\/joe\/selectivity-guesses-in-absence-of-statistics\/\" \/>\n<meta property=\"og:site_name\" content=\"Joe Sack\" \/>\n<meta property=\"article:published_time\" content=\"2012-05-10T04:43:37+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2013-12-30T03:18:30+00:00\" \/>\n<meta property=\"og:image\" content=\"https:\/\/www.sqlskills.com\/blogs\/joe\/wp-content\/uploads\/windows-live-writer\/query-guesses\/5bc397a7\/snaghtml1904f4f_thumb.png\" \/>\n<meta name=\"author\" content=\"Joseph Sack\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Joseph Sack\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"3 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"WebPage\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/joe\/selectivity-guesses-in-absence-of-statistics\/\",\"url\":\"https:\/\/www.sqlskills.com\/blogs\/joe\/selectivity-guesses-in-absence-of-statistics\/\",\"name\":\"Selectivity Guesses in absence of Statistics - Joe Sack\",\"isPartOf\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/joe\/#website\"},\"datePublished\":\"2012-05-10T04:43:37+00:00\",\"dateModified\":\"2013-12-30T03:18:30+00:00\",\"author\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/joe\/#\/schema\/person\/533eb0113a15fb5a6e8067a49e4ae648\"},\"breadcrumb\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/joe\/selectivity-guesses-in-absence-of-statistics\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.sqlskills.com\/blogs\/joe\/selectivity-guesses-in-absence-of-statistics\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/joe\/selectivity-guesses-in-absence-of-statistics\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/www.sqlskills.com\/blogs\/joe\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Cardinality Estimation\",\"item\":\"https:\/\/www.sqlskills.com\/blogs\/joe\/category\/cardinality-estimation\/\"},{\"@type\":\"ListItem\",\"position\":3,\"name\":\"Selectivity Guesses in absence of Statistics\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/joe\/#website\",\"url\":\"https:\/\/www.sqlskills.com\/blogs\/joe\/\",\"name\":\"Joe Sack\",\"description\":\"SQL Server Performance Tuning, High Availability and Disaster Recovery Blog\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/www.sqlskills.com\/blogs\/joe\/?s={search_term_string}\"},\"query-input\":\"required name=search_term_string\"}],\"inLanguage\":\"en-US\"},{\"@type\":\"Person\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/joe\/#\/schema\/person\/533eb0113a15fb5a6e8067a49e4ae648\",\"name\":\"Joseph Sack\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/joe\/#\/schema\/person\/image\/\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/a4b39a7719a6bfff1add3ec00527810734579ee114d6d983e8e68f937b77be96?s=96&d=mm&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/a4b39a7719a6bfff1add3ec00527810734579ee114d6d983e8e68f937b77be96?s=96&d=mm&r=g\",\"caption\":\"Joseph Sack\"},\"description\":\"Joe Sack is a Principal Consultant with SQLskills. He has worked as a SQL Server professional since 1997 and has supported and developed for SQL Server environments in financial services, IT consulting, manufacturing, retail and the real estate industry. Prior to joining SQLskills he worked at Microsoft as a Premier Field Engineer supporting very large enterprise customer environments. He was responsible for providing deep SQL Server advisory services, training, troubleshooting and ongoing solutions guidance. His areas of expertise include performance tuning, scalability, T-SQL development and high-availability. In 2006 Joe earned the \u201cMicrosoft Certified Master: SQL Server 2005\u201d certification and in 2008 he earned the \u201cMicrosoft Certified Master: SQL Server 2008\u201d certification. In 2009 he took over responsibility for the entire SQL Server Microsoft Certified Master program and held that post until 2011. He was given the SQL Server MVP award in 2013.\",\"sameAs\":[\"http:\/\/3.209.169.194\/blogs\/joe\",\"https:\/\/twitter.com\/https:\/\/twitter.com\/josephsack\"],\"url\":\"https:\/\/www.sqlskills.com\/blogs\/joe\/author\/joe\/\"}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"Selectivity Guesses in absence of Statistics - Joe Sack","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\/joe\/selectivity-guesses-in-absence-of-statistics\/","og_locale":"en_US","og_type":"article","og_title":"Selectivity Guesses in absence of Statistics - Joe Sack","og_description":"Let\u2019s say you have a heap table with 1,000,000 rows in it.\u00a0 Let\u2019s also say that your automatic creation of statistics are disabled, as well as updates to the statistics (and in this scenario, there are NO existing indexes or statistics). What kind of selectivity guess would the optimizer make for a query like the [&hellip;]","og_url":"https:\/\/www.sqlskills.com\/blogs\/joe\/selectivity-guesses-in-absence-of-statistics\/","og_site_name":"Joe Sack","article_published_time":"2012-05-10T04:43:37+00:00","article_modified_time":"2013-12-30T03:18:30+00:00","og_image":[{"url":"https:\/\/www.sqlskills.com\/blogs\/joe\/wp-content\/uploads\/windows-live-writer\/query-guesses\/5bc397a7\/snaghtml1904f4f_thumb.png"}],"author":"Joseph Sack","twitter_misc":{"Written by":"Joseph Sack","Est. reading time":"3 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"WebPage","@id":"https:\/\/www.sqlskills.com\/blogs\/joe\/selectivity-guesses-in-absence-of-statistics\/","url":"https:\/\/www.sqlskills.com\/blogs\/joe\/selectivity-guesses-in-absence-of-statistics\/","name":"Selectivity Guesses in absence of Statistics - Joe Sack","isPartOf":{"@id":"https:\/\/www.sqlskills.com\/blogs\/joe\/#website"},"datePublished":"2012-05-10T04:43:37+00:00","dateModified":"2013-12-30T03:18:30+00:00","author":{"@id":"https:\/\/www.sqlskills.com\/blogs\/joe\/#\/schema\/person\/533eb0113a15fb5a6e8067a49e4ae648"},"breadcrumb":{"@id":"https:\/\/www.sqlskills.com\/blogs\/joe\/selectivity-guesses-in-absence-of-statistics\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.sqlskills.com\/blogs\/joe\/selectivity-guesses-in-absence-of-statistics\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.sqlskills.com\/blogs\/joe\/selectivity-guesses-in-absence-of-statistics\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/www.sqlskills.com\/blogs\/joe\/"},{"@type":"ListItem","position":2,"name":"Cardinality Estimation","item":"https:\/\/www.sqlskills.com\/blogs\/joe\/category\/cardinality-estimation\/"},{"@type":"ListItem","position":3,"name":"Selectivity Guesses in absence of Statistics"}]},{"@type":"WebSite","@id":"https:\/\/www.sqlskills.com\/blogs\/joe\/#website","url":"https:\/\/www.sqlskills.com\/blogs\/joe\/","name":"Joe Sack","description":"SQL Server Performance Tuning, High Availability and Disaster Recovery Blog","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/www.sqlskills.com\/blogs\/joe\/?s={search_term_string}"},"query-input":"required name=search_term_string"}],"inLanguage":"en-US"},{"@type":"Person","@id":"https:\/\/www.sqlskills.com\/blogs\/joe\/#\/schema\/person\/533eb0113a15fb5a6e8067a49e4ae648","name":"Joseph Sack","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/www.sqlskills.com\/blogs\/joe\/#\/schema\/person\/image\/","url":"https:\/\/secure.gravatar.com\/avatar\/a4b39a7719a6bfff1add3ec00527810734579ee114d6d983e8e68f937b77be96?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/a4b39a7719a6bfff1add3ec00527810734579ee114d6d983e8e68f937b77be96?s=96&d=mm&r=g","caption":"Joseph Sack"},"description":"Joe Sack is a Principal Consultant with SQLskills. He has worked as a SQL Server professional since 1997 and has supported and developed for SQL Server environments in financial services, IT consulting, manufacturing, retail and the real estate industry. Prior to joining SQLskills he worked at Microsoft as a Premier Field Engineer supporting very large enterprise customer environments. He was responsible for providing deep SQL Server advisory services, training, troubleshooting and ongoing solutions guidance. His areas of expertise include performance tuning, scalability, T-SQL development and high-availability. In 2006 Joe earned the \u201cMicrosoft Certified Master: SQL Server 2005\u201d certification and in 2008 he earned the \u201cMicrosoft Certified Master: SQL Server 2008\u201d certification. In 2009 he took over responsibility for the entire SQL Server Microsoft Certified Master program and held that post until 2011. He was given the SQL Server MVP award in 2013.","sameAs":["http:\/\/3.209.169.194\/blogs\/joe","https:\/\/twitter.com\/https:\/\/twitter.com\/josephsack"],"url":"https:\/\/www.sqlskills.com\/blogs\/joe\/author\/joe\/"}]}},"_links":{"self":[{"href":"https:\/\/www.sqlskills.com\/blogs\/joe\/wp-json\/wp\/v2\/posts\/495","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.sqlskills.com\/blogs\/joe\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.sqlskills.com\/blogs\/joe\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/joe\/wp-json\/wp\/v2\/users\/7"}],"replies":[{"embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/joe\/wp-json\/wp\/v2\/comments?post=495"}],"version-history":[{"count":0,"href":"https:\/\/www.sqlskills.com\/blogs\/joe\/wp-json\/wp\/v2\/posts\/495\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.sqlskills.com\/blogs\/joe\/wp-json\/wp\/v2\/media?parent=495"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/joe\/wp-json\/wp\/v2\/categories?post=495"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/joe\/wp-json\/wp\/v2\/tags?post=495"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}