{"id":563,"date":"2012-01-10T00:00:11","date_gmt":"2012-01-10T05:00:11","guid":{"rendered":"http:\/\/sqlity.net\/en\/?p=563"},"modified":"2014-11-13T14:02:30","modified_gmt":"2014-11-13T19:02:30","slug":"index-misconceptions-tsql-tuesday-026-second-chances","status":"publish","type":"post","link":"https:\/\/sqlity.net\/en\/563\/index-misconceptions-tsql-tuesday-026-second-chances\/","title":{"rendered":"Index Misconceptions [TSQL Tuesday #026 &#8211; Second Chances]"},"content":{"rendered":"<style type=\"text\/css\">\n<!--\np {margin-top:8px;}\n-->\n<\/style>\n<div>\n<p><a href=\"http:\/\/davidbrycehoward.com\/archive\/2012\/01\/tsql-tuesday-026-second-chances\/\"><img loading=\"lazy\" decoding=\"async\" height=\"132\" border=\"0\" hspace=\"9\" width=\"131\" alt=\"T-SQL Tuesday #26\" src=\"http:\/\/images.sqlity.net\/SqlTuesday.png\" \/><\/a><\/p>\n<p>T-SQL Tuesday #26 is hosted by David Howard (<a href=\"http:\/\/davidbrycehoward.com\/\">blog<\/a>|<a href=\"https:\/\/twitter.com\/#!\/DaveH0ward\">twitter<\/a>). This month\u2019s topic is \u201c<a href=\"http:\/\/davidbrycehoward.com\/archive\/2012\/01\/tsql-tuesday-026-second-chances\/\">Second Chances<\/a>\u201d.<\/p>\n<h2>Index Misconceptions<\/h2>\n<p>\nThe topic this month is \"Second Chances\" which means that we can write about anything, we would have liked to write about before but didn't get to. Well, not really anything, but any of the 25 previos T-SQL Tuesday topics. I picked two topics: <a href=\"http:\/\/michaeljswart.com\/2010\/09\/invitation-to-participate-in-t-sql-tuesday-10-indexes\/\">Indexes<\/a> by <a href=\"http:\/\/michaeljswart.com\/\">Michael Swart<\/a> and <a href=\"http:\/\/sankarreddy.com\/2010\/10\/invitation-to-participate-in-t-sql-tuesday-11-misconceptions-in-sql-server\/\">Misconceptions in SQL Server<\/a> by <a href=\"http:\/\/sankarreddy.com\/\">Sankar Reddy<\/a>.\n<\/p>\n<h3>Misconception: SQL Server Indexes are binary trees<\/h3>\n<p>I keep running into articles that claim that SQL Server internally uses a binary tree to build it's indexes. That however is incorrect and I would like to use this opportunity to clear things up a little.\n<\/p>\n<p>\nSQL Server stores its tables (to be exact: table partitions) in a format that is called a HoBT. HoBT stands for \"Heap or B-Tree\". A Heap is used for tables without a clustered index. A B-Tree is used for all clustered and nonclustered indexes. If you lock up the word <a href=\"http:\/\/en.wikipedia.org\/wiki\/B-tree\">B-Tree on Wikipedia<\/a>, the first sentence states: \"Not to be confused with <a href=\"http:\/\/en.wikipedia.org\/wiki\/Binary_tree\" title=\"Binary tree\">Binary tree<\/a>.\"\n<\/p>\n<p>\nThe B in B-Tree is often said to stand fo \"Balanced\". However, when Rudolf Bayer and Ed McCreight at Boeing invented the B-Tree in 1971, they did not specify the meaning of the B at all.<br \/>\nSpeculations include Balanced, Bushy, Bayer and Boeing (<a href=\"http:\/\/en.wikipedia.org\/wiki\/B-tree#Etymology_unknown\">see again Wikipedia<\/a>).\n<\/p>\n<p>\nSQL Server is not using a plain B-Tree but instead a variation called a <a href=\"http:\/\/en.wikipedia.org\/wiki\/B%2B_tree\">B+Tree<\/a>. The main difference to a B-Tree is, that in a B+Tree the actual data is only stored in the leaf nodes with all other nodes containing only key values. In B-Trees the data is distributed over all levels.\n<\/p>\n<p>\nSo, let's take a look under the covers to see the structure for ourselves. First lets create a table to play with:\n<\/p>\n<div>\n[sql]\nCREATE TABLE dbo.IdxTst1<br \/>\n    (<br \/>\n      Id INT IDENTITY(1, 1) ,<br \/>\n      V1 CHAR(795) ,<br \/>\n      F1 CHAR(7254) ,<br \/>\n      CONSTRAINT PK_IdxTst1 PRIMARY KEY CLUSTERED( Id, V1 )<br \/>\n    ) ;<br \/>\n[\/sql]\n<\/div>\n<p> The table has a 4 byte (INT) identity column and two fixed length CHAR columns. The Clustered Index key contains the Id and the 795 byte, a total of 799 bytes. The full row contains an additional 7254 bytes for a total row data size of 8053 bytes. Each row when stored contains some meta information and for this table this additional information is 7 bytes per row, which brings us to a total of 8060 bytes for each row. This is the maximum number of bytes that one row is allowed to take (we are excluding LOB data from this exercise). This row size will make sure that there is always only one row per page.\n<\/p>\n<p>\n  To look at the structure of the table we are going to use the <a href=\"http:\/\/msdn.microsoft.com\/en-us\/library\/ms188917.aspx\">sys.dm_db_index_physical_stats<\/a> dmf:\n<\/p>\n<div>\n[sql]\nSELECT index_type_desc,Alloc_unit_type_desc,index_depth,index_level,page_count,record_count,avg_record_size_in_bytes<br \/>\n  FROM sys.dm_db_index_physical_stats(DB_ID(),OBJECT_ID('dbo.IdxTst1'),NULL,NULL,'DETAILED');<br \/>\n[\/sql]\n<\/div>\n<p>\nThis query returns one row for every level of every B+Tree for the dbo.IdxTst1 table. As the table is not partitioned and contains only one index (the clustered index), the query in our case returns only information about this one index.\n<\/p>\n<p>\nAfter inserting one row into the table the query returns the following result set:\n<\/p>\n<table>\n<thead>\n<tr>\n<th class=\"index_type_desc-cell\">index_type_desc<\/th>\n<th class=\"Alloc_unit_type_desc-cell\">Alloc_unit_type_desc<\/th>\n<th class=\"index_depth-cell\">index_depth<\/th>\n<th class=\"index_level-cell\">index_level<\/th>\n<th class=\"page_count-cell\">page_count<\/th>\n<th class=\"record_count-cell\">record_count<\/th>\n<th class=\"avg_record_size_in_bytes-cell\">avg_record_size_in_bytes<\/th>\n<\/tr>\n<\/thead>\n<tbody>\n<tr class=\"lastRow\">\n<td class=\"index_type_desc-cell\">CLUSTERED INDEX<\/td>\n<td class=\"Alloc_unit_type_desc-cell\">IN_ROW_DATA<\/td>\n<td class=\"index_depth-cell\">1<\/td>\n<td class=\"index_level-cell\">0<\/td>\n<td class=\"page_count-cell\">1<\/td>\n<td class=\"record_count-cell\">1<\/td>\n<td class=\"avg_record_size_in_bytes-cell\">8060<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<p>\nThe B+Tree has so far only one level (index_level = 0), contains 1 node (page_count = 1) and 1 record within that node. The record has the expected size of 8060 bytes.\n<\/p>\n<p>\nTo insert a second row SQL Server needs to create a new page (node), as each page can hold only one row. With now two data pages we also need a new root page that links to the data pages:\n<\/p>\n<table>\n<thead>\n<tr>\n<th class=\"index_type_desc-cell\">index_type_desc<\/th>\n<th class=\"Alloc_unit_type_desc-cell\">Alloc_unit_type_desc<\/th>\n<th class=\"index_depth-cell\">index_depth<\/th>\n<th class=\"index_level-cell\">index_level<\/th>\n<th class=\"page_count-cell\">page_count<\/th>\n<th class=\"record_count-cell\">record_count<\/th>\n<th class=\"avg_record_size_in_bytes-cell\">avg_record_size_in_bytes<\/th>\n<\/tr>\n<\/thead>\n<tbody>\n<tr class=\"firstRow\">\n<td class=\"index_type_desc-cell\">CLUSTERED INDEX<\/td>\n<td class=\"Alloc_unit_type_desc-cell\">IN_ROW_DATA<\/td>\n<td class=\"index_depth-cell\">2<\/td>\n<td class=\"index_level-cell\">0<\/td>\n<td class=\"page_count-cell\">2<\/td>\n<td class=\"record_count-cell\">2<\/td>\n<td class=\"avg_record_size_in_bytes-cell\">8060<\/td>\n<\/tr>\n<tr class=\"lastRow\">\n<td class=\"index_type_desc-cell\">CLUSTERED INDEX<\/td>\n<td class=\"Alloc_unit_type_desc-cell\">IN_ROW_DATA<\/td>\n<td class=\"index_depth-cell\">2<\/td>\n<td class=\"index_level-cell\">1<\/td>\n<td class=\"page_count-cell\">1<\/td>\n<td class=\"record_count-cell\">2<\/td>\n<td class=\"avg_record_size_in_bytes-cell\">806<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<p>\nAs expected, the B+Tree has now two levels: Index_level 0 with 2 pages holding one row of 8060 bytes each, and index_level 1 for the root page which holds two records of 806 bytes each &ndash; one for each level 0 page. The 806 bytes contain the 799 byte key value plus the pointer to the data page for this key value.\n<\/p>\n<p>\nWith a key storage size of 806 a page can hold 10 different values, so lets insert an other 8 rows to check:\n<\/p>\n<table>\n<thead>\n<tr>\n<th class=\"index_type_desc-cell\">index_type_desc<\/th>\n<th class=\"Alloc_unit_type_desc-cell\">Alloc_unit_type_desc<\/th>\n<th class=\"index_depth-cell\">index_depth<\/th>\n<th class=\"index_level-cell\">index_level<\/th>\n<th class=\"page_count-cell\">page_count<\/th>\n<th class=\"record_count-cell\">record_count<\/th>\n<th class=\"avg_record_size_in_bytes-cell\">avg_record_size_in_bytes<\/th>\n<\/tr>\n<\/thead>\n<tbody>\n<tr class=\"firstRow\">\n<td class=\"index_type_desc-cell\">CLUSTERED INDEX<\/td>\n<td class=\"Alloc_unit_type_desc-cell\">IN_ROW_DATA<\/td>\n<td class=\"index_depth-cell\">2<\/td>\n<td class=\"index_level-cell\">0<\/td>\n<td class=\"page_count-cell\">10<\/td>\n<td class=\"record_count-cell\">10<\/td>\n<td class=\"avg_record_size_in_bytes-cell\">8060<\/td>\n<\/tr>\n<tr class=\"lastRow\">\n<td class=\"index_type_desc-cell\">CLUSTERED INDEX<\/td>\n<td class=\"Alloc_unit_type_desc-cell\">IN_ROW_DATA<\/td>\n<td class=\"index_depth-cell\">2<\/td>\n<td class=\"index_level-cell\">1<\/td>\n<td class=\"page_count-cell\">1<\/td>\n<td class=\"record_count-cell\">10<\/td>\n<td class=\"avg_record_size_in_bytes-cell\">806<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<p>\nAnd one more to force an additional page on index_level 1:\n<\/p>\n<table>\n<thead>\n<tr>\n<th class=\"index_type_desc-cell\">index_type_desc<\/th>\n<th class=\"Alloc_unit_type_desc-cell\">Alloc_unit_type_desc<\/th>\n<th class=\"index_depth-cell\">index_depth<\/th>\n<th class=\"index_level-cell\">index_level<\/th>\n<th class=\"page_count-cell\">page_count<\/th>\n<th class=\"record_count-cell\">record_count<\/th>\n<th class=\"avg_record_size_in_bytes-cell\">avg_record_size_in_bytes<\/th>\n<\/tr>\n<\/thead>\n<tbody>\n<tr class=\"firstRow\">\n<td class=\"index_type_desc-cell\">CLUSTERED INDEX<\/td>\n<td class=\"Alloc_unit_type_desc-cell\">IN_ROW_DATA<\/td>\n<td class=\"index_depth-cell\">3<\/td>\n<td class=\"index_level-cell\">0<\/td>\n<td class=\"page_count-cell\">11<\/td>\n<td class=\"record_count-cell\">11<\/td>\n<td class=\"avg_record_size_in_bytes-cell\">8060<\/td>\n<\/tr>\n<tr>\n<td class=\"index_type_desc-cell\">CLUSTERED INDEX<\/td>\n<td class=\"Alloc_unit_type_desc-cell\">IN_ROW_DATA<\/td>\n<td class=\"index_depth-cell\">3<\/td>\n<td class=\"index_level-cell\">1<\/td>\n<td class=\"page_count-cell\">2<\/td>\n<td class=\"record_count-cell\">11<\/td>\n<td class=\"avg_record_size_in_bytes-cell\">806<\/td>\n<\/tr>\n<tr class=\"lastRow\">\n<td class=\"index_type_desc-cell\">CLUSTERED INDEX<\/td>\n<td class=\"Alloc_unit_type_desc-cell\">IN_ROW_DATA<\/td>\n<td class=\"index_depth-cell\">3<\/td>\n<td class=\"index_level-cell\">2<\/td>\n<td class=\"page_count-cell\">1<\/td>\n<td class=\"record_count-cell\">2<\/td>\n<td class=\"avg_record_size_in_bytes-cell\">806<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<p>\nAs predicted, we now have two pages on index_level 1 and a new root page on index_level 2. All the data (11 rows) is still stored in index_level 0 in 11 separate pages. All intermediate (and root) pages contain only key values (806 bytes).\n<\/p>\n<p>\nThe root page can hold 10 entries and each index_level 1 page can also hold 10 entries. That should allow us to insert an additional 89 rows into this table without requiring a fourth index level. So let's try it:\n<\/p>\n<table>\n<thead>\n<tr>\n<th class=\"index_type_desc-cell\">index_type_desc<\/th>\n<th class=\"Alloc_unit_type_desc-cell\">Alloc_unit_type_desc<\/th>\n<th class=\"index_depth-cell\">index_depth<\/th>\n<th class=\"index_level-cell\">index_level<\/th>\n<th class=\"page_count-cell\">page_count<\/th>\n<th class=\"record_count-cell\">record_count<\/th>\n<th class=\"avg_record_size_in_bytes-cell\">avg_record_size_in_bytes<\/th>\n<\/tr>\n<\/thead>\n<tbody>\n<tr class=\"firstRow\">\n<td class=\"index_type_desc-cell\">CLUSTERED INDEX<\/td>\n<td class=\"Alloc_unit_type_desc-cell\">IN_ROW_DATA<\/td>\n<td class=\"index_depth-cell\">4<\/td>\n<td class=\"index_level-cell\">0<\/td>\n<td class=\"page_count-cell\">100<\/td>\n<td class=\"record_count-cell\">100<\/td>\n<td class=\"avg_record_size_in_bytes-cell\">8060<\/td>\n<\/tr>\n<tr>\n<td class=\"index_type_desc-cell\">CLUSTERED INDEX<\/td>\n<td class=\"Alloc_unit_type_desc-cell\">IN_ROW_DATA<\/td>\n<td class=\"index_depth-cell\">4<\/td>\n<td class=\"index_level-cell\">1<\/td>\n<td class=\"page_count-cell\">24<\/td>\n<td class=\"record_count-cell\">100<\/td>\n<td class=\"avg_record_size_in_bytes-cell\">806<\/td>\n<\/tr>\n<tr>\n<td class=\"index_type_desc-cell\">CLUSTERED INDEX<\/td>\n<td class=\"Alloc_unit_type_desc-cell\">IN_ROW_DATA<\/td>\n<td class=\"index_depth-cell\">4<\/td>\n<td class=\"index_level-cell\">2<\/td>\n<td class=\"page_count-cell\">5<\/td>\n<td class=\"record_count-cell\">24<\/td>\n<td class=\"avg_record_size_in_bytes-cell\">806<\/td>\n<\/tr>\n<tr class=\"lastRow\">\n<td class=\"index_type_desc-cell\">CLUSTERED INDEX<\/td>\n<td class=\"Alloc_unit_type_desc-cell\">IN_ROW_DATA<\/td>\n<td class=\"index_depth-cell\">4<\/td>\n<td class=\"index_level-cell\">3<\/td>\n<td class=\"page_count-cell\">1<\/td>\n<td class=\"record_count-cell\">5<\/td>\n<td class=\"avg_record_size_in_bytes-cell\">806<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<p>\nWhat happened here? We have the expected 100 data rows and the corresponding 100 index_level 0 pages but instead of only 10 index_level 1 and one root page we have now 30 non leaf level pages spread over three levels.\n<\/p>\n<p>\nThe reason for this is that SQL Server, every time it requires a new page to insert a row, takes the page that should have contained the new row and splits its contents in half, leaving one half in the old page and moving the other half into the new page. After that it inserts the new row into the new page. This algorithm is used for all index pages, no matter of their index_level and independent of the value of the new row. (For leaf level pages SQL Server does not split the existing page if the position of the new row is the end of the table. It instead just adds a new empty page to the table and inserts the row in there. The example in this article hides this behavior as there is always only one row per data page.)<br \/>\nThat algorithm leaves most of the intermediate index pages with only 5 rows, so after only 55 rows a new index level is required.\n<\/p>\n<p>\nTo reclaim that space lost space we need to rebuild the index:\n<\/p>\n<div>\n[sql]\nALTER INDEX PK_IdxTst1 ON dbo.IdxTst1 REBUILD;<br \/>\n[\/sql]\n<\/div>\n<p>\nNow the table structure looks as expected:\n<\/p>\n<table>\n<thead>\n<tr>\n<th class=\"index_type_desc-cell\">index_type_desc<\/th>\n<th class=\"Alloc_unit_type_desc-cell\">Alloc_unit_type_desc<\/th>\n<th class=\"index_depth-cell\">index_depth<\/th>\n<th class=\"index_level-cell\">index_level<\/th>\n<th class=\"page_count-cell\">page_count<\/th>\n<th class=\"record_count-cell\">record_count<\/th>\n<th class=\"avg_record_size_in_bytes-cell\">avg_record_size_in_bytes<\/th>\n<\/tr>\n<\/thead>\n<tbody>\n<tr class=\"firstRow\">\n<td class=\"index_type_desc-cell\">CLUSTERED INDEX<\/td>\n<td class=\"Alloc_unit_type_desc-cell\">IN_ROW_DATA<\/td>\n<td class=\"index_depth-cell\">3<\/td>\n<td class=\"index_level-cell\">0<\/td>\n<td class=\"page_count-cell\">100<\/td>\n<td class=\"record_count-cell\">100<\/td>\n<td class=\"avg_record_size_in_bytes-cell\">8060<\/td>\n<\/tr>\n<tr>\n<td class=\"index_type_desc-cell\">CLUSTERED INDEX<\/td>\n<td class=\"Alloc_unit_type_desc-cell\">IN_ROW_DATA<\/td>\n<td class=\"index_depth-cell\">3<\/td>\n<td class=\"index_level-cell\">1<\/td>\n<td class=\"page_count-cell\">10<\/td>\n<td class=\"record_count-cell\">100<\/td>\n<td class=\"avg_record_size_in_bytes-cell\">806<\/td>\n<\/tr>\n<tr class=\"lastRow\">\n<td class=\"index_type_desc-cell\">CLUSTERED INDEX<\/td>\n<td class=\"Alloc_unit_type_desc-cell\">IN_ROW_DATA<\/td>\n<td class=\"index_depth-cell\">3<\/td>\n<td class=\"index_level-cell\">2<\/td>\n<td class=\"page_count-cell\">1<\/td>\n<td class=\"record_count-cell\">10<\/td>\n<td class=\"avg_record_size_in_bytes-cell\">806<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<p>\nThe FILLFACTOR of the index does not matter in this case as non data pages in an index are always completely filled during an index rebuild operation (unless PAD_INDEX = ON is also specified).\n<\/p>\n<h3>Conclusion<\/h3>\n<p>\nWe did see that SQL Server uses a physical tree implementation that allows for more than two child nodes per tree node. We also confirmed that SQL Server stores only the key values in the non-leaf nodes. That are the two main characteristics of a B+Tree. It also clearly rules out the binary search tree format.\n<\/p>\n<p>\nWe also ran into a situation where most of the non-leaf levels of the index where only half filled. This is one of the disadvantages that come with the use of B+Trees. Usually this is however not a big issue, as the number of non leaf pages is usually small compared to the total number of pages. You might however want to keep an eye out for this behavior, especially if you ar dealing with overly wide keys, as we did in this example.\n<\/p>\n<p>\nIf you rather store you data as compact as possible you can do so by executing an index rebuild.\n<\/p>\n<h3>Additional Information<\/h3>\n<p>\nFor further information about index internals with some nice graphics check out Michael Swart's <a href=\"http:\/\/michaeljswart.com\/2010\/09\/guts-of-an-clustered-index\/\">Guts Of An Clustered Index<\/a>, his contribution to his own SQL Saturday #10.\n<\/p>\n<\/div>\n","protected":false},"excerpt":{"rendered":"<p>T-SQL Tuesday #26 is hosted by David Howard (blog|twitter). This month\u2019s topic is \u201cSecond Chances\u201d. Index Misconceptions The topic this month is &#8220;Second Chances&#8221; which means that we can write about anything, we would have liked to write about before <a href=\"https:\/\/sqlity.net\/en\/563\/index-misconceptions-tsql-tuesday-026-second-chances\/\">[more&#8230;]<\/a><\/p>\n","protected":false},"author":3,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_jetpack_newsletter_access":"","_jetpack_dont_email_post_to_subs":false,"_jetpack_newsletter_tier_id":0,"_jetpack_memberships_contains_paywalled_content":false,"_jetpack_feature_clip_id":0,"_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},"jetpack_post_was_ever_published":false},"categories":[5,13],"tags":[],"class_list":["post-563","post","type-post","status-publish","format-standard","hentry","category-general","category-t-sql-tuesday"],"acf":[],"yoast_head":"<!-- This site is optimized with the Yoast SEO plugin v28.2 - https:\/\/yoast.com\/product\/yoast-seo-wordpress\/ -->\n<title>Index Misconceptions [TSQL Tuesday #026 - Second Chances] - sqlity.net<\/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:\/\/sqlity.net\/en\/563\/index-misconceptions-tsql-tuesday-026-second-chances\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Index Misconceptions [TSQL Tuesday #026 - Second Chances] - sqlity.net\" \/>\n<meta property=\"og:description\" content=\"T-SQL Tuesday #26 is hosted by David Howard (blog|twitter). This month\u2019s topic is \u201cSecond Chances\u201d. Index Misconceptions The topic this month is &quot;Second Chances&quot; which means that we can write about anything, we would have liked to write about before [more...]\" \/>\n<meta property=\"og:url\" content=\"https:\/\/sqlity.net\/en\/563\/index-misconceptions-tsql-tuesday-026-second-chances\/\" \/>\n<meta property=\"og:site_name\" content=\"sqlity.net\" \/>\n<meta property=\"article:publisher\" content=\"https:\/\/www.facebook.com\/sqlity.net\" \/>\n<meta property=\"article:published_time\" content=\"2012-01-10T05:00:11+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2014-11-13T19:02:30+00:00\" \/>\n<meta property=\"og:image\" content=\"http:\/\/images.sqlity.net\/SqlTuesday.png\" \/>\n<meta name=\"author\" content=\"Sebastian Meine\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:creator\" content=\"@sqlity\" \/>\n<meta name=\"twitter:site\" content=\"@sqlity\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Sebastian Meine\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"7 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\\\/\\\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/563\\\/index-misconceptions-tsql-tuesday-026-second-chances\\\/#article\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/563\\\/index-misconceptions-tsql-tuesday-026-second-chances\\\/\"},\"author\":{\"name\":\"Sebastian Meine\",\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/#\\\/schema\\\/person\\\/bcffd8c572bc2f1bd10fdba80135e53c\"},\"headline\":\"Index Misconceptions [TSQL Tuesday #026 &#8211; Second Chances]\",\"datePublished\":\"2012-01-10T05:00:11+00:00\",\"dateModified\":\"2014-11-13T19:02:30+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/563\\\/index-misconceptions-tsql-tuesday-026-second-chances\\\/\"},\"wordCount\":1444,\"commentCount\":0,\"image\":{\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/563\\\/index-misconceptions-tsql-tuesday-026-second-chances\\\/#primaryimage\"},\"thumbnailUrl\":\"http:\\\/\\\/images.sqlity.net\\\/SqlTuesday.png\",\"articleSection\":[\"General\",\"T-SQL Tuesday\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\\\/\\\/sqlity.net\\\/en\\\/563\\\/index-misconceptions-tsql-tuesday-026-second-chances\\\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/563\\\/index-misconceptions-tsql-tuesday-026-second-chances\\\/\",\"url\":\"https:\\\/\\\/sqlity.net\\\/en\\\/563\\\/index-misconceptions-tsql-tuesday-026-second-chances\\\/\",\"name\":\"Index Misconceptions [TSQL Tuesday #026 - Second Chances] - sqlity.net\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/#website\"},\"primaryImageOfPage\":{\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/563\\\/index-misconceptions-tsql-tuesday-026-second-chances\\\/#primaryimage\"},\"image\":{\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/563\\\/index-misconceptions-tsql-tuesday-026-second-chances\\\/#primaryimage\"},\"thumbnailUrl\":\"http:\\\/\\\/images.sqlity.net\\\/SqlTuesday.png\",\"datePublished\":\"2012-01-10T05:00:11+00:00\",\"dateModified\":\"2014-11-13T19:02:30+00:00\",\"author\":{\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/#\\\/schema\\\/person\\\/bcffd8c572bc2f1bd10fdba80135e53c\"},\"breadcrumb\":{\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/563\\\/index-misconceptions-tsql-tuesday-026-second-chances\\\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\\\/\\\/sqlity.net\\\/en\\\/563\\\/index-misconceptions-tsql-tuesday-026-second-chances\\\/\"]}]},{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/563\\\/index-misconceptions-tsql-tuesday-026-second-chances\\\/#primaryimage\",\"url\":\"http:\\\/\\\/images.sqlity.net\\\/SqlTuesday.png\",\"contentUrl\":\"http:\\\/\\\/images.sqlity.net\\\/SqlTuesday.png\"},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/563\\\/index-misconceptions-tsql-tuesday-026-second-chances\\\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\\\/\\\/sqlity.net\\\/en\\\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Index Misconceptions [TSQL Tuesday #026 &#8211; Second Chances]\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/#website\",\"url\":\"https:\\\/\\\/sqlity.net\\\/en\\\/\",\"name\":\"sqlity.net\",\"description\":\"Quality for SQL\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\\\/\\\/sqlity.net\\\/en\\\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":\"Person\",\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/#\\\/schema\\\/person\\\/bcffd8c572bc2f1bd10fdba80135e53c\",\"name\":\"Sebastian Meine\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/4ab0a6d02dd494849a584a2c3c8bc3bdcef1d0aa5f87e98bf905dbdb9ad2ce3a?s=96&d=mm&r=g\",\"url\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/4ab0a6d02dd494849a584a2c3c8bc3bdcef1d0aa5f87e98bf905dbdb9ad2ce3a?s=96&d=mm&r=g\",\"contentUrl\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/4ab0a6d02dd494849a584a2c3c8bc3bdcef1d0aa5f87e98bf905dbdb9ad2ce3a?s=96&d=mm&r=g\",\"caption\":\"Sebastian Meine\"},\"sameAs\":[\"http:\\\/\\\/sqlity.net\",\"https:\\\/\\\/x.com\\\/sqlity\"]}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"Index Misconceptions [TSQL Tuesday #026 - Second Chances] - sqlity.net","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:\/\/sqlity.net\/en\/563\/index-misconceptions-tsql-tuesday-026-second-chances\/","og_locale":"en_US","og_type":"article","og_title":"Index Misconceptions [TSQL Tuesday #026 - Second Chances] - sqlity.net","og_description":"T-SQL Tuesday #26 is hosted by David Howard (blog|twitter). This month\u2019s topic is \u201cSecond Chances\u201d. Index Misconceptions The topic this month is \"Second Chances\" which means that we can write about anything, we would have liked to write about before [more...]","og_url":"https:\/\/sqlity.net\/en\/563\/index-misconceptions-tsql-tuesday-026-second-chances\/","og_site_name":"sqlity.net","article_publisher":"https:\/\/www.facebook.com\/sqlity.net","article_published_time":"2012-01-10T05:00:11+00:00","article_modified_time":"2014-11-13T19:02:30+00:00","og_image":[{"url":"http:\/\/images.sqlity.net\/SqlTuesday.png","type":"","width":"","height":""}],"author":"Sebastian Meine","twitter_card":"summary_large_image","twitter_creator":"@sqlity","twitter_site":"@sqlity","twitter_misc":{"Written by":"Sebastian Meine","Est. reading time":"7 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/sqlity.net\/en\/563\/index-misconceptions-tsql-tuesday-026-second-chances\/#article","isPartOf":{"@id":"https:\/\/sqlity.net\/en\/563\/index-misconceptions-tsql-tuesday-026-second-chances\/"},"author":{"name":"Sebastian Meine","@id":"https:\/\/sqlity.net\/en\/#\/schema\/person\/bcffd8c572bc2f1bd10fdba80135e53c"},"headline":"Index Misconceptions [TSQL Tuesday #026 &#8211; Second Chances]","datePublished":"2012-01-10T05:00:11+00:00","dateModified":"2014-11-13T19:02:30+00:00","mainEntityOfPage":{"@id":"https:\/\/sqlity.net\/en\/563\/index-misconceptions-tsql-tuesday-026-second-chances\/"},"wordCount":1444,"commentCount":0,"image":{"@id":"https:\/\/sqlity.net\/en\/563\/index-misconceptions-tsql-tuesday-026-second-chances\/#primaryimage"},"thumbnailUrl":"http:\/\/images.sqlity.net\/SqlTuesday.png","articleSection":["General","T-SQL Tuesday"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/sqlity.net\/en\/563\/index-misconceptions-tsql-tuesday-026-second-chances\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/sqlity.net\/en\/563\/index-misconceptions-tsql-tuesday-026-second-chances\/","url":"https:\/\/sqlity.net\/en\/563\/index-misconceptions-tsql-tuesday-026-second-chances\/","name":"Index Misconceptions [TSQL Tuesday #026 - Second Chances] - sqlity.net","isPartOf":{"@id":"https:\/\/sqlity.net\/en\/#website"},"primaryImageOfPage":{"@id":"https:\/\/sqlity.net\/en\/563\/index-misconceptions-tsql-tuesday-026-second-chances\/#primaryimage"},"image":{"@id":"https:\/\/sqlity.net\/en\/563\/index-misconceptions-tsql-tuesday-026-second-chances\/#primaryimage"},"thumbnailUrl":"http:\/\/images.sqlity.net\/SqlTuesday.png","datePublished":"2012-01-10T05:00:11+00:00","dateModified":"2014-11-13T19:02:30+00:00","author":{"@id":"https:\/\/sqlity.net\/en\/#\/schema\/person\/bcffd8c572bc2f1bd10fdba80135e53c"},"breadcrumb":{"@id":"https:\/\/sqlity.net\/en\/563\/index-misconceptions-tsql-tuesday-026-second-chances\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/sqlity.net\/en\/563\/index-misconceptions-tsql-tuesday-026-second-chances\/"]}]},{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/sqlity.net\/en\/563\/index-misconceptions-tsql-tuesday-026-second-chances\/#primaryimage","url":"http:\/\/images.sqlity.net\/SqlTuesday.png","contentUrl":"http:\/\/images.sqlity.net\/SqlTuesday.png"},{"@type":"BreadcrumbList","@id":"https:\/\/sqlity.net\/en\/563\/index-misconceptions-tsql-tuesday-026-second-chances\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/sqlity.net\/en\/"},{"@type":"ListItem","position":2,"name":"Index Misconceptions [TSQL Tuesday #026 &#8211; Second Chances]"}]},{"@type":"WebSite","@id":"https:\/\/sqlity.net\/en\/#website","url":"https:\/\/sqlity.net\/en\/","name":"sqlity.net","description":"Quality for SQL","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/sqlity.net\/en\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Person","@id":"https:\/\/sqlity.net\/en\/#\/schema\/person\/bcffd8c572bc2f1bd10fdba80135e53c","name":"Sebastian Meine","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/secure.gravatar.com\/avatar\/4ab0a6d02dd494849a584a2c3c8bc3bdcef1d0aa5f87e98bf905dbdb9ad2ce3a?s=96&d=mm&r=g","url":"https:\/\/secure.gravatar.com\/avatar\/4ab0a6d02dd494849a584a2c3c8bc3bdcef1d0aa5f87e98bf905dbdb9ad2ce3a?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/4ab0a6d02dd494849a584a2c3c8bc3bdcef1d0aa5f87e98bf905dbdb9ad2ce3a?s=96&d=mm&r=g","caption":"Sebastian Meine"},"sameAs":["http:\/\/sqlity.net","https:\/\/x.com\/sqlity"]}]}},"jetpack_publicize_connections":[],"jetpack_sharing_enabled":true,"jetpack_shortlink":"https:\/\/wp.me\/p2wXuw-95","jetpack-related-posts":[],"jetpack_featured_media_url":"","_links":{"self":[{"href":"https:\/\/sqlity.net\/en\/wp-json\/wp\/v2\/posts\/563","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/sqlity.net\/en\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/sqlity.net\/en\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/sqlity.net\/en\/wp-json\/wp\/v2\/users\/3"}],"replies":[{"embeddable":true,"href":"https:\/\/sqlity.net\/en\/wp-json\/wp\/v2\/comments?post=563"}],"version-history":[{"count":0,"href":"https:\/\/sqlity.net\/en\/wp-json\/wp\/v2\/posts\/563\/revisions"}],"wp:attachment":[{"href":"https:\/\/sqlity.net\/en\/wp-json\/wp\/v2\/media?parent=563"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/sqlity.net\/en\/wp-json\/wp\/v2\/categories?post=563"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/sqlity.net\/en\/wp-json\/wp\/v2\/tags?post=563"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}