Building Indexes on Views based on XML

When dealing with XML columns, you can improve search times by building indexes on the values extracted from the XML.  The index persists the value for searches so there is no need to re-extract the value until you actually retrieve the record after finding it.

 

Tonight, I was helping a friend and it required a view from an inner join of two different tables and getting values from each table’s XML to be coalesce. So the logical solution is to create a VIEW and then index the xml values. I could create the columns for each value and proceed that way – but it consumes  resources that are not absolutely required.

 

Wait! You cannot create indexes on views containing XML data type methods. If you try, you will get this error message:

Msg 1985, Level 16, State 1, Line 1
Cannot create index on view 'VerraTerraDatabase.dbo.CurrentData2'. It contains one or more XML data type methods.

 

i.e. If you try something like:

Create view CurrentData WITH SCHEMABINDING
as
SELECT   
    P.ListingNumber, 
    Coalesce( U.received,P.received) as Received,
    case when U.received =P.received then 'Original' else 'Updated' end as Status,
    coalesce(u.Data.value('(//Property/@APN)[1]', 'nvarchar(50)'),p.Data.value('(//Property/@APN)[1]', 'nvarchar(50)')) as APN,
    coalesce( u.Data.value('(//Property/@matrix_unique_id)[1]', 'int'),p.Data.value('(//Property/@matrix_unique_id)[1]', 'int')) as matrix_unique_id
    
FROM         dbo.PrimaryXml P 
INNER JOIN
dbo.UpdateXml  U
ON P.ListingNumber = U.ListingNumber
and VersionNo=0
 
go
CREATE UNIQUE CLUSTERED INDEX PK_ListingNumber ON CurrentData (ListingNumber)

 

However the code below will work and create indexes on XML values through the use of functions.

Create view CurrentData WITH SCHEMABINDING
as
SELECT   
    P.ListingNumber, 
    Coalesce( U.received,P.received) as Received,
    case when U.received =P.received then 'Original' else 'Updated' end as Status,
    coalesce(dbo.GetAPN(u.Data),dbo.GetAPN(p.Data)) as APN,
    coalesce(dbo.GetMatrixUniqueId( u.Data), dbo.GetMatrixUniqueId(p.Data)) as matrix_unique_id
    
FROM         dbo.PrimaryXml P 
INNER JOIN
                      dbo.UpdateXml  U
                      ON P.ListingNumber = U.ListingNumber
and VersionNo=0
 
go
CREATE UNIQUE CLUSTERED INDEX PK_ListingNumber ON CurrentData (ListingNumber)
go
CREATE  INDEX PK_Apn ON CurrentData (APN)
go
CREATE  INDEX PK_matrix_unique_id ON CurrentData (matrix_unique_id)
go

What we do is create a function wrapper, for example this lean definition:

Create FUNCTION [dbo].[GetAPN] 
(
    @Data xml
)
RETURNS nvarchar(50)
WITH SCHEMABINDING
AS
BEGIN
    return @Data.value('(//Property/@APN)[1]', 'nvarchar(50)')
END

 

Result: I have a join on two tables containing XML columns with the coalesce of values from the xml in each table being indexed. For most of you this is not likely something you need often, but it can simplify a data model.

Comments

Popular posts from this blog

Yet once more into the breech (of altered programming logic)

Simple WP7 Mango App for Background Tasks, Toast, and Tiles: Code Explanation

How to convert SVG data to a Png Image file Using InkScape