{"id":1570,"date":"2013-01-25T00:00:00","date_gmt":"2013-01-25T00:00:00","guid":{"rendered":"https:\/\/test.simple-talk.com\/uncategorized\/precision-indexing-basics-of-selective-xml-indexes-in-sql-server-2012\/"},"modified":"2021-08-16T15:02:00","modified_gmt":"2021-08-16T15:02:00","slug":"precision-indexing-basics-of-selective-xml-indexes-in-sql-server-2012","status":"publish","type":"post","link":"https:\/\/www.red-gate.com\/simple-talk\/databases\/sql-server\/learn\/precision-indexing-basics-of-selective-xml-indexes-in-sql-server-2012\/","title":{"rendered":"Precision Indexing:  Basics of Selective XML Indexes in SQL Server 2012"},"content":{"rendered":"<div class=\"article-content\">\n<p class=\"start\">SQL Server 2012 has introduced a brand-new <a href=\"http:\/\/msdn.microsoft.com\/en-us\/library\/bb500435.aspx\">feature<\/a> called &#8216;Selective XML Indexes&#8217;, available in the SP1 update. Selective XML indexes work to extend the existing XML indexing options by offering what can be a much more efficient indexing solution. Selective XML indexes have the benefits of <a href=\"http:\/\/www.simple-talk.com\/sql\/database-administration\/getting-started-with-xml-indexes\/\">standard XML indexes<\/a>, but have a dramatically reduced storage and maintenance requirement. They can be customized during creation to accommodate specific types of XML queries. <\/p>\n<h2>How Are Selective XML Indexes Different? <\/h2>\n<p>One of the main compromises of using standard XML indexes is that you&#8217;re likely to have to use huge amounts of disk space to store the index. When a standard XML index is created, the <strong>entire<\/strong> XML document or fragment is <a href=\"http:\/\/www.simple-talk.com\/sql\/database-administration\/getting-started-with-xml-indexes\/\">shredded<\/a> to provide a relational version of the data because a relational index table is actually created from the nodes in the XML document. This index table is usually <strong>many times larger<\/strong> than the original size of the XML document. Even if you only need to query certain nodes or paths, the XML indexing process shreds all portions of the document. Selective XML indexes, however, shred only the node paths of the XML document <strong>that are <\/strong><strong>explicitly <\/strong><strong>specified on index creation<\/strong> &#8211; thereby greatly reducing the index overhead in terms of storage space and creation time. Therefore, the main advantages of using a selective XML index over standard XML indexes are: <\/p>\n<ul>\n<li>Greatly decreased storage requirements  <\/li>\n<li>Greatly decreased index creation times  <\/li>\n<li>Greatly decreased index maintenance costs <\/li>\n<\/ul>\n<h2>Selective XML Index Basics <\/h2>\n<p>To demonstrate the advantages of selective XML indexes, let&#8217;s run a script that will generate an XML representation of the <strong>SalesOrderHeader<\/strong> and <strong>SalesOrderDetails<\/strong> tables from the <a href=\"http:\/\/msftdbprodsamples.codeplex.com\/releases\/view\/55330\">AdventureWorks2012<\/a> sample database: <\/p>\n<pre class=\"lang:tsql theme:ssms2012\">--create database and table to hold generated xml data\nCREATE DATABASE Sales_XML\nGO\n\nCREATE TABLE Sales_XML.dbo.SalesOrdersData\n(\n  ID INT PRIMARY KEY, \n  SalesOrdersDetail XML\n)\nGO\n\n--use AdventureWorks2012 sample database to generate xml data\nDECLARE @counter INT = 1\nDECLARE @row VARCHAR(MAX) \n\n--for each sales order (SalesOrderHeader record), find all associated line items (SalesOrderDetail records)\nWHILE @counter &lt;= (SELECT COUNT(*) FROM AdventureWorks2012.Sales.SalesOrderHeader)\nBEGIN\n\n  WITH Orders AS\n  (\n    --use ROW_NUMBER() to enumerate SalesOrderHeader records\n    SELECT ROW_NUMBER() OVER(ORDER BY SalesOrderID) AS RowNum, *\n    FROM AdventureWorks2012.Sales.SalesOrderHeader\n  ) \n  SELECT @row = (\n        SELECT  o.SalesOrderID AS '@ID',\n          o.OrderDate,\n          o.PurchaseOrderNumber,\n          o.SalesPersonID,\n          (\n            --associated SalesOrderDetail records\n            SELECT  d.SalesOrderDetailID  AS '@ID',\n              d.OrderQty,\n              d.rowguid AS 'RowGUID',\n              d.ProductID,\n              d.LineTotal,\n              d.UnitPrice\n            FROM AdventureWorks2012.Sales.SalesOrderDetail AS d\n            WHERE o.SalesOrderID = d.SalesOrderID\n            FOR XML PATH('SalesOrderDetail'), TYPE\n                \n) AS SalesOrderDetails\n        FROM Orders AS o \n        WHERE o.RowNum = @counter\n        FOR XML PATH('SalesOrder'), ROOT('SalesOrders')\n      )\n\n  INSERT Sales_XML.dbo.SalesOrdersData VALUES(@counter, CONVERT(XML,@row))\n\n  SET @counter += 1\n\nEND<\/pre>\n<p>The reason we&#8217;ve added the &#8216;ID&#8217; column to our new <strong>Sales_XML<\/strong> table is because a primary key is required in order to use selective XML indexes. After we&#8217;ve run the above script, let&#8217;s take a look at a random XML fragment: <\/p>\n<pre class=\"lang:tsql theme:ssms2012\">--get a random record from the new XML table\nSELECT TOP 1 SalesOrdersDetail\nFROM Sales_XML.dbo.SalesOrdersData\nORDER BY NEWID()\nGO<\/pre>\n<pre class=\"lang:xhtml theme:github\">&lt;SalesOrders&gt;\n  &lt;SalesOrder id=\"gt;\"\n    &lt;Orderdate&gt;2008-03-30T00:00:00&lt;\/Orderdate&gt;\n    &lt;SalesOrderDetails&gt;\n      &lt;SalesOrderDetail id=\"gt;\"&gt;\n        &lt;OrderQty&gt;1&lt;\/OrderQty&gt;\n        &lt;RowGUID&gt;E55F8171-2B62-43D1-8A53-EE83F057D08&lt;\/RowGUID&gt;\n        &lt;ProductID&gt;878&lt;\/ProductID&gt;\n        &lt;LineTotal&gt;21.980000&lt;\/LineTotal&gt;\n        &lt;UnitPrice&gt;21.9800&lt;\/UnitPrice&gt;\n      &lt;\/SalesOrderDetail&gt;\n      &lt;SalesOrderDetail id=\"gt;\"&gt;\n        &lt;OrderQty&gt;1&lt;\/OrderQty&gt;\n        &lt;RowGUID&gt;7CE490FB-D798-41C9-B893-F0D6124880F8&lt;\/RowGUID&gt;\n        &lt;ProductID&gt;716&lt;\/ProductID&gt;\n        &lt;LineTotal&gt;49.990000&lt;\/LineTotal&gt;\n        &lt;UnitPrice&gt;49.9900&lt;\/UnitPrice&gt;\n      &lt;\/SalesOrderDetail&gt;\n    &lt;\/SalesOrderDetails&gt;\n  &lt;\/SalesOrder&gt;\n&lt;\/SalesOrders&gt;<\/pre>\n<p>You&#8217;ll notice that the XML fragment contains relevant data from the <strong>SalesOrderHeader <\/strong>record, combined with data from all associated <strong>SalesOrderDetail<\/strong> records. In this case, there are two associated <strong>SalesOrderDetail<\/strong> records. We can quickly verify that this is an accurate count by checking the originating table: <\/p>\n<pre class=\"lang:tsql theme:ssms2012\">--cross-check the number of detail records\nSELECT SalesOrderDetailID, ProductID\nFROM AdventureWorks2012.Sales.SalesOrderDetail\nWHERE SalesOrderID = 67180\nGO<\/pre>\n<p class=\"illustration\"><img decoding=\"async\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/imported\/1729-2ef4e19c-433c-4dd8-8acb-df9c24768998.png\" alt=\"1729-2ef4e19c-433c-4dd8-8acb-df9c2476899\" \/><\/p>\n<p>There are indeed two <strong>SalesOrderDetail<\/strong> records for <strong>SalesOrderID<\/strong> 67180. We will also check to see that a record has been generated for every sales order (<strong>SalesHeaderOrder<\/strong> record): <\/p>\n<pre class=\"lang:tsql theme:ssms2012\">--find count of matching SalesOrderIDs between original header record SalesOrderIDs and new xml record SalesOrderIDs\nSELECT COUNT(*) \nFROM \n(\n  SELECT SalesOrdersDetail.value('(\/\/SalesOrder\/@ID)[1]', 'varchar(20)') AS SalesOrderID\n  FROM Sales_XML.dbo.SalesOrdersData\n) x\nJOIN AdventureWorks2012.Sales.SalesOrderHeader a\n   ON a.SalesOrderID = x.SalesOrderID\nGO\n\n--compare to count of ALL header records\nSELECT COUNT(*) \nFROM AdventureWorks2012.Sales.SalesOrderHeader\nGO<\/pre>\n<p class=\"illustration\"><img decoding=\"async\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/imported\/1729-110b547b-a9f7-4f28-ae6c-57b4b69fd941.png\" alt=\"1729-110b547b-a9f7-4f28-ae6c-57b4b69fd94\" \/><\/p>\n<p>This verifies that there exists an XML fragment (containing all related <strong>SalesOrderDetail <\/strong>records) in the new <strong>Sales_XML<\/strong> table for every <strong><\/strong><strong>SalesOrderID<\/strong> in the <strong>SalesOrderHeader<\/strong> table. <\/p>\n<p>Now that we have XML data, we can start experimenting with a selective XML index. Although we are only allowed to have one selective XML index per XML column, we can customize a single index to work with several types of XML queries. Initially, let&#8217;s plan on writing queries that filter the XML fragments by <strong>SalesPersonID<\/strong>, like the following: <\/p>\n<pre class=\"lang:tsql theme:ssms2012\">--get sales order count for a given salesperson\nSELECT COUNT(*) AS SalesOrderCount\nFROM Sales_XML.dbo.SalesOrdersData\nWHERE SalesOrdersDetail.exist('\/SalesOrders\/SalesOrder\/SalesPersonID[.=276]') = 1\nGO<\/pre>\n<p>This query will give us the number of sales orders that were handled by the salesperson having ID #276. Let&#8217;s run the query before we add a selective XML index &#8211; but first we&#8217;ll <a href=\"http:\/\/msdn.microsoft.com\/en-us\/library\/ms190287.aspx\">SET STATISTICS TIME ON<\/a> so we can see the query time before and after creating the index: <\/p>\n<pre class=\"lang:tsql theme:ssms2012\">--turn on statistics time\nSET STATISTICS TIME ON<\/pre>\n<p>Now we&#8217;ll run the above &#8216;sales order count&#8217; query: <\/p>\n<p class=\"illustration\"><img decoding=\"async\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/imported\/1729-9fdc1a52-180c-421f-a4dc-23d44715e461.png\" alt=\"1729-9fdc1a52-180c-421f-a4dc-23d44715e46\" \/><\/p>\n<p>In the Messages tab, we see that the query execution time was 428 ms: <\/p>\n<p class=\"illustration center\"><img decoding=\"async\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/imported\/1729-a6941a4a-f71f-4cdf-b6af-62418aba7656.png\" alt=\"1729-a6941a4a-f71f-4cdf-b6af-62418aba765\" \/><\/p>\n<p>To see if we can speed up this type of query, we&#8217;ll create a basic selective XML index: <\/p>\n<pre class=\"lang:tsql theme:ssms2012\">--create a selective XML index\nCREATE SELECTIVE XML INDEX SXI_Selective_SalesOrdersDetails\nON Sales_XML.dbo.SalesOrdersData(SalesOrdersDetail)\nFOR \n(\n    pathSalesPersonID = '\/SalesOrders\/SalesOrder\/SalesPersonID'\n)\nGO<\/pre>\n<p>Note that we&#8217;ve customized this index for a specific XQuery path (\/SalesOrders\/SalesOrder\/SalesPersonID&#8217;), and we&#8217;ve given it a name (pathSalesPersonID). However, we get the following error when running the create index statement: <\/p>\n<p class=\"illustration center\"><img decoding=\"async\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/imported\/1729-2b7d52c1-7271-477a-83e8-f8f6123967fe.png\" alt=\"1729-2b7d52c1-7271-477a-83e8-f8f6123967f\" \/><\/p>\n<p>This is because selective XML indexes must be enabled for each database. To <a href=\"http:\/\/msdn.microsoft.com\/en-us\/library\/jj670102.aspx\">enable<\/a> selective indexes, use the <strong>sp_db_selective_<\/strong><strong>xml_<\/strong><strong>index<\/strong> stored procedure: <\/p>\n<pre class=\"lang:tsql theme:ssms2012\">--enable selective XML indexes for the database\nEXECUTE Sales_XML.sys.sp_db_selective_xml_index Sales_XML, TRUE\n<\/pre>\n<p class=\"illustration\"><img decoding=\"async\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/imported\/1729-184f1319-14f8-4b9f-827d-6ca646810351.png\" alt=\"1729-184f1319-14f8-4b9f-827d-6ca64681035\" \/><\/p>\n<p>Interestingly, in order to reverse this, the database must be set to the simple recovery model. If the recovery model is set otherwise, the following message is seen: <\/p>\n<pre class=\"lang:tsql theme:ssms2012\">--disable selective XML indexes for the database\nEXECUTE Sales_XML.sys.sp_db_selective_xml_index Sales_XML, FALSE\n<\/pre>\n<p class=\"illustration center\"><img decoding=\"async\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/imported\/1729-3c7f3f0a-6d86-4c8e-95d7-8c4786e823a1.png\" alt=\"1729-3c7f3f0a-6d86-4c8e-95d7-8c4786e823a\" \/><\/p>\n<p>Now that selective XML indexes are enabled for the <strong>Sales_XML<\/strong> database, we can run the create index script again, and we can also check to make sure that the index was created: <\/p>\n<pre class=\"lang:tsql theme:ssms2012\">--find all XML indexes in the database\nUSE Sales_XML\nGO\n\nSELECT \ni.name as IndexName, \no.name as TableName, \nc.[name] as ColumnName\nFROM sys.indexes i \nJOIN sys.objects o \n\tON i.object_id = o.object_id\nJOIN sys.index_columns ic \n\tON ic.object_id = i.object_id \n\t\tAND ic.index_id = i.index_id\nJOIN sys.columns c\n\tON c.object_id = i.object_id \n\t\tAND c.column_id = ic.column_id\nWHERE i.type_desc = 'XML'\n\tAND is_disabled = 0\nGO<\/pre>\n<p class=\"illustration center\"><img decoding=\"async\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/imported\/1729-bdb44cf4-c658-4b93-b7c4-c54de3a1d8d0.png\" alt=\"1729-bdb44cf4-c658-4b93-b7c4-c54de3a1d8d\" \/><\/p>\n<p>The index exists. We can also verify that it is a selective XML index by expanding the Indexes node for the table in Object Explorer: <\/p>\n<p class=\"illustration center\"><img decoding=\"async\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/imported\/1729-8d85065d-ef65-4f8c-875a-13f3482f2523.png\" alt=\"1729-8d85065d-ef65-4f8c-875a-13f3482f252\" \/><\/p>\n<p>Let&#8217;s run our query again: <\/p>\n<pre class=\"lang:tsql theme:ssms2012\">--get sales order count for a given salesperson\nSELECT COUNT(*) AS SalesOrderCount\nFROM Sales_XML.dbo.SalesOrdersData\nWHERE SalesOrdersDetail.exist('\/SalesOrders\/SalesOrder\/SalesPersonID[.=276]') = 1\nGO<\/pre>\n<p>We now see a greatly reduced query execution time: <\/p>\n<p class=\"illustration\"><img decoding=\"async\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/imported\/1729-1dc3792e-18e3-4edc-a560-162070e38913.png\" alt=\"1729-1dc3792e-18e3-4edc-a560-162070e3891\" \/><\/p>\n<p>That&#8217;s a reduction in query execution time by more than <strong>90%<\/strong>! <strong><\/strong><\/p>\n<h2>Selective XML Index Optimizations <\/h2>\n<p>Selective XML indexes make allowances for some fine-tuning customizations of path specifications, using either <strong>XQuery<\/strong> or <strong>SQL Server<\/strong> data types (for <a href=\"http:\/\/msdn.microsoft.com\/en-us\/library\/ms184277.aspx\">untyped XML<\/a>). By using XQuery data types, we can take advantage of what is called &#8216;user-specified mapping mode&#8217;. In this mode, <a href=\"http:\/\/msdn.microsoft.com\/en-us\/library\/jj670107.aspx\">optional optimization hints<\/a> and XQuery data types are defined, in addition to the XQuery path specifications. When default mapping mode is used, only the path is specified. SQL Server data types, on the other hand, are designed to efficiently handle return values from the XQuery <a href=\"http:\/\/msdn.microsoft.com\/en-us\/library\/ms178030.aspx\">value() method<\/a>. Although better query performance can be expected when using optimization hints on untyped XML, there is a risk of failed cast operations. This is because the index assumes that data of the correct type will be present in the path, when this may not always be the case. By using default mapping mode (no optimization hints), one can guarantee that there will be no cast operation failures. In the case of <a href=\"http:\/\/msdn.microsoft.com\/en-us\/library\/ms184277.aspx\">typed XML<\/a>, there is no reason to specify the data type, as data types are discovered by using the schema. <\/p>\n<p>We&#8217;re using untyped XML, so let&#8217;s try to improve our query execution time by adding an optimization hint. Before we specify an appropriate hint, we will need to define an XQuery data type for the SalesPersonID node. <\/p>\n<p>The <a href=\"http:\/\/msdn.microsoft.com\/en-us\/library\/jj670107.aspx\">available XQuery types<\/a> for untyped XML are: <\/p>\n<ul>\n<li>xs:boolean  <\/li>\n<li>xs:double  <\/li>\n<li>xs:string  <\/li>\n<li>xs:date  <\/li>\n<li>xs:time  <\/li>\n<li>xs:dateTime <\/li>\n<\/ul>\n<p>Notice that there is no <strong>xs:integer<\/strong> type. We&#8217;ll use the closest available type, <strong>xs:double<\/strong>. Also, since we are familiar with our data, we know that there is always only one <strong>SalesPersonID<\/strong> node in its parent (<strong>SalesOrder<\/strong> node). This will allow us to implement the <a href=\"http:\/\/msdn.microsoft.com\/en-us\/library\/jj670107.aspx\">SINGLETON<\/a> optimization hint. The <strong>SINGLETON<\/strong> hint expects that a node appears only once inside its parent node. Again, as with data type casting, there is a slight risk of failure if the <strong>SINGLETON<\/strong> hint is used where there may be more than one node (of its kind) in a parent node. <\/p>\n<p>Before we recreate our index to include our optimization hint, let&#8217;s record the current size of the index: <\/p>\n<pre class=\"lang:tsql theme:ssms2012\">--check index size\nEXEC sp_spaceused\n<\/pre>\n<p class=\"illustration center\"><img decoding=\"async\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/imported\/1729-b9782a21-e81e-4d9c-a5f6-3d52ae0a72cc.png\" alt=\"1729-b9782a21-e81e-4d9c-a5f6-3d52ae0a72c\" \/><\/p>\n<p>The current index size is <strong>1432 KB<\/strong>. We&#8217;ll now drop the selective index and recreate it, this time using an XQuery type and optimization hint: <\/p>\n<pre class=\"lang:tsql theme:ssms2012\">--drop selective XML index\nDROP INDEX SXI_Selective_SalesOrdersDetails ON SalesOrdersData\nGO\n\n--recreate selective XML index using optimization hint\nCREATE SELECTIVE XML INDEX SXI_Selective_SalesOrdersDetails \nON Sales_XML.dbo.SalesOrdersData(SalesOrdersDetail)\nFOR \n(\n    pathSalesPersonID = '\/SalesOrders\/SalesOrder\/SalesPersonID' \n\tAS XQUERY 'xs:double' SINGLETON\n)\nGO<\/pre>\n<p>Does this optimization improve our query speed? Let&#8217;s check it by running our sales order count query once again: <\/p>\n<pre class=\"lang:tsql theme:ssms2012\">--get sales order count for a given salesperson\nSELECT COUNT(*)AS SalesOrderCount\nFROM Sales_XML.dbo.SalesOrdersData\nWHERE SalesOrdersDetail.exist('\/SalesOrders\/SalesOrder\/SalesPersonID[.=276]') = 1\nGO<\/pre>\n<p class=\"illustration center\"><img decoding=\"async\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/imported\/1729-094f055a-0718-46c9-8327-97750751f968.png\" alt=\"1729-094f055a-0718-46c9-8327-97750751f96\" \/><\/p>\n<p>This shows a reduction in query time by almost <strong>another<\/strong> <strong>90%<\/strong>, down to 4 ms! We can see how important it is to tune the index by using appropriate optimization hints. A quick index size check reveals only a tiny storage cost increase (8 KB) that is offset by a huge performance gain: <\/p>\n<pre class=\"lang:tsql theme:ssms2012\">--check index size\nEXEC sp_spaceused\n<\/pre>\n<p class=\"illustration center\"><img decoding=\"async\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/imported\/1729-9331b2a0-9e1d-413e-b942-a3fd92eb926a.png\" alt=\"1729-9331b2a0-9e1d-413e-b942-a3fd92eb926\" \/><\/p>\n<h2>Adding Additional XQuery Paths to the Index <\/h2>\n<p>Suppose that we must also support slightly more complex queries that find<strong> PurchaseOrderNumbers<\/strong> for a given <strong>salesperson<\/strong>, using our existing selective index. We&#8217;ll be dealing with queries that look like this: <\/p>\n<pre class=\"lang:tsql theme:ssms2012\">--get purchase order numbers for all sales orders owned by a given salesperson\nSELECT SalesOrdersDetail.value('(\/SalesOrders\/SalesOrder\/PurchaseOrderNumber)[1]','varchar(13)') AS PurchaseOrderNumber\nFROM Sales_XML.dbo.SalesOrdersData\nWHERE SalesOrdersDetail.exist('\/SalesOrders\/SalesOrder\/SalesPersonID[.=276]') = 1\nGO<\/pre>\n<p>Before we do anything to our existing selective index, let&#8217;s see how this query performs with the current index configuration: <\/p>\n<p class=\"illustration center\"><img decoding=\"async\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/imported\/1729-9344ce8a-ab02-4886-a588-70db37f18227.png\" alt=\"1729-9344ce8a-ab02-4886-a588-70db37f1822\" \/><\/p>\n<p>Remember how we mentioned that you can handle XQuery value() method return values effectively by using SQL Server data types in a selective index path? Let&#8217;s try using a SQL Server data type here, instead of an XQuery data type. We can still use an optimization hint in conjunction with the SQL Server data type. We don&#8217;t have to start over with our index; all we have to do is add a new path: <\/p>\n<pre class=\"lang:tsql theme:ssms2012\">--add a second path specification for the selective index\n ALTER INDEX SXI_Selective_SalesOrdersDetails\nON Sales_XML.dbo.SalesOrdersData\nFOR \n(\n\tADD pathPurchaseOrderNumber = '\/SalesOrders\/SalesOrder\/PurchaseOrderNumber' \n\t\tAS SQL VARCHAR(13) SINGLETON\n)\nGO<\/pre>\n<p>You&#8217;ll have seen that we defined the SQL Server data type as <strong>VARCHAR(13).<\/strong> This is because the maximum length of any existing <strong>PurchaseOrderNumber<\/strong> is <strong>1<\/strong><strong>3<\/strong> (If we expect that there could be future data with longer <strong>PurchaseOrderNumber <\/strong>values, we would want to allow for this by using a larger <strong>VARCHAR<\/strong> size). <\/p>\n<pre class=\"lang:tsql theme:ssms2012\">--find max length of all existing purchase order numbers\nSELECT MAX(LEN(PurchaseOrderNumber)) \nFROM AdventureWorks2012.Sales.SalesOrderHeader\nGO<\/pre>\n<p class=\"illustration\"><img decoding=\"async\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/imported\/1729-26195e4e-692b-4f52-94bb-6a78faa57e48.png\" alt=\"1729-26195e4e-692b-4f52-94bb-6a78faa57e4\" \/><\/p>\n<p>Since there is always just one <strong>PurchaseOrderNumber<\/strong> node in its parent, we can use the <strong>SINGLETON <\/strong>hint once again in this new path. Now that we&#8217;ve added a second path to our selective XML index, let&#8217;s run our query again, and see if the new path specification will reduce our current query execution time (22 ms): <\/p>\n<pre class=\"lang:tsql theme:ssms2012\">--get purchase order numbers for all sales orders owned by a given salesperson\nSELECT SalesOrdersDetail.value('(\/SalesOrders\/SalesOrder\/PurchaseOrderNumber)[1]','varchar(13)') AS PurchaseOrderNumber\nFROM Sales_XML.dbo.SalesOrdersData\nWHERE SalesOrdersDetail.exist('\/SalesOrders\/SalesOrder\/SalesPersonID[.=276]') = 1\nGO<\/pre>\n<p class=\"illustration center\"><img decoding=\"async\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/imported\/1729-356ba43c-5a6b-42bb-9317-aee628cb9e89.png\" alt=\"1729-356ba43c-5a6b-42bb-9317-aee628cb9e8\" \/><\/p>\n<p>The addition of the second path specification has reduced the execution time by more than 50%. <\/p>\n<h2>Storage Savings <\/h2>\n<p>Our current index storage cost, which includes two path specifications and associated hints, is 1536 KB: <\/p>\n<pre class=\"lang:tsql theme:ssms2012\">--check index size\nEXEC sp_spaceused\n<\/pre>\n<p class=\"illustration center\"><img decoding=\"async\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/imported\/1729-fd4e66bb-a839-4668-b53c-b902ba416b62.png\" alt=\"1729-fd4e66bb-a839-4668-b53c-b902ba416b6\" \/><\/p>\n<p>Let&#8217;s take a look at what our storage cost would have been if we had used a primary XML index instead of a selective XML index. Remember, standard XML indexes shred and index the entire XML document, instead of only the specific paths that will be used for queries, as selective indexes do. Let&#8217;s replace our selective index with a primary XML index to see what our hypothetical storage savings are: <\/p>\n<pre class=\"lang:tsql theme:ssms2012\">--drop the existing selective XML index\nDROP INDEX SXI_Selective_SalesOrdersDetails ON SalesOrdersData\nGO\n\n--create a standard primary XML index\nCREATE PRIMARY XML INDEX IX_Primary_SalesOrdersDetails \nON Sales_XML.dbo.SalesOrdersData(SalesOrdersDetail)\nGO\n\n--check index size\nEXEC sp_spaceused<\/pre>\n<p class=\"illustration center\"><img decoding=\"async\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/imported\/1729-78227e35-db47-4eec-8662-e4dc71840b5b.png\" alt=\"1729-78227e35-db47-4eec-8662-e4dc71840b5\" \/><\/p>\n<p>A huge increase in storage space is seen. So, by using a selective XML index instead of an ordinary primary XML index, we&#8217;ve <strong>reduced our storage cost by over 98%<\/strong><strong>.<\/strong> <\/p>\n<h2>Requirements\/Limitations <\/h2>\n<p>Some prerequisites and limitations regarding selective XML indexes include: <\/p>\n<ul>\n<li>A clustered index and a primary key are required on the table that a selective XML index is created on. If a selective index creation is attempted on a table with no primary key, the following error will result: <\/li>\n<\/ul>\n<p class=\"illustration center\"><img decoding=\"async\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/imported\/1729-07a1a18d-7d31-4984-8862-b7c1f83fd310.png\" alt=\"1729-07a1a18d-7d31-4984-8862-b7c1f83fd31\" \/><\/p>\n<ul>\n<li>Each XML column in a table can have only one selective XML index. Conversely, a selective XML index must be created to index only one column. In other words, an XML column can have at most one selective index, and a single selective index cannot span multiple XML columns. However, a table can contain up to 249 selective indexes. <\/li>\n<\/ul>\n<h2>When Not to Use Selective XML Indexes <\/h2>\n<p><a href=\"http:\/\/msdn.microsoft.com\/en-us\/library\/jj670108.aspx\">Microsoft<\/a> recommends using selective XML indexes for most XML query operations. However, if you find that a large number of node paths need to be mapped in the selective index, you may benefit more from standard XML indexes than selective indexes. Also, selective indexes are not recommended if your queries search for unknown elements or unknown node locations. <\/p>\n<h2>Summary <\/h2>\n<p>We&#8217;ve gone over some operations involving basic uses of selective XML indexes. We&#8217;ve used examples that showed the advantages to using selective XML indexes over standard XML indexes &#8211; we saw that index storage and creation time costs are greatly reduced with selective indexes. We also implemented optimization hints, and discovered how they can considerably increase index performance. Finally, we looked into some of the basic prerequisites to using selective indexes, such as the clustered primary key requirement, and the one-selective-index-per-column limitation. <\/p>\n<\/div>\n","protected":false},"excerpt":{"rendered":"<p>Seldom has a SQL Server Service pack had such an effect on database development as when SQL Server 2012 SP1 introduced selective XML indexes. These transform the practicality of querying large amounts of  XML data. Seth Delconte demonstrates how and why this feature makes all the difference.&hellip;<\/p>\n","protected":false},"author":221920,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[143525],"tags":[4242,4168,4149,4150,4151,4217],"coauthors":[],"class_list":["post-1570","post","type-post","status-publish","format-standard","hentry","category-learn","tag-basics","tag-database","tag-learn-sql-server","tag-sql","tag-sql-server","tag-xml"],"acf":[],"_links":{"self":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/1570","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/users\/221920"}],"replies":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/comments?post=1570"}],"version-history":[{"count":4,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/1570\/revisions"}],"predecessor-version":[{"id":92140,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/1570\/revisions\/92140"}],"wp:attachment":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/media?parent=1570"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/categories?post=1570"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/tags?post=1570"},{"taxonomy":"author","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/coauthors?post=1570"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}