One of the most prominent enhancements to SQL Server 2005 was the introduction of the integrated SQL Common Language Runtime, that was named SQL CLR at that time. What is now called CLR integration is an SQL Server-specific version of the .NET Common Language Runtime, which allows you to run .NET managed code in the database. CLR integration programming is a broad subject that could easily fill an entire book, and in fact it does—Pro SQL Server 2005 Assemblies, by Robin Dewson and Julian Skinner (Apress, 2005), is an excellent resource for in-depth coverage of CLR integration programming. In this chapter, we’ll discuss the methods used to extend SQL Server functionality in the past, and explain the basics of the CLR integration programming model in SQL Server 2012.

The Old Way

In versions of SQL Server prior to the 2005 release, developers could extend SQL Server functionality by writing extended stored procedures (XPs). Writing high-quality XPs required a strong knowledge of the Open Data Services (ODS) library and the poorly documented C-style Extended Stored Procedure API. Anyone who attempted the old style of XP programming can tell you it was a complex undertaking, in which a single misstep could easily result in memory leaks and corruption of the SQL Server process space. Additionally, the threading model used by XPs required SQL Server to rely on the operating system to control threading within the XP. This could lead to many issues, such as unresponsiveness of XP code.

Earlier SQL Server releases also allowed you to create OLE Automation server objects via the spOACreate SP. Creating OLE Automation servers can be complex and awkward as well. OLE Automation servers created with spOACreate can result in memory leaks and in some instances corruption of the SQL Server process space.

Another option in previous versions of SQL Server was to code all business logic exclusively in physically separate business objects. While this method is preferred by many developers and administrators, it can result in extra network traffic and a less robust security model than can be achieved through tight integration with the SQL Server security model.

The CLR Integration Way

The CLR integration programming model provides several advantages over older methods of extending SQL Server functionality via XPs, OLE Automation, or external business objects. These advantages include the following:

• A managed code base that runs on the CLR integration .NET Framework is managed by the SQL Server Operating System (SQL OS). This means that SQL Server can properly manage threading, memory usage, and other resources accessed via CLR integration code.
• Tight integration of the CLR into SQL Server means that SQL Server can provide a robust security model for running code, and maintain stricter control over database objects and external resources accessed by CLR code.
• CLR integration is more thoroughly documented in more places than the Extended Stored Procedure API ever was (or presumably ever will be).
• CLR integration does not tie you to the C language-based Extended Stored Procedure API. In theory, the .NET programming model does not tie you to any one specific language (although you cannot use dynamic languages like IronPython in CLR integration).
• CLR integration allows access to the familiar .NET namespaces, data types, and managed objects, easing development.
• CLR integration introduces SQL Server-specific namespaces that allow direct access to the underlying SQL Server databases and resources, which can be used to limit or reduce network traffic generated by using external business objects.

There’s a misperception expressed by some that CLR integration is a replacement for T-SQL altogether. CLR integration is not a replacement for T-SQL, but rather a supplement that works hand in hand with T-SQL to make SQL Server 2012 more powerful than ever. So when should you use CLR code in your database? There are no hard and fast rules concerning this, but here are some general guidelines:

• Existing custom XPs on older versions of SQL Server are excellent candidates for conversion to SQL Server CLR integration assemblies—that is, if the functionality provided isn’t already part of SQL Server 2012 T-SQL (e.g., encryption).
• Code that accesses external server resources, such as calls to xpcmdshell, are also excellent candidates for conversion to more secure and robust CLR assemblies.
• T-SQL code that performs lots of complex calculations and string manipulations can make strong candidates for conversion to CLR integration assemblies.
• Highly procedural code with lots of processing steps might be considered for conversion.
• External business objects that pull a lot of data across the wire and perform a lot of processing on that data might be considered for conversion. You might first consider these business objects for conversion to T-SQL SPs, especially if they don’t perform a lot of processing on the data in question.

On the flip side, here are some general guidelines for items that should not be converted to CLR integration assemblies:

• External business objects that pull relatively little data across the wire, or that pull a lot of data across the wire but perform little processing on that data, are good candidates for conversion to T-SQL SPs instead of CLR assemblies.
• T-SQL code and SPs that do not perform many complex calculations or string manipulations generally won’t benefit from conversion to CLR assemblies.
• T-SQL can be expected to always be faster than CLR integration for set-based operations on data stored in the database.
• You might not be able to integrate CLR assemblies into databases that are hosted on an ISP’s (Internet Service Provider’s) server, if the ISP didn’t allow CLR integration at the database server level. This is mainly for security reasons and because there can be less control of the code within an assembly.
• CLR integration is not supported on the SQL Azure platform.

As with T-SQL SPs, the decision on whether and to what extent CLR integration will be used in your databases depends on your needs, including organizational policies and procedures. The recommendations we have presented here are guidelines of instances that can make good business cases for conversion of existing code and creation of new code.

CLR Integration Assemblies

CLR integration exposes .NET managed code to SQL Server via assemblies. An assembly is a compiled .NET managed code library that can be registered with SQL Server using the CREATE ASSEMBLY statement. Publicly accessible members of classes within the assemblies are then referenced in the appropriate CREATE statements, which we will describe later in this chapter. Creating a CLR integration assembly requires:

1. Designing and programminxg .NET classes that publicly expose the appropriate members.
2. Compiling the .NET classes into managed code DLL manifest files containing the assembly.
3. Registering the assemblies with SQL Server via the CREATE ASSEMBLY statement.
4. Registering the appropriate assembly members via the appropriate CREATE FUNCTION, CREATE PROCEDURE, CREATE TYPE, CREATE TRIGGER, or CREATE AGGREGATE statements.

CLR integration provides additional SQL Server-specific namespaces, classes, and attributes to facilitate development of assemblies. Visual Studio 2010 and Visual Studio 11 (in beta at the time of this writing) also include an SQL Server project type that assists in quickly creating assemblies. Also, to maximize your SQL Server development possibilities with Visual Studio, install the SQL Server Data Tools (SSDT) from the Microsoft Data Developer Center website (http://msdn.microsoft.com/en-us/data/tools.aspx) which provide an integrated environment for database developers inside Visual Studio by allowing you to create and manage database objects and data and to execute T-SQL queries directly.

Perform the following steps to create a new assembly using Visual Studio 2010:

1. Select File New Project from the menu.
2. Go to Database SQL Server in the Installed Templates list and select either Visual Basic CLR Database Project or Visual C# CLR Database Project, as shown in Figure 14-1. Make sure you target the .NET Framework 4 as it is the version of the SQL Server 2012 CLR Integration.

   

   Figure 14-1.  Visual Studio 2010 New Project Dialog Box

3. You will be prompted with a dialog to select a database connection for the project, as shown in Figure 14-2. You may be prompted to turn on CLR integration debugging for the connection. This is required if you want to test your assemblies in debug mode, which involves remote debugging handled by remote debugging components on the SQL Server communicating with the Visual Studio host. You might need to take extra steps to configure your firewall for it to work. Refer to Set Up Remote Debugging in the Visual Studio online help (http://msdn.microsoft.com/en-us/library/bt727f1t.aspx).

   

   Figure 14-2.  The Add Database Reference Dialog Box

4. Next, highlight the project name in the Solution Explorer and right-click. Then choose a type of CLR integration item to add to the solution (User-Defined Function, Stored Procedure, etc.), as shown in Figure 14-3.

   

   Figure 14-3.  Adding a New CLR Integration Class to Your Project

5. Visual Studio will show you an “Add New Item” dialog box in which you can change the name of the item, and then automatically generate a template for the item you select in the language of your choice, complete with the appropriate Imports statements in VB.NET or using in C#.

In addition to the standard .NET namespaces and classes, CLR integration implements some SQL Server-specific namespaces and classes to simplify interfacing your code with SQL Server. Some of the most commonly used namespaces include the following:

• The System namespace, which includes the base .NET data types and the Object base class from which all .NET classes inherit.
• The System.Data namespace, which contains the DataSet class and other classes for ADO.NET data management.
• The System.Data.SqlClient namespace, which contains the SQL Server-specific ADO.NET data provider.
• The System.Data.SqlTypes namespace, which contains SQL Server data types. This is important because (unlike the standard .NET data types) these types can be set to SQL NULL and are defined to conform to the same operator rules, behaviors, precision, and scale as their SQL Server type counterparts.
• The Microsoft.SqlServer.Server namespace, which contains the SqlContext and SqlPipe classes that allow assemblies to communicate with SQL Server.

Once the assembly is created and compiled, it is registered with SQL Server via the CREATE ASSEMBLY statement. Listing 14-1 demonstrates a CREATE ASSEMBLY statement that registers a CLR integration assembly with SQL Server from an external DLL file. The DLL file used in the example is not supplied in precompiled form in the sample downloads for this book, but you can compile it yourself from the code we will introduce in Listing 14-2. Source code is included in the sample downloads available on the Apress web site. As CLR integration is not enabled by default, we also need to enable it at the server level. Here, we do it using the sp_configure system stored procedure prior to running the CREATE ASSEMBLY statement. CREATE ASSEMBLY would succeed even if CLR integration is disabled; an error would be raised by SQL Server only when a CLR integration code module would be called by a user later on. The RECONFIGURE statement applies the configuration change immediately.

Listing 14-1.  Registering a CLR Integration Assembly with SQL Server

EXEC sp_configure 'CLR Enabled';
RECONFIGURE;
  
CREATE ASSEMBLY ApressExamples
AUTHORIZATION dbo
FROM N'C:MyApplicationApress.Examples.DLL'
WITH PERMISSION_SET = SAFE;
GO

The CREATE ASSEMBLY statement in the example specifies an assembly name of EmailUDF. This name must be a valid SQL Server identifier, and it must be unique within the database. You will use this assembly name when referencing the assembly in other statements.

The AUTHORIZATION clause specifies the owner of the assembly, in this case dbo. If you leave out the AUTHORIZATION clause, it defaults to the current user.

The FROM clause in this example specifies the full path to the external DLL file. Alternatively, you can specify a varbinary value instead of character file name. If you use a varbinary value, SQL Server uses it, as it is a long binary string representing the compiled assembly code, and no external file needs to be specified.

Finally, the WITH PERMISSION_SET clause grants a set of Code Access Security (CAS) permissions to the assembly. Valid permission sets include the following:

• The SAFE permission set is the most restrictive, preventing the assembly from accessing system resources outside of SQL Server. SAFE is the default.
• EXTERNAL_ACCESS allows assemblies to access some external resources, such as files, network, the registry, and environment variables.
• UNSAFE permission allows assemblies unlimited access to external resources, including the ability to execute unmanaged code.

After the assembly is installed, you can use variations of the T-SQL database object creation statements (e.g., CREATE FUNCTION, CREATE PROCEDURE) to access the methods exposed by the assembly classes. We will demonstrate these statements individually in the following sections.

User-Defined Functions

CLR integration UDFs that return scalar values are similar to standard .NET functions. The primary differences with standard .NET functions are that the SqlFunction attribute must be applied to the main function of CLR integration functions if you are using Visual Studio to deploy your function or if you need to set additional attribute values like IsDeterministic and DataAccess. Listing 14-2 demonstrates a scalar UDF that accepts an input string value and a regular expression pattern and returns a bit value indicating a match (1) or no match (0). The UDF is named EmailMatch() and is declared as a method of the UDFExample class, inside the Apress.Example namespace that we will use for all our examples in this chapter.

Listing 14-2.  Regular Expression Match UDF

using System.Data.SqlTypes;
using System.Text.RegularExpressions;
  
namespace Apress.Examples
{
    public static class UDFExample
    {
        private static readonly Regex email_pattern = new Regex
        (
        //  Everything  before  the  @  sign  (the  "local  part")
        "^[a-z0-9!#$%&'*+/=?^_'{|}  ∼  −]  +  (?:\.[a-z0-9!#$%&'*+/=?^_'{|}  ∼  −]+)*" +
  
        //  Subdomains  after  the  @  sign
        "@(?:[a-z0-9](?:[a-z0-9-]*[a-z0-9])?\.)  +  " +
  
        // Top-level domains
        "(?:[a-z]{2}|com|org|net|gov|mil|biz|info|mobi|name|aero|jobs|museum)\b$"
        );
        [Microsoft.SqlServer.Server.SqlFunction
        (
        IsDeterministic  =  true
        )]
        public static SqlBoolean EmailMatch(SqlString input)
        {
            SqlBoolean result = new SqlBoolean();
            if  (input.IsNull)
                result  =  SqlBoolean.Null;
            else
                result  =  (email_pattern.IsMatch(input.Value.ToLower()) == true)
                ?  SqlBoolean.True : SqlBoolean.False;
            return result;
        }
    }
}

The first part of the listing specifies the required namespaces to import. This UDF uses the System.Data.SqlTypes and System.Text.RegularExpressions namespaces.

using System.Data.SqlTypes;
using System.Text.RegularExpressions;

The UDFExample class and the EmailMatch function it exposes are both declared static. CLR integration functions need to be declared as static. A static function is shared between all instances of the class. Here, the class itself is also static, so it cannot be instantiated; this allows the class to be loaded faster and its memory to be shared between SQL Server sessions. The function is decorated with the Microsoft.SqlServer.Server.SqlFunction attribute with the IsDeterministic property set to true to indicate the function is a deterministic CLR integration method. The function body is relatively simple. It accepts an SqlString input string value. If the input string is NULL, the function returns NULL; otherwise the function uses the .NET Regex.IsMatch function to perform a regular expression match. If the result is a match, the function returns a bit value of 1; otherwise it returns 0.

    public static class UDFExample
    {
        private static readonly Regex email_pattern = new Regex
        (
        //  Everything  before  the  @  sign  (the  "local  part")
        "^[a-z0-9!#$%&'*+/=?^_'{|}  ∼  −]  +  (?:\.[a-z0-9!#$%&'*+/=?^_'{|}  ∼  −]+)*" +
  
        //  Subdomains  after  the  @  sign
        "@(?:[a-z0-9](?:[a-z0-9-]*[a-z0-9])?\.)  +  " +
  
        // Top-level domains
        "(?:[a-z]{2}|com|org|net|gov|mil|biz|info|mobi|name|aero|jobs|museum)\b$"
        );
  
        [Microsoft.SqlServer.Server.SqlFunction
        (
        IsDeterministic  =  true
        )]
        public static SqlBoolean EmailMatch(SqlString input)
        {
            SqlBoolean result = new SqlBoolean();
            if  (input.IsNull)
                result  =  SqlBoolean.Null;
            else
                result  =  (email_pattern.IsMatch(input.Value.ToLower()) == true)
                ?  SqlBoolean.True : SqlBoolean.False;
            return result;
        }
    }

The regular expression pattern used in Listing 14-2 was created by Jan Goyvaerts of Regular-Expressions.info (www.regular-expressions.info). Jan’s regular expression validates e-mail addresses according to RFC 2822, the standard for e-mail address formats. While not perfect, Jan estimates that this regular expression matches over 99 percent of “e-mail addresses in actual use today.” Performing this type of e-mail address validation using only T-SQL statements would be cumbersome, complex, and inefficient.

After the assembly is installed via the CREATE ASSEMBLY statement we wrote in Listing 14-1, the function is created with the CREATE FUNCTION statement using the EXTERNAL NAME clause, as shown in Listing 14-3.

Listing 14-3.  Creating CLR UDF from Assembly Method

CREATE FUNCTION dbo.EmailMatch (@input nvarchar(4000))
RETURNS bit
WITH EXECUTE AS CALLER
AS
EXTERNAL NAME ApressExamples.[Apress.Examples.UDFExample].EmailMatch
GO

After this, the CLR function can be called like any other T-SQL UDF, as shown in Listing 14-4. The results are shown in Figure 14-4.

Listing 14-4.  Validating E-mail Addresses with Regular Expressions

SELECT
    '[email protected]' AS Email,
    dbo.EmailMatch (N'[email protected]') AS Valid
UNION
SELECT
    '123@456789',
    dbo.EmailMatch('123@456789')
UNION
    SELECT '[email protected]',

    dbo.EmailMatch('[email protected]'),

As we mentioned previously, CLR UDFs also allow tabular results to be returned to the caller. This example demonstrates another situation in which CLR integration can be a useful supplement to T-SQL functionality—accessing external resources such as the file system, network resources, or even the Internet. Listing 14-5 uses a CLR function to retrieve the Yahoo Top News Stories RSS feed and return the results as a table. Table-valued CLR UDFs are a little more complex than scalar functions. The following code could be added to the sqme Visual Studio project that we created for the first CLR function example. Here we create another class named YahooRSS.

Listing 14-5.  Retrieving Yahoo RSS Feed Top News Stories

using System;
using System.Collections;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
using System.Xml;
  
namespace Apress.Examples {
    public partial class YahooRSS {
  
        [Microsoft.SqlServer.Server.SqlFunction (
            IsDeterministic = false,
            DataAccess = DataAccessKind.None,
            TableDefinition = "title nvarchar(256),"
            + "link nvarchar(256), "
            + "pubdate datetime, "
            + "description nvarchar(max)",
            FillRowMethodName = "GetRow" )
        ]
        public static IEnumerable GetYahooNews() {
            XmlTextReader xmlsource =
                new XmlTextReader("http://rss.news.yahoo.com/rss/topstories");
            XmlDocument newsxml = new XmlDocument();
            newsxml.Load(xmlsource);
            xmlsource.Close();
            return newsxml.SelectNodes("//rss/channel/item");
        }
  
        private static void GetRow (
            Object o,
            out SqlString title,
            out SqlString link,
            out SqlDateTime pubdate,
            out SqlString description )
        {
            XmlElement element = (XmlElement)o;
            title = element.SelectSingleNode("./title").InnerText;
            link = element.SelectSingleNode("./link").InnerText;
            pubdate = DateTime.Parse(element.SelectSingleNode("./pubDate").InnerText);
            description = element.SelectSingleNode("./description").InnerText;
        }
    }
}

Before we step through the source listing, we need to address security since this function accesses the Internet. Because the function needs to access an external resource, it requires EXTERNAL_ACCESS permissions. In order to deploy a non-SAFE assembly, one of two sets of conditions must be met:

• The database must be marked TRUSTWORTHY and the user installing the assembly must have EXTERNAL ACCESS ASSEMBLY or UNSAFE ASSEMBLY permission; or
• The assembly must be signed with an asymmetric key or certificate associated with a login that has proper permissions.

To meet the first set of requirements:

1. Execute the ALTER DATABASE AdventureWorks SET TRUSTWORTHY ON; statement.
2. In Visual Studio, select Project Properties Database and change the permission level to EXTERNAL_ACCESS.
3. If you manually import the assembly into SQL Server, specify the EXTERNAL_ACCESS permission set when issuing the CREATE ASSEMBLY statement as shown in Listing 14-6.

Listing 14-6.  CREATE ASSEMBLY with EXTERNAL_ACCESS Permission Set

CREATE ASSEMBLY ApressExample
AUTHORIZATION dbo
FROM N'C:MyApplicationApress.Example.DLL'
WITH PERMISSION_SET = EXTERNAL_ACCESS;

As mentioned previously, signing assemblies is beyond the scope of this book. Additional information on signing assemblies can be found in this MSDN Data Access Technologies blog entry: http://blogs.msdn.com/b/dataaccesstechnologies/archive/2011/10/29/deploying-sql-clr-assembly-using-asymmetric-key.aspx.

The code listing begins with the using statements. This function requires the addition of the System.Xml namespace in order to parse the RSS feed, and the System.Collections namespace to allow the collection to be searched amongst other functionality specific to collections.

using System;
using System.Collections;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
using System.Xml;

The primary public function again requires that the SqlFunction attribute be declared. This time there are several additional attributes that need to be declared with it:

        [Microsoft.SqlServer.Server.SqlFunction (
            IsDeterministic = false,
            DataAccess = DataAccessKind.None,
            TableDefinition = "title nvarchar(256),"
            + "link nvarchar(256), "
            + "pubdate datetime, "
            + "description nvarchar(max)",
            FillRowMethodName = "GetRow" )
        ]
        public static IEnumerable GetYahooNews()
        {
            XmlTextReader xmlsource =
                new XmlTextReader("http://rss.news.yahoo.com/rss/topstories");
            XmlDocument newsxml = new XmlDocument();
            newsxml.Load(xmlsource);
            xmlsource.Close();
            return newsxml.SelectNodes("//rss/channel/item");
        }

We specifically set the IsDeterministic attribute to false this time to indicate that the contents of an RSS feed can change between calls, making this UDF nondeterministic. Since the function does not read data from system tables using the in-process data provider, the DataAccess attribute is set to DataAccessKind.None. This CLR TVF also sets the additional TableDefinition attribute defining the structure of the result set for Visual Studio. It also needs the FillRowMethodName attribute to designate the fill-row method. The fill-row method is a user method that converts each element of an IEnumerable object into an SQL Server result set row.

The public function is declared to return an IEnumerable result. This particular function opens an XmlTextReader that retrieves the Yahoo Top News Stories RSS feed and stores it in an XmlDocument. The function then uses the SelectNodes method to retrieve news story summaries from the RSS feed. The SelectNodes method, used to return results from the function, generates an XmlNodeList. The XmlNodeList class implements the IEnumerable interface. This is important since the fill-row method is fired once for each object returned by the IEnumerable collection returned (in this case the XmlNodeList).

The GetRow method is declared as a C# void function, which means that no value will be returned by the function; the method communicates with SQL Server via its out parameters. The first parameter is an Object passed by value—in this case an XmlElement. The remaining parameters correspond to the columns of the result set. The GetRow method casts the first parameter to an XmlElement (the parameter cannot be directly an XmlElement because the fill-row method signature must have an Object as first parameter). It then uses the SelectSingleNode method and InnerText property to retrieve the proper text from individual child nodes of the XmlElement, assigning each to the proper columns of the result set along the way.

        private static void GetRow (
            Object o,
            out SqlString title,
            out SqlString link,
            out SqlDateTime pubdate,
            out SqlString description )
        {
            XmlElement element = (XmlElement)o;
            title = element.SelectSingleNode("./title").InnerText;
            link = element.SelectSingleNode("./link").InnerText;
            pubdate = DateTime.Parse(element.SelectSingleNode("./pubDate").InnerText);
            description = element.SelectSingleNode("./description").InnerText;
        }

The CLR TVF can be called with a SELECT query, as shown in Listing 14-7. The results are shown in Figure 14-5.

Listing 14-7.  Querying a CLR Integration TVF

CREATE FUNCTION dbo.GetYahooNews()
RETURNS TABLE(title nvarchar(256), link nvarchar(256), pubdate datetime, description nvarchar(max))
AS EXTERNAL NAME ApressExamples.[Apress.Examples.YahooRSS].GetYahooNews
GO
  
SELECT
    title,
    link,
    pubdate,
    description

FROM dbo.GetYahooNews();

Stored Procedures

CLR integration SPs provide an alternative to extend SQL Server functionality when T-SQL SPs just won’t do. Of course, like other CLR integration functionality, there is a certain amount of overhead involved with CLR SPs, and you can expect them to be less efficient than comparable T-SQL code for set-based operations. On the other hand, if you need to access .NET functionality or external resources, or if you have code that is computationally intensive, CLR integration SPs can provide an excellent alternative to straight T-SQL code.

Listing 14-8 shows how to use CLR integration to retrieve operating system environment variables and return them as a recordset via an SP. In the Apress.Examples namespace, we create a SampleProc class.

Listing 14-8.  Retrieving Environment Variables with a CLR Stored Procedure

using System;
using System.Collections;
using System.Data;
  
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
namespace Apress.Examples
{
    public partial class SampleProc
    {
        [Microsoft.SqlServer.Server.SqlProcedure()]
        public static void GetEnvironmentVars()
        {
            try
            {
                SortedList environment_list = new SortedList();
                foreach (DictionaryEntry de in Environment.GetEnvironmentVariables())
                {
                    environment_list[de.Key] = de.Value;
                }
  
                SqlDataRecord record = new SqlDataRecord (
                    new SqlMetaData("VarName", SqlDbType.NVarChar, 1024),
                    new SqlMetaData("VarValue", SqlDbType.NVarChar, 4000)
                );
                SqlContext.Pipe.SendResultsStart(record);
                foreach (DictionaryEntry de in environment_list)
                {
                    record.SetValue(0, de.Key);
                    record.SetValue(1, de.Value);
                    SqlContext.Pipe.SendResultsRow(record);
                }
  
                SqlContext.Pipe.SendResultsEnd();
            }
            catch (Exception ex)
            {
                SqlContext.Pipe.Send(ex.Message);
            }
        }
    }
};

As with the previous CLR integration examples, appropriate namespaces are imported at the top:

using System;
using System.Collections;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;

The GetEnvironmentVars() method is declared as a public void function. The SqlProcedure() attribute is applied to the function in this code to indicate to Visual Studio that this is a CLR SP. The body of the SP is wrapped in a try . . . catch block to capture any .NET exceptions, which are returned to SQL Server. If an exception occurs in the .NET code, it’s sent back to SQL Server via the SqlContext.Pipe.Send method.

    public partial class SampleProc
    {
        [Microsoft.SqlServer.Server.SqlProcedure()]
        public static void GetEnvironmentVars()
        {
            try
            {
                ...
            }
            catch (Exception ex)
            {
                SqlContext.Pipe.Send(ex.Message);
            }
        }
    }
};

Creating a Simple UDA

The sample UDA in Listing 14-10 determines the statistical range for a set of numbers. The statistical range for a given set of numbers is the difference between the minimum and maximum values for the set. The UDA determines the minimum and maximum values of the set of numbers passed in and returns the difference.

Listing 14-10.  Sample Statistical Range UDA

using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
  
namespace Apress.Examples {
    [Serializable]
    [Microsoft.SqlServer.Server.SqlUserDefinedAggregate(Format.Native)]
  
    public struct Range
    {
        SqlDouble min, max;
        public void Init() {
            min = SqlDouble.Null;
            max = SqlDouble.Null;
        }
  
        public void Accumulate(SqlDouble value)
        {
            if (!value.IsNull) {
                if (min.IsNull || value < min)
                {
                    min = value;
                }
  
                if (max.IsNull || value > max)
                {
                    max = value;
                }
            }
        }
  
        public void Merge(Range group)
        {
            if (min.IsNull || (!group.min.IsNull && group.min < min))
            {
                min = group.min;
            }
            if (max.IsNull || (!group.max.IsNull && group.max > max))
            {
                max = group.max;
            }
        }
  
        public SqlDouble Terminate() {
            SqlDouble result = SqlDouble.Null;
            if (!min.IsNull && !max.IsNull)
            {
                result = max - min;
            }
  
            return result;
        }
    }
}

This UDA begins, like the previous CLR integration assemblies, by importing the proper namespaces:

using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;

Next, the code declares the struct that represents the UDA. The attributes Serializable and SqlUserDefinedAggregate are applied to the struct. We used the Format.Native serialization format for this UDA. Because this is a simple UDA, Format.Native will provide the best performance and will be the easiest to implement. More complex UDAs that use reference types require Format.UserDefined serialization and must implement the IBinarySerialize interface.

[Serializable]
[Microsoft.SqlServer.Server.SqlUserDefinedAggregate(Format.Native)]
public struct Range
{
}

The struct declares two member variables, min and max, which will hold the minimum and maximum values encountered during the aggregation process:

SqlDouble min, max;

The mandatory Init() method in the aggregate body initializes the min and max member variables to SqlDouble.Null:

public void Init() {
    min = SqlDouble.Null;
    max = SqlDouble.Null;
}

The Accumulate() method accepts a SqlDouble parameter. This method first checks that the value is not NULL (NULL is ignored during aggregation). Then it checks to see if the value passed in is less than the min variable (or if min is NULL), and if so, assigns the parameter value to min. The method also checks max and updates it if the parameter value is greater than max (or if max is NULL). In this way, the min and max values are determined on the fly as the query engine feeds values into the Accumulate() method.

public void Accumulate(SqlDouble value)
        {
            if (!value.IsNull) {
                if (min.IsNull || value < min)
                {
                    min = value;
                }
  
                if (max.IsNull || value > max)
                {
                    max = value;
                }
            }
        }

The Merge() method merges a Range structure that was created in parallel with the current structure. The method accepts a Range structure and compares its min and max variables to those of the current Range structure. It then adjusts the current structure’s min and max variables based on the Range structure passed into the method, effectively merging the two results.

        public void Merge(Range group)
        {
            if (min.IsNull || (!group.min.IsNull && group.min < min))
            {
                min = group.min;
            }
            if (max.IsNull || (!group.max.IsNull && group.max > max))
            {
                max = group.max;
            }
        }

The final method of the UDA is the Terminate() function, which returns an SqlDouble result. This function checks for min or max results that are NULL. The UDA returns NULL if either min or max is NULL. If neither min nor max is NULL, the result is the difference between the max and min values.

        public SqlDouble Terminate() {
            SqlDouble result = SqlDouble.Null;
            if (!min.IsNull && !max.IsNull)
            {
                result = max - min;
            }
  
            return result;
        }

Listing 14-11 is a simple test of this UDA. The test determines the statistical range of unit prices that customers have paid for AdventureWorks products. Information like this, on a per-product or per-model basis, can be paired with additional information to help the AdventureWorks sales teams set optimal price points for their products. The results are shown in Figure 14-7.

Listing 14-11.  Retrieving Statistical Ranges with UDA

CREATE AGGREGATE Range (@value float) RETURNS float
EXTERNAL NAME ApressExamples.[Apress.Examples.Range];
GO
  
SELECT
    ProductID,
    dbo.Range(UnitPrice) AS UnitPriceRange
FROM Sales.SalesOrderDetail
WHERE UnitPrice > 0

GROUP BY ProductID;

Creating an Advanced UDA

You can create more advanced CLR aggregates that use reference data types and user-defined serialization. When creating a UDA that uses reference (nonvalue) data types such as ArrayLists, SortedLists, and Objects, CLR integration imposes the additional restriction that you cannot mark the UDA for Format.Native serialization. Instead these aggregates have to be marked for Format.UserDefined serialization, which means that the UDA must implement the IBinarySerialize interface, including both the Read and Write methods. Basically, you have to tell SQL Server how to serialize your data when using reference types. There is a performance impact associated with Format.UserDefined serialization as opposed to Format.Native.

Listing 14-12 is a UDA that calculates the statistical median of a set of numbers. The statistical median is the middle number of an ordered group of numbers. If there is an even number of numbers in the set, the statistical median is the average (mean) of the middle two numbers in the set.

Listing 14-12.  UDA to Calculate Statistical Median

using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlTypes;
using System.Runtime.InteropServices;
using Microsoft.SqlServer.Server;

namespace Apress.Examples {
[Serializable]
[Microsoft.SqlServer.Server.SqlUserDefinedAggregate (
          Format.UserDefined,
          IsNullIfEmpty = true,
          MaxByteSize = −1 )]
#160;   [StructLayout(LayoutKind.Sequential)]
  
    public struct Median : IBinarySerialize
    {
        List<double> temp; // List of numbers
  
        public void Init()
        {
            // Create new list of double numbers
            this.temp = new List<double> ();
        }
  
        public void Accumulate(SqlDouble number)
        {
            if (!number.IsNull) // Skip over NULLs
            {
                this.temp.Add(number.Value); // If number is not NULL, add it to list
            }
        }
  
        public void Merge(Median group)
        {
            // Merge two sets of numbers
            this.temp.InsertRange(this.temp.Count, group.temp);
        }
  
        public SqlDouble Terminate() {
            SqlDouble result = SqlDouble.Null; // Default result to NULL
            this.temp.Sort(); // Sort list of numbers
  
            int first, second; // Indexes to middle two numbers
  
            if (this.temp.Count % 2 == 1)
            {
                // If there is an odd number of values get the middle number twice
                first = this.temp.Count / 2;
                second = first;
            }
            else
            {
                // If there is an even number of values get the middle two numbers
                first = this.temp.Count / 2-1;
                second = first + 1;
            }
  
            if (this.temp.Count > 0) // If there are numbers, calculate median
            {
                // Calculate median as average of middle number(s)
                result = (SqlDouble)( this.temp[first] + this.temp[second] ) / 2.0;
            }
  
            return result;
        }
  
        #region IBinarySerialize Members
  
        // Custom serialization read method
        public void Read(System.IO.BinaryReader r)
        {
            // Create a new list of double values
            this.temp = new List<double> ();
  
            // Get the number of values that were serialized
            int j = r.ReadInt32();
  
            // Loop and add each serialized value to the list
            for (int i = 0; i < j; i++)
            {
                this.temp.Add(r.ReadDouble());
            }
        }
  
        // Custom serialization write method
        public void Write(System.IO.BinaryWriter w)
        {
  
            // Write the number of values in the list
            w.Write(this.temp.Count);
  
            // Write out each value in the list
            foreach (double d in this.temp)
            {
                w.Write(d);
            }
        }
  
        #endregion
    }
}

This UDA begins, like the other CLR integration examples, with namespace imports. We’ve added the System.Collections.Generic namespace this time so we can use the .NET List<T> strongly typed list.

using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlTypes;
using System.Runtime.InteropServices;
using Microsoft.SqlServer.Server;

The Median structure in the example is declared with the Serializable attribute to indicate that it can be serialized, and the StructLayout attribute with the LayoutKind.Sequential property to force the structure to be serialized in sequential fashion for a UDA that has a Format different from Native. The SqlUserDefinedAggregate attribute declares three properties, as follows:

• The Format.UserDefined property indicates that the UDA will implement serialization methods through the IBinarySerialize interface. This is required since the List<T> reference type is being used in the UDA.
• The IsNullIfEmpty property is set to true, indicating that NULL will be returned if no rows are passed to the UDA.
• The MaxByteSize property is set to −1 so that the UDA can be serialized if it is greater than 8,000 bytes. (The 8,000-byte serialization limit was a strict limit in SQL Server 2005 that prevented serialization of large objects, like large ArrayList objects, in the UDA.)

Because Format.UserDefined was specified on the Median structure, it must implement the IBinarySerialize interface. Inside the body of the struct, we’ve defined a List<double> named temp that will hold an intermediate temporary list of numbers passed into the UDA.

[Serializable]
[Microsoft.SqlServer.Server.SqlUserDefinedAggregate (
    Format.UserDefined,
    IsNullIfEmpty = true,
    MaxByteSize = −1 )]
[StructLayout(LayoutKind.Sequential)]
public struct Median : IBinarySerialize
{
    List<double> temp; // List of numbers
    ...
}

The Read() and Write() methods of the IBinarySerialize interface are used to deserialize and serialize the list, respectively:

        #region IBinarySerialize Members
  
        // Custom serialization read method
        public void Read(System.IO.BinaryReader r)
        {
            // Create a new list of double values
            this.temp = new List<double>();
            // Get the number of values that were serialized
            int j = r.ReadInt32();
  
            // Loop and add each serialized value to the list
            for (int i = 0; i < j; i++)
            {
                this.temp.Add(r.ReadDouble());
            }
        }
  
        // Custom serialization write method
        public void Write(System.IO.BinaryWriter w)
        {
            // Write the number of values in the list
            w.Write(this.temp.Count);
  
            // Write out each value in the list
            foreach (double d in this.temp)
            {
                w.Write(d);
            }
        }
  
        #endregion

The Init method of the UDA initializes the temp list by creating a new List<double> instance:

public void Init() {
    // Create new list of double numbers
    this.temp = new List<double>();
}

The Accumulate() method accepts a SqlDouble number and adds all non-NULL values to the temp list. Although you can include NULLs in your aggregate results, keep in mind that T-SQL developers are used to the NULL handling of built-in aggregate functions like SUM and AVG. In particular, developers are used to their aggregate functions discarding NULL. This is the main reason we eliminate NULL in this UDA.

public void Accumulate(SqlDouble number)
{
    if (!number.IsNull) // Skip over NULLs
    {
        this.temp.Add(number.Value); // If number is not NULL, add it to list
    }
}

The Merge() method in the example merges two lists of numbers if SQL Server decides to calculate the aggregate in parallel. If so, the server will pass a list of numbers into the Merge() method. This list of numbers must then be appended to the current list. For efficiency, we use the InsertRange() method of List<T> to combine the lists.

public void Merge(Median group)
{
    // Merge two sets of numbers
    this.temp.InsertRange(this.temp.Count, group.temp);
}

The Terminate() method of the UDA sorts the list of values and then determines the indexes of the middle numbers. If there is an odd number of values in the list, there is only a single middle number; if there is an even number of values in the list, the median is the average of the middle two numbers. If the list contains no values (which can occur if every value passed to the aggregate is NULL), the result is NULL; otherwise the Terminate() method calculates and returns the median.

        public SqlDouble Terminate() {
            SqlDouble result = SqlDouble.Null; // Default result to NULL
            this.temp.Sort(); // Sort list of numbers
  
            int first, second; // Indexes to middle two numbers
  
            if (this.temp.Count % 2 == 1)
            {
                // If there is an odd number of values get the middle number twice
                first = this.temp.Count / 2;
                second = first;
            }
            else
            {
                // If there is an even number of values get the middle two numbers
                first = this.temp.Count / 2–1;
                second = first + 1;
            }
  
            if (this.temp.Count > 0) // If there are numbers, calculate median
            {
                // Calculate median as average of middle number(s)
                result = (SqlDouble)( this.temp[first] + this.temp[second] ) / 2.0;
            }
  
            return result;
        }

Listing 14-13 demonstrates the use of this UDA to calculate the median UnitPrice from the Sales.SalesOrderDetail table on a per-product basis. The results are shown in Figure 14-8.

Listing 14-13.  Calculating Median Unit Price with a UDA

CREATE AGGREGATE dbo.Median (@value float) RETURNS float
EXTERNAL NAME ApressExamples.[Apress.Examples.Median];
GO
  
SELECT
    ProductID,
    dbo.Median(UnitPrice) AS MedianUnitPrice
FROM Sales.SalesOrderDetail

GROUP BY ProductID;