Paulo Morgado

.NET Development & Architecture

Recent Articles

view all

Events

Projects

Recent Readers

Visitor Locations

Visitor Locations

Disclaimer

The opinions and viewpoints expressed in this site are mine and do not necessarily reflect those of Microsoft, my employer or any community that I belong to. Any code or opinions are offered as is. Products or services mentioned are purchased by me, made available to me by my employer or the manufacturer/vendor which doesn't influence my opinion in any way.

Playing With SQL Server CLR Integration – Part I

I’m currently working with an application that stores a property bag in a SQL Server column like this:

[[[name1]]]
value1
[[[name2]]]
value2.1
value2.2
[[[name3]]]
value3

Don’t ask me why it’s done like this. It just is.

The application decodes this property bag into its inner structures and all works fine.

Sometimes I would like to query the database directly or do some reporting on those properties and just can’t.

So, I thought this was a good use case for SQL Server CLR Integration. I decided to build a CLR Table-Valued Function that would return the property bag as a two column table.

Parsing the property bag text can easily be achieved with a simple regular expression:

new Regex(
        string.Format(@"(?<Name>(?<=\[\[\[).*(?=\]\]\]{0}))\]\]\]{0}(?<Value>(([\s\S]*?(?={0}\[\[\[))|([\s\S]*?(?={0}$))))", Environment.NewLine),
        RegexOptions.Multiline | RegexOptions.ExplicitCapture | RegexOptions.CultureInvariant | RegexOptions.Compiled);

Ultrapico’s Expresso was a big help when developing this regular expression.

In case you don’t know, the way CLR Table-Valued Function are built is using an initial method to take the input and return an IEnumerable and row filler method the receives the enumerator item and outputs the column values.

Since these are really key-value pairs of strings, I decided to use )>) Structure" href="http://msdn.microsoft.com/library/5tbh8a42.aspx" target=_blank>KeyValuePair<string, string> instances to store each item and the enumerator became simply this:

private static IEnumerable<KeyValuePair<string, string>> ShortPropsEnumerable(string shortPropsText)
{
    return from Match m in shortPropsRegex.Matches(shortPropsText)
           select new KeyValuePair<string, string>(m.Groups["Name"].Value, m.Groups["Value"].Value);
}

And the implementation of the CLR Table-Valued Function is as simple as this:

[Microsoft.SqlServer.Server.SqlFunction(
    Name = "ShortPropsToTable",
    FillRowMethodName = "ShortPropsToTableFillRow",
    TableDefinition = "Name NVARCHAR(4000), Value NVARCHAR(4000)",
    IsDeterministic = true,
    IsPrecise = false,
    DataAccess = DataAccessKind.None,
    SystemDataAccess = SystemDataAccessKind.None)]
public static IEnumerable ShortPropsToTable(string shortPropsText)
{
    return ShortPropsEnumerable(shortPropsText);
}

public static void ShortPropsToTableFillRow(object item, out SqlChars name, out SqlChars value)
{
    KeyValuePair<string, string> shortProp = (KeyValuePair<string, string>)item;

    name = new SqlChars(shortProp.Key);
    value = new SqlChars(shortProp.Value);
}

To use this in SQL Server a few simple steps are need:

  1. Load the assembly into the database:
    CREATE ASSEMBLY [MyAssembly]
    AUTHORIZATION [dbo]
    FROM '...\MyAssembly.dll'
    WITH PERMISSION_SET = SAFE
    GO
  2. CREATE FUNCTION [dbo].[ShortPropsToTable](@shortPropsText [nvarchar](4000))
    RETURNS  TABLE (
        [Name] [nvarchar](4000) NULL,
        [Value] [nvarchar](4000) NULL
    ) WITH EXECUTE AS CALLER
    AS 
    EXTERNAL NAME [MyAssembly].[ShortProps].[ShortPropsToTable]
    GO
    
  3. Enable CLR Integration:
    EXEC sp_configure 'clr enabled', 1
    GO
    RECONFIGURE
    GO
    

And all is set to go.

Now I can just query the property bag as a table:

SELECT
    e.[ID],
    e.[Name],
    e.[Class],
    e.[Type],
    p.[Name],
    p.[Value]
FROM
    dbo.Entity as e
    CROSS APPLY dbo.ShortPropsToTable(e.[ShortProps]) as p

Just for curiosity, for a little over 50000 entities (that can be retrieved on about 1 second on my laptop), I got a little under 630000 properties in les then 40 seconds.

40 seconds might seem a lot compared to the 1 second, but I would like to see better times using T-SQL. And develop and test the TVF in just a couple of hours.

Now, if only he DBAs would allow me to use it. It doesn’t fit into the company’s “security policy”, you know.

Comments

riaz said:

nice job dude got cleared on concepts of clr sqlfunctions,really it helped me a lot

# November 4, 2009 6:20 AM