{"id":470,"date":"2012-09-22T15:02:00","date_gmt":"2012-09-22T15:02:00","guid":{"rendered":"\/blogs\/bobb\/post\/Selective-XML-Index-Implementation-Details.aspx"},"modified":"2014-01-20T12:22:15","modified_gmt":"2014-01-20T20:22:15","slug":"selective-xml-index-implementation-details","status":"publish","type":"post","link":"https:\/\/www.sqlskills.com\/blogs\/bobb\/selective-xml-index-implementation-details\/","title":{"rendered":"Selective XML Index – Implementation Details"},"content":{"rendered":"

\nA while after I posted part 1, there was a twitter message from Aaron (@AaronBertrand), a well-known "smart person" in the SQL Server space, about using feature packs as "ship vehicles", especially when they (possibly) have the effect of requiring keeping all instances in-sync at the service pack level to keep things working and possibly even making SP feature relevent during a restore. I answered that "it must be a compelling feature". And then we reminisced about features like "vardecimal" and "15k partitions". So I'll discuss why it is a compelling feature (or at least why I happen to think it is) later on in the process.\n<\/p>\n

\nBut for now, about how it's implemented…\n<\/p>\n

\nBefore this even. You can add or remove path specs on a Selective XML Index. I found this one by "reading into an error message" and trying syntax until it worked.\n<\/p>\n

\n— add a pathspec
\nalter index fooidx on foo
\nfor
\n(
\nadd another_pathspec = 'foo\/bar2'
\n);\n<\/p>\n

\n–remove the pathspec we just added
\nalter index fooidx on foo
\nfor
\n(
\nremove another_pathspec
\n);\n<\/p>\n

\nAs Micheal said, the selective XML index is implemented using sparse columns. The first column (or columns) is the primary key of the base table, which they always named pk1…n. Then a row_id column, which is incremented if there are multiple matching nodes in the same row.\n<\/p>\n

\nEach pathspec consists of at least two columns: a path column and 1-2 value column(s). Path column is named path_[n]_id (which is always varbinary(900) and sparse).\n<\/p>\n

\nValue column is named either "[pathspec_name]_[n]_value" or "[pathspec_name]_[n]_sql_value" (for SQL type pathspec). If the pathspec contains a wildcard, there is an additional column named path_[n]_hid (varbinary(900)) where HID stands for hierarchyid (i.e. the path in the document). A pathspec with an XPath\/XQuery wildcard would look like this: '\/foo\/*\/baz2'.\n<\/p>\n

\nAs an aside, remember that the SQL Server XML data type (node paths) and hierarchyid data type use the same encoding scheme, known as Ordpath, see my old blog entrry "Ordpath, ordpath, everywhere<\/a>".\n<\/p>\n

\nAll of the columns (except pk1 and row_id) are sparse. As far as data types (because the nice thing is that SPARSE columns can preserve strong typing) as typed as:
\n Type 1: xs:untypedAtomic = varbinary(max)
\n Type 2: The closest SQL data type to XSD type (e.g. xs:double = SQL FLOAT)
\n Type 3: The SQL type specified in the "pathspec AS SQL…" specification. Including length and collation.\n<\/p>\n

\nThe length of all of the sparse columns are based on the length of the column (either express or implied) in the pathspec.\n<\/p>\n

\nSo, what's this all mean?\n<\/p>\n

\nIt means that, depending on how selective each pathspec is, how many pathspecs are designation in the Selective XML Index definition and the sparseness of each of the column values, the Selective XML Index can be MUCH, MUCH smaller than the (original) PRIMARY XML INDEX (which is implemented as a 12-column side-table, one row per XML node). Because you're only indexing those values your care about. And individual node data CAN be sparse in XML (element text can be, but XML attribute nodes are even more likely to be sparse).\n<\/p>\n

\nAnd it also means that the limitations of SQL Server 2008 SPARSE columns apply. See this starting point in Books Online<\/a> to understand these limits in detail. And, because there isn't a XML columnset column, you are limited to theoretical maximum of 511 pathspecs, if none of them are wildcards and there's a 1 column primary key (1024 maximum columns in table without a columnset, – 2 \/ 2. I don't know what the real limit is yet.\n<\/p>\n

\n@bobbeauch<\/p>\n","protected":false},"excerpt":{"rendered":"

A while after I posted part 1, there was a twitter message from Aaron (@AaronBertrand), a well-known "smart person" in the SQL Server space, about using feature packs as "ship vehicles", especially when they (possibly) have the effect of requiring keeping all instances in-sync at the service pack level to keep things working and possibly even making SP […]<\/p>\n","protected":false},"author":7,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[31,37],"tags":[],"class_list":["post-470","post","type-post","status-publish","format-standard","hentry","category-sql-server-2012","category-sql-server-xml"],"yoast_head":"\nSelective XML Index - Implementation Details - Bob Beauchemin<\/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\/bobb\/selective-xml-index-implementation-details\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Selective XML Index - Implementation Details - Bob Beauchemin\" \/>\n<meta property=\"og:description\" content=\"A while after I posted part 1, there was a twitter message from Aaron (@AaronBertrand), a well-known "smart person" in the SQL Server space, about using feature packs as "ship vehicles", especially when they (possibly) have the effect of requiring keeping all instances in-sync at the service pack level to keep things working and possibly even making SP […]\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.sqlskills.com\/blogs\/bobb\/selective-xml-index-implementation-details\/\" \/>\n<meta property=\"og:site_name\" content=\"Bob Beauchemin\" \/>\n<meta property=\"article:published_time\" content=\"2012-09-22T15:02:00+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2014-01-20T20:22:15+00:00\" \/>\n<meta name=\"author\" content=\"Bob Beauchemin\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Bob Beauchemin\" \/>\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\/bobb\/selective-xml-index-implementation-details\/\",\"url\":\"https:\/\/www.sqlskills.com\/blogs\/bobb\/selective-xml-index-implementation-details\/\",\"name\":\"Selective XML Index - Implementation Details - Bob Beauchemin\",\"isPartOf\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/bobb\/#website\"},\"datePublished\":\"2012-09-22T15:02:00+00:00\",\"dateModified\":\"2014-01-20T20:22:15+00:00\",\"author\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/bobb\/#\/schema\/person\/62bfa986c5b5d28fcffd8b4fc409c73e\"},\"breadcrumb\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/bobb\/selective-xml-index-implementation-details\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.sqlskills.com\/blogs\/bobb\/selective-xml-index-implementation-details\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/bobb\/selective-xml-index-implementation-details\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/www.sqlskills.com\/blogs\/bobb\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"SQL Server 2012\",\"item\":\"https:\/\/www.sqlskills.com\/blogs\/bobb\/category\/sql-server-2012\/\"},{\"@type\":\"ListItem\",\"position\":3,\"name\":\"Selective XML Index – Implementation Details\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/bobb\/#website\",\"url\":\"https:\/\/www.sqlskills.com\/blogs\/bobb\/\",\"name\":\"Bob Beauchemin\",\"description\":\"SQL Server Blog\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/www.sqlskills.com\/blogs\/bobb\/?s={search_term_string}\"},\"query-input\":\"required name=search_term_string\"}],\"inLanguage\":\"en-US\"},{\"@type\":\"Person\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/bobb\/#\/schema\/person\/62bfa986c5b5d28fcffd8b4fc409c73e\",\"name\":\"Bob Beauchemin\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/bobb\/#\/schema\/person\/image\/\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/6f80e6cc667410857fa6a21931dc528b8092f4d112bf7a8ff7c267674d44ee37?s=96&d=mm&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/6f80e6cc667410857fa6a21931dc528b8092f4d112bf7a8ff7c267674d44ee37?s=96&d=mm&r=g\",\"caption\":\"Bob Beauchemin\"},\"sameAs\":[\"http:\/www.sqlskills.com\/blogs\/bobb\/\"],\"url\":\"https:\/\/www.sqlskills.com\/blogs\/bobb\/author\/bobb\/\"}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"Selective XML Index - Implementation Details - Bob Beauchemin","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\/bobb\/selective-xml-index-implementation-details\/","og_locale":"en_US","og_type":"article","og_title":"Selective XML Index - Implementation Details - Bob Beauchemin","og_description":"A while after I posted part 1, there was a twitter message from Aaron (@AaronBertrand), a well-known "smart person" in the SQL Server space, about using feature packs as "ship vehicles", especially when they (possibly) have the effect of requiring keeping all instances in-sync at the service pack level to keep things working and possibly even making SP […]","og_url":"https:\/\/www.sqlskills.com\/blogs\/bobb\/selective-xml-index-implementation-details\/","og_site_name":"Bob Beauchemin","article_published_time":"2012-09-22T15:02:00+00:00","article_modified_time":"2014-01-20T20:22:15+00:00","author":"Bob Beauchemin","twitter_misc":{"Written by":"Bob Beauchemin","Est. reading time":"3 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"WebPage","@id":"https:\/\/www.sqlskills.com\/blogs\/bobb\/selective-xml-index-implementation-details\/","url":"https:\/\/www.sqlskills.com\/blogs\/bobb\/selective-xml-index-implementation-details\/","name":"Selective XML Index - Implementation Details - Bob Beauchemin","isPartOf":{"@id":"https:\/\/www.sqlskills.com\/blogs\/bobb\/#website"},"datePublished":"2012-09-22T15:02:00+00:00","dateModified":"2014-01-20T20:22:15+00:00","author":{"@id":"https:\/\/www.sqlskills.com\/blogs\/bobb\/#\/schema\/person\/62bfa986c5b5d28fcffd8b4fc409c73e"},"breadcrumb":{"@id":"https:\/\/www.sqlskills.com\/blogs\/bobb\/selective-xml-index-implementation-details\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.sqlskills.com\/blogs\/bobb\/selective-xml-index-implementation-details\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.sqlskills.com\/blogs\/bobb\/selective-xml-index-implementation-details\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/www.sqlskills.com\/blogs\/bobb\/"},{"@type":"ListItem","position":2,"name":"SQL Server 2012","item":"https:\/\/www.sqlskills.com\/blogs\/bobb\/category\/sql-server-2012\/"},{"@type":"ListItem","position":3,"name":"Selective XML Index – Implementation Details"}]},{"@type":"WebSite","@id":"https:\/\/www.sqlskills.com\/blogs\/bobb\/#website","url":"https:\/\/www.sqlskills.com\/blogs\/bobb\/","name":"Bob Beauchemin","description":"SQL Server Blog","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/www.sqlskills.com\/blogs\/bobb\/?s={search_term_string}"},"query-input":"required name=search_term_string"}],"inLanguage":"en-US"},{"@type":"Person","@id":"https:\/\/www.sqlskills.com\/blogs\/bobb\/#\/schema\/person\/62bfa986c5b5d28fcffd8b4fc409c73e","name":"Bob Beauchemin","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/www.sqlskills.com\/blogs\/bobb\/#\/schema\/person\/image\/","url":"https:\/\/secure.gravatar.com\/avatar\/6f80e6cc667410857fa6a21931dc528b8092f4d112bf7a8ff7c267674d44ee37?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/6f80e6cc667410857fa6a21931dc528b8092f4d112bf7a8ff7c267674d44ee37?s=96&d=mm&r=g","caption":"Bob Beauchemin"},"sameAs":["http:\/www.sqlskills.com\/blogs\/bobb\/"],"url":"https:\/\/www.sqlskills.com\/blogs\/bobb\/author\/bobb\/"}]}},"_links":{"self":[{"href":"https:\/\/www.sqlskills.com\/blogs\/bobb\/wp-json\/wp\/v2\/posts\/470","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.sqlskills.com\/blogs\/bobb\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.sqlskills.com\/blogs\/bobb\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/bobb\/wp-json\/wp\/v2\/users\/7"}],"replies":[{"embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/bobb\/wp-json\/wp\/v2\/comments?post=470"}],"version-history":[{"count":0,"href":"https:\/\/www.sqlskills.com\/blogs\/bobb\/wp-json\/wp\/v2\/posts\/470\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.sqlskills.com\/blogs\/bobb\/wp-json\/wp\/v2\/media?parent=470"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/bobb\/wp-json\/wp\/v2\/categories?post=470"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/bobb\/wp-json\/wp\/v2\/tags?post=470"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}