Extracting ExtendedAttributes via SQL

In Community Server, several of our classes derive from the ExtendedAttributes class.  This class creates a list of name value pairs on the class, and is picked off by our data layer and stored into two NTEXT columns in the table, PropertyNames, and PropertyValues.  This pattern is derived directly from the way that the ASPNET membership system implements this capability in the ASPNET_PROFILE table.  It allows you to define, and persist custom key value data with an item in the database without need to make schema changes.  Whats good about this, is its easy for a developer to save and retrieve custom pieces of information about the object, whats bad is its stored in an unfriendly way in the database and is very difficult to query against.

SELECT PropertyNames, PropertyValues FROM cs_Sections WHERE ApplicationKey = 'sample'

yieds a result of

In recently working with this, I wrote a bit of SQL to help extract a value from the PropertyValues column where you know the specific key.  I use this in a SQL script to populate a temp table which I can then join against; In this case I am trying to extract the section owner, which happens to be at the beginning of this property value collection, but doesnt need to be... 

This SQL will extract the Owner Name and the Primary Key which in this case is a column called SectionID

DECLARE @PropertyString NVARCHAR(50)
DECLARE @PropertyDelimiter NVARCHAR(50)

SET @PropertyString = '%SectionOwners:S:%'
SET @PropertyDelimiter = '%:%'

This code basically finds all the rows that have the property, the property is defined as [PropertyName]:[DataType]:[Begin]:[Length] or ( SectionOwners:S:0:5 ) so the first big SUBSTRING, is getting the first integer after the PropertyName:Datatype:, and the second is getting the next integer after the first, which is the length... (the only real assumption in the above code, is that the combined digits of the [begin]:[length] cannot exceed 100...  I am grabbing only the first 100 cars after the PropertyString to find the integers in)

While my tests on SQL2005 seem to indicate that this is not a horrible query performance wise, I wouldn't want to constantly be doing this type of string manipulation, So I would only recommend this for reporting, special one off queries, or to use on a relatively infrequent process, like item save, to peel off the custom data and put it in an indexable location.