{"id":546,"date":"2009-03-09T08:28:00","date_gmt":"2009-03-09T08:28:00","guid":{"rendered":"\/blogs\/kimberly\/post\/Tipping-Point-Queries-More-Questions-To-Really-Test-You!.aspx"},"modified":"2013-01-11T23:11:19","modified_gmt":"2013-01-12T07:11:19","slug":"tipping-point-queries-more-questions-to-really-test-you","status":"publish","type":"post","link":"https:\/\/www.sqlskills.com\/blogs\/kimberly\/tipping-point-queries-more-questions-to-really-test-you\/","title":{"rendered":"Tipping Point Queries &#8211; More Questions To Really Test You!"},"content":{"rendered":"<p>\nOK, so this is interesting. I&#39;ve got a few answers to my last survey (<a href=\"https:\/\/www.sqlskills.com\/blogs\/kimberly\/why-arent-those-nonclustered-indexes-being-used\/\" target=\"_blank\">Tipping Point Query #1<\/a>) and well, there&#39;s a good mix of answers (and, yes, some are correct! ;)). <em>Be sure to go back and review that last post so that you can evaluate it and these two tipping point questions completely. <\/em>So, now I want to see if people really know the basis of &quot;the tipping point&quot;.\n<\/p>\n<p>\nTry these two:\n<\/p>\n<p>\n<strong>Tipping Point Query #2 <\/strong>\n<\/p>\n<p>\nTable1 (t1) has 1 million rows at 100 rows per page. The table has 10,000 pages. A nonclustered index exists (on name) but it does not cover the query. At what percentage (of the table) is this nonclustered index no longer selective enough to use:\n<\/p>\n<p><script>\nem24_survey_x = \"97592c31-6ab5-4511-909c-8f189e8b13b9\";\nem24_survey_width = \"255\";\nem24_survey_height = \"268\";\nem24_styles = \".em24_s {border:solid 1px #626A84; width:250px;} .em24_s td {font-size:12px;} .em24_q {background:#798BC6; color:#ffffff;} .em24_ai0, .em24_at0 {background:#E7F3FF; border-top:1px solid #B3C7D9;} .em24_ai1, .em24_at1 {background:#E3F7DE; border-top:1px solid #B3C7D9;} .em24_v {background:#798BC6}\";<\/script><br \/>\n<script src=\"http:\/\/my.surveypopups.com\/show\/si.js\" type=\"text\/javascript\">\n<\/script><\/p>\n<p>\n<strong>Tipping Point Query #3 <\/strong>\n<\/p>\n<p>\nTable2 (t2) has 1 million rows at 2 rows per page. The table has 500,000 pages. A nonclustered index exists (on name) but it does not cover the query. At what percentage (of the table) is this nonclustered index no longer selective enough to use:\n<\/p>\n<p><script>\nem24_survey_x = \"b6d6ebd3-4ed5-475d-88c4-58a08bad438a\";\nem24_survey_width = \"255\";\nem24_survey_height = \"268\";\nem24_styles = \".em24_s {border:solid 1px #626A84; width:250px;} .em24_s td {font-size:12px;} .em24_q {background:#798BC6; color:#ffffff;} .em24_ai0, .em24_at0 {background:#E7F3FF; border-top:1px solid #B3C7D9;} .em24_ai1, .em24_at1 {background:#E3F7DE; border-top:1px solid #B3C7D9;} .em24_v {background:#798BC6}\";<\/script><br \/>\n<script src=\"http:\/\/my.surveypopups.com\/show\/si.js\" type=\"text\/javascript\">\n<\/script><\/p>\n<p>\nOK, so I&#39;d really love to see quite a few responses to these *3* &quot;tipping point&quot; questions. I PROMISE to do a nice long (<em>and detailed<\/em>) post for what is the actual tipping point AND the answers to all three of these questions. I&#39;ll explain the math as well as how you can generalize &quot;what is selective enough&quot; so that you can better create your nonclustered indexes!!!\n<\/p>\n<p>\nThanks for reading &#8211; and responding to these <strong><em>brain teasers<\/em><\/strong>!!\n<\/p>\n<p>\nCheers, <br \/>\nkt\n<\/p>\n<p>\nPS &#8211; It&#39;s snowing here (ah&#8230;again)&#8230; maybe I&#39;ll spend the day creating brain teasers??! Do you guys like this kind of a post? (well, I suppose you won&#39;t really know until I post the answer part of it&#8230; but, just in general??). I think it&#39;s pretty cool. But, don&#39;t worry, I won&#39;t (nor will Paul) make all of my posts surveys. But, I think this is a really good one. I&#39;m anxious to see if the asnwers come in correctly for these two as well! Have at it!<\/p>\n","protected":false},"excerpt":{"rendered":"<p>OK, so this is interesting. I&#39;ve got a few answers to my last survey (Tipping Point Query #1) and well, there&#39;s a good mix of answers (and, yes, some are correct! ;)). Be sure to go back and review that last post so that you can evaluate it and these two tipping point questions completely. [&hellip;]<\/p>\n","protected":false},"author":7,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[36,48,65,66,73,77],"tags":[],"class_list":["post-546","post","type-post","status-publish","format-standard","hentry","category-indexes","category-nonclustered-indexes","category-sql-server-2005","category-sql-server-2008","category-survey","category-the-tipping-point"],"_links":{"self":[{"href":"https:\/\/www.sqlskills.com\/blogs\/kimberly\/wp-json\/wp\/v2\/posts\/546","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.sqlskills.com\/blogs\/kimberly\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.sqlskills.com\/blogs\/kimberly\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/kimberly\/wp-json\/wp\/v2\/users\/7"}],"replies":[{"embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/kimberly\/wp-json\/wp\/v2\/comments?post=546"}],"version-history":[{"count":0,"href":"https:\/\/www.sqlskills.com\/blogs\/kimberly\/wp-json\/wp\/v2\/posts\/546\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.sqlskills.com\/blogs\/kimberly\/wp-json\/wp\/v2\/media?parent=546"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/kimberly\/wp-json\/wp\/v2\/categories?post=546"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/kimberly\/wp-json\/wp\/v2\/tags?post=546"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}