{"id":2936,"date":"2021-11-11T15:31:33","date_gmt":"2021-11-11T23:31:33","guid":{"rendered":"https:\/\/www.sqlskills.com\/blogs\/kimberly\/?p=2936"},"modified":"2021-11-11T15:50:19","modified_gmt":"2021-11-11T23:50:19","slug":"use-these-updates-to-sqlskills-index-procedures","status":"publish","type":"post","link":"https:\/\/www.sqlskills.com\/blogs\/kimberly\/use-these-updates-to-sqlskills-index-procedures\/","title":{"rendered":"USE THESE: Updates to SQLskills Index Procedures"},"content":{"rendered":"\t\t<div data-elementor-type=\"wp-post\" data-elementor-id=\"2936\" class=\"elementor elementor-2936\">\n\t\t\t\t\t\t<div class=\"elementor-inner\">\n\t\t\t\t<div class=\"elementor-section-wrap\">\n\t\t\t\t\t\t\t\t\t<section class=\"elementor-section elementor-top-section elementor-element elementor-element-591acc27 elementor-section-boxed elementor-section-height-default elementor-section-height-default\" data-id=\"591acc27\" data-element_type=\"section\">\n\t\t\t\t\t\t<div class=\"elementor-container elementor-column-gap-default\">\n\t\t\t\t\t\t\t<div class=\"elementor-row\">\n\t\t\t\t\t<div class=\"elementor-column elementor-col-100 elementor-top-column elementor-element elementor-element-5801ba04\" data-id=\"5801ba04\" data-element_type=\"column\">\n\t\t\t<div class=\"elementor-column-wrap elementor-element-populated\">\n\t\t\t\t\t\t\t<div class=\"elementor-widget-wrap\">\n\t\t\t\t\t\t<div class=\"elementor-element elementor-element-770ab12e elementor-widget elementor-widget-text-editor\" data-id=\"770ab12e\" data-element_type=\"widget\" data-widget_type=\"text-editor.default\">\n\t\t\t\t<div class=\"elementor-widget-container\">\n\t\t\t\t\t\t\t\t<div class=\"elementor-text-editor elementor-clearfix\">\n\t\t\t\t<p><!-- wp:paragraph --><\/p>\n<p>OK, I&#8217;ve been meaning to update these for quite some time&#8230; Randolph West tweaked a few things a few months ago (they&#8217;re so awesome!) and I&#8217;d been wanting to consolidate all of the madness of having to have multiple scripts for &#8220;normalizing&#8221; the INCLUDE list as the order there doesn&#8217;t matter (but, it is &#8220;preserved&#8221; in the internal tables so for me to determine REAL duplicates I need to order this for comparison).<\/p>\n<p><!-- \/wp:paragraph --><\/p>\n<p><!-- wp:paragraph --><\/p>\n<p>Anyway, I&#8217;ve finally done all of this. And, I removed the OLD &#8220;2008&#8221; portion of the name as they work on all releases from 2008 through 2019. I&#8217;ve tested most of this but I suppose I could have missed something. So, definitely let me know if you find anything!<\/p>\n<p><!-- \/wp:paragraph --><\/p>\n<p><!-- wp:paragraph --><\/p>\n<p>In the end, there are now ONLY THREE index related procedures (create them in this order):<\/p>\n<p><!-- \/wp:paragraph --><\/p>\n<p><!-- wp:paragraph --><\/p>\n<ol>\n<li><span style=\"color: var( --e-global-color-text ); font-family: var( --e-global-typography-text-font-family ), Sans-serif; font-weight: var( --e-global-typography-text-font-weight );\">sp_SQLskills_exposecolsinindexlevels<\/span><\/li>\n<li><span style=\"color: var( --e-global-color-text ); font-family: var( --e-global-typography-text-font-family ), Sans-serif; font-weight: var( --e-global-typography-text-font-weight );\">sp_SQLskills_helpindex (this one uses sp_SQLskills_exposecolsinindexlevels)<\/span><\/li>\n<li><span style=\"color: var( --e-global-color-text ); font-family: var( --e-global-typography-text-font-family ), Sans-serif; font-weight: var( --e-global-typography-text-font-weight );\">sp_SQLskills finddupes (this one uses sp_helpindex)<\/span><\/li>\n<\/ol>\n<p><!-- \/wp:paragraph --><\/p>\n<p><!-- wp:paragraph --><\/p>\n<p><!-- \/wp:paragraph --><\/p>\n<p><!-- wp:paragraph --><\/p>\n<p><!-- \/wp:paragraph --><\/p>\n<p><!-- wp:paragraph --><\/p>\n<p>And, because I know a few of you have the previous scripts installed, these updated scripts will drop the old versions &#8211; if they exist. These procedures will be removed:<\/p>\n<ul>\n<li>sp_SQLskills_ExposeColsInIndexLevels_INCLUDE_UNORDERED<\/li>\n<li>sp_SQLskills_SQL2008_finddupes_helpindex<\/li>\n<li>sp_SQLskills_SQL2008_finddupes<\/li>\n<\/ul>\n<div>IMPORTANT: If you attended my PASS Session &#8220;Indexing for Performance,&#8221; I&#8217;ve included these updated scripts in the SQLskillsIndexProcs project and I have sent them in for PASS to post on their site AND I&#8217;ve upload them to our demos page <a href=\"https:\/\/www.sqlskills.com\/sql-server-resources\/sql-server-demos\/\" target=\"_blank\" rel=\"noopener\">here<\/a>.&nbsp;<\/div>\n<h2>How do you use these index scripts?<\/h2>\n<h3>sp_SQLskills_helpindex<\/h3>\n<p>sp_SQLskills_helpindex is for adding \/ exposing more details related to you indexes. All you need to do is pass in a table name.<\/p>\n<pre>EXEC [sp_sqlskills_helpindex] '[dbo].[member]';<br>go<\/pre>\n<p>Or, for those of that are lazy (myself included)&#8230;<\/p>\n<pre>EXEC sp_sqlskills_helpindex member<br>go<\/pre>\n<h3>sp_SQLskills_finddupes<\/h3>\n<p>sp_SQLskills_finddupes is for determining your duplicate indexes on a table, or within the entire database.<\/p>\n<pre>EXEC [sp_sqlskills_finddupes] '[dbo].[member]';<br>go<\/pre>\n<p>Or, for those of that are lazy (myself included)&#8230;<\/p>\n<pre>EXEC sp_sqlskills_finddupes member<br>go<\/pre>\n<p>And, the BEST one is this as it looks at the entire database:<\/p>\n<pre>EXEC sp_sqlskills_finddupes<br>go<\/pre>\n<p>SO, here&#8217;s the zip <a href=\"https:\/\/www.sqlskills.com\/blogs\/kimberly\/wp-content\/uploads\/2021\/11\/20211111_sqlskillsindexprocs.zip\" target=\"_blank\" rel=\"noopener\">20211111_SQLskillsIndexProcs<\/a> that has all 3 SQL scripts.<\/p>\n<p>Have fun!<br>-k<\/p>\n<p><\/p>\n<p><!-- \/wp:paragraph --><\/p>\t\t\t\t\t<\/div>\n\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t\t\t<\/div>\n\t\t\t\t\t<\/div>\n\t\t<\/div>\n\t\t\t\t\t\t\t\t<\/div>\n\t\t\t\t\t<\/div>\n\t\t<\/section>\n\t\t\t\t\t\t\t\t\t<\/div>\n\t\t\t<\/div>\n\t\t\t\t\t<\/div>\n\t\t","protected":false},"excerpt":{"rendered":"<p>OK, I&#8217;ve been meaning to update these for quite some time&#8230; Randolph West tweaked a few things a few months ago (they&#8217;re so awesome!) and I&#8217;d been wanting to consolidate all of the madness of having to have multiple scripts for &#8220;normalizing&#8221; the INCLUDE list as the order there doesn&#8217;t matter (but, it is &#8220;preserved&#8221; [&hellip;]<\/p>\n","protected":false},"author":7,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[19,20,36,63,79],"tags":[],"class_list":["post-2936","post","type-post","status-publish","format-standard","hentry","category-conference-resourcesqa","category-conferences","category-indexes","category-sp_helpindex-rewrites","category-tools"],"_links":{"self":[{"href":"https:\/\/www.sqlskills.com\/blogs\/kimberly\/wp-json\/wp\/v2\/posts\/2936","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=2936"}],"version-history":[{"count":0,"href":"https:\/\/www.sqlskills.com\/blogs\/kimberly\/wp-json\/wp\/v2\/posts\/2936\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.sqlskills.com\/blogs\/kimberly\/wp-json\/wp\/v2\/media?parent=2936"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/kimberly\/wp-json\/wp\/v2\/categories?post=2936"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/kimberly\/wp-json\/wp\/v2\/tags?post=2936"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}