{"id":787,"date":"2012-07-20T22:27:00","date_gmt":"2012-07-20T22:27:00","guid":{"rendered":"http:\/\/mooredynasty.net\/?p=787"},"modified":"2014-12-18T22:27:48","modified_gmt":"2014-12-18T22:27:48","slug":"creating-an-index-on-an-xml-fragment-in-sql-server","status":"publish","type":"post","link":"https:\/\/mooredynasty.net\/index.php\/2012\/07\/creating-an-index-on-an-xml-fragment-in-sql-server\/","title":{"rendered":"Creating an Index on an XML Fragment in SQL Server"},"content":{"rendered":"

Imagine you are given a table with a column of XML in it and a few million rows of data.  Further imagine that you want to write an efficient query using an attribute of that XML in the WHERE clause of your query.<\/p>\n

A short test scenario describes how I recently dealt with the problem.  First, a basic table definition.  The XML will end up in the SomeContent field.<\/p>\n

CREATE TABLE [dbo].[MarcTest]
(
    [ID] [int] NOT NULL,
    [TypeCode] [varchar](20) NOT NULL,
    [SomeContent] [varchar](MAX) NOT NULL
CONSTRAINT [PK_MarcTest] PRIMARY KEY CLUSTERED
    ([ID] ASC)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO<\/p>\n

Now for some test data.  As in my real-life scenario, the schema of the XML data varies from row to row.<\/p>\n

INSERT INTO marctest (ID, TypeCode, SomeContent) VALUES(1, ‘CODE1’, ‘<MyDoc><MetaData><EmployeeID>13791<\/EmployeeID><Name>Marc Moore<\/Name><\/MetaData><ContentData><ContentField1>This is some content.<\/ContentField1><ContentField2>This is some additional content.<\/ContentField2><\/ContentData><\/MyDoc>’)
INSERT INTO marctest (ID, TypeCode, SomeContent) VALUES(2, ‘CODE2’, ‘<MyDoc><MetaData><EmployeeID>4<\/EmployeeID><Name>Xi Zhang<\/Name><\/MetaData><ContentData><ContentField1>This is some content.<\/ContentField1><ContentField2>This is some additional content.<\/ContentField2><\/ContentData><\/MyDoc>’)
INSERT INTO marctest (ID, TypeCode, SomeContent) VALUES(1001, ‘OTHER1’, ‘<SomeOtherData><Data1>111111<\/Data1><Data2>222222<\/Data2>333333<Data3><\/Data3><\/SomeOtherData>’)
GO<\/p>\n

It\u2019s not possible to create an index directly on an XML column\u2019s XPath expression and the XML indexes offered by SQL Server 2008 have a bad reputation for being bloated.  So what to do?  The solution that follows was blessed by David, our DBA, as a valid one.<\/p>\n

Based on this question and answer about SQL<\/a>, XML, and XPath, what I first wanted to do was create a computed column based on an XPATH expression like this one:<\/p>\n

\"image\"<\/a><\/p>\n

Unfortunately that does not work.  There are a couple of reason for this: The SomeContent column is not an XML column, which doesn\u2019t help, nor does the fact that SQL considers this expression non-deterministic.<\/p>\n

Various articles led me to create this scalar function to help solve the problem:<\/p>\n

FUNCTION MarcTestEmployeeID
(
    @SomeXml xml
)
RETURNS INT
AS
BEGIN<\/p>\n

    DECLARE @ret INT = @SomeXml.value(‘(\/MyDoc\/MetaData\/EmployeeID\/node())[1]’, ‘int’)
    IF @ret IS NULL
        SET @ret = 0
    RETURN @ret<\/p>\n

END<\/p>\n

Now SQL was happy and let me create the computed column<\/p>\n

ALTER TABLE MarcTest ADD EmployeeID as ([dbo].[MarcTestEmployeeID]([SomeContent]))<\/p>\n

Unfortunately, when I attempted to create my index:<\/p>\n

CREATE INDEX MarcTestEmployeeID_IDX ON dbo.MarcTest(TypeCode, EmployeeID)<\/p>\n

SQL became unhappy once more:<\/p>\n

Msg 2729, Level 16, State 1, Line 2
Column ‘EmployeeID’ in table ‘dbo.MarcTest’ cannot be used in an index or statistics or as a partition key because it is non-deterministic.<\/p>\n

I\u2019m sure SQL has its own reasons for doing this, but its logic was clearly incorrect as far as my function goes.  Given a blob of XML, dbo.MarcTestEmployeeID will always return the same answer for that XML; ergo, it is deterministic.  Yet SQL remained unhappy.<\/p>\n

Happily, this question and answer about deterministic scalar functions<\/a> solved the issue with a one-line change to the function definition:<\/p>\n

FUNCTION MarcTestEmployeeID
(
    @SomeXml xml
)
RETURNS INT
WITH SCHEMABINDING
<\/strong>\u2026<\/p>\n

After creating my index, my sample \u201cSELECT *\u201d query returned the following, which is just what I would expect:<\/p>\n

\"image\"<\/a><\/p>\n

Next, I inserted a couple of thousand more records to make things more interesting and ran this query, which is similar to the one I plan to run in the real world:<\/p>\n

SELECT * FROM MarcTest WHERE TypeCode = ‘CODE1’ AND EmployeeID = 13791<\/p>\n

The execution plan indicates that my new index that\u2019s built on the computed column using the UDF and XML data is being used:<\/p>\n

\"image\"<\/a><\/p>\n

Exactly what was hoped for!<\/p>\n","protected":false},"excerpt":{"rendered":"

Imagine you are given a table with a column of XML in it and a few million rows of data.  Further imagine that you want to write an efficient query using an attribute of that XML in the WHERE clause of your query. A short test scenario describes how I … <\/p>\n","protected":false},"author":6,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[15],"tags":[],"class_list":["post-787","post","type-post","status-publish","format-standard","hentry","category-development"],"_links":{"self":[{"href":"https:\/\/mooredynasty.net\/index.php\/wp-json\/wp\/v2\/posts\/787","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/mooredynasty.net\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/mooredynasty.net\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/mooredynasty.net\/index.php\/wp-json\/wp\/v2\/users\/6"}],"replies":[{"embeddable":true,"href":"https:\/\/mooredynasty.net\/index.php\/wp-json\/wp\/v2\/comments?post=787"}],"version-history":[{"count":1,"href":"https:\/\/mooredynasty.net\/index.php\/wp-json\/wp\/v2\/posts\/787\/revisions"}],"predecessor-version":[{"id":788,"href":"https:\/\/mooredynasty.net\/index.php\/wp-json\/wp\/v2\/posts\/787\/revisions\/788"}],"wp:attachment":[{"href":"https:\/\/mooredynasty.net\/index.php\/wp-json\/wp\/v2\/media?parent=787"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/mooredynasty.net\/index.php\/wp-json\/wp\/v2\/categories?post=787"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/mooredynasty.net\/index.php\/wp-json\/wp\/v2\/tags?post=787"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}