Back in 1999, or possibly early 2000, there was a rumor circulating about the possibility of using VBScript to write stored procedures in SQL Server. However, when SQL Server 2000 was released, this feature was not included. Fast forward to November 2005, and SQL Server 2005 (also known as Yukon) was finally released. One of the most talked-about features in SQL Server 2005 was CLR Integration, which allowed developers to use any .Net language to create database programmability objects.

For application developers familiar with C# or any other .Net language, this was seen as a revolutionary feature. However, for DBAs who were not familiar with programming languages, it seemed daunting. Many questions arose, such as whether T-SQL was dead and if every DBA needed to learn C#. The good news is that T-SQL is still very much alive and SQL Server 2005 introduced new features to enhance the language. DBAs can continue to handle their responsibilities in T-SQL and utilize the new management tools included in SQL Server 2005.

CLR Integration was not added to SQL Server just for the sake of it. There are scenarios where a managed .Net procedure can outperform one written in T-SQL. Additionally, writing code in a modern programming language like C# can result in better code quality. However, it is important to note that CLR Integration does not bring anything new to how data in a relational database is managed. Declarative set-based DML still outperforms procedural processing in this area.

One of the concerns regarding CLR Integration is that it may open the doors to programmers who have little knowledge of data management. While this may be a possibility, it is unlikely that everyone will start programming database functionality just because they are more familiar with the syntax. However, there is a risk of seeing more procedural code and potentially questionable uses of SQL Server, such as procedures calling out to web services or working with the file system.

CLR Integration is an interesting tool that can be helpful in certain situations, but it should be used with care. It is not the most important new feature in SQL Server 2005, but it does require the most coverage to ensure that users understand its capabilities and limitations. Developers need to be aware of the impact of their .Net implementations on SQL Server, and DBAs may want to review the code being executed in their databases.

In the upcoming parts of this series, we will dive deeper into how CLR Integration works, what you can do with it, and when you should or should not use it. To kick things off, let’s take a look at an example of a user-defined scalar function implemented in C# and how to use it in SQL Server 2005.

Using CLR Integration to Validate Email Addresses

One common request in SQL Server is the ability to use regular expressions for validation purposes. While SQL Server 2005 does not have this feature, the .Net Framework does. With CLR Integration, we can implement a user-defined function in C# that takes a string and validates it against a regular expression.

Here’s an example of a user-defined scalar function implemented in C#:

using System.Data.SqlTypes;
using System.Text.RegularExpressions;
using Microsoft.SqlServer.Server;

public partial class UserDefinedFunctions
{
  [Microsoft.SqlServer.Server.SqlFunction]
  public static SqlBoolean RegExValidate(SqlString expressionToValidate, SqlString regularExpression)
  {
    Regex regex = new Regex(regularExpression.Value);
    return regex.IsMatch(expressionToValidate.Value);
  }
}

This function, called RegExValidate, takes two parameters: the expression to validate and the regular expression to validate it against. It uses the Regex class from the .Net Framework to perform the validation and returns a SqlBoolean indicating whether the expression matches the format defined in the regular expression.

To use this function, we need to activate CLR Integration in SQL Server 2005. Once activated, we can create a new project in Visual Studio 2005 and choose the SQL Server Project type. We can then add a user-defined function and implement the logic in C#. After compiling the project into an assembly, we can deploy it to the SQL Server database.

Once deployed, the function can be used to validate strings with regular expressions. For example:

DECLARE @regex NVARCHAR(100)
SET @regex = N'^([\w-\.]+)@((\[[0-9]{1,3}\.[0-9]{1,3}\.[0-9]{1,3}\.)|(([\w-]+\.)+))([a-zA-Z]{2,4}|[0-9]{1,3})(\]?)$'

SELECT name, emailaddress, dbo.RegExValidate(emailaddress, @regex) AS validemail
FROM dbo.PEOPLE

In addition to using the function directly, we can also create a CHECK constraint that utilizes the function to ensure that only valid email addresses are entered into a table.

CLR Integration in SQL Server 2005 is a powerful tool for developers and DBAs, but it should be used judiciously. It is important to understand its capabilities and limitations to make informed decisions about when to use it. Stay tuned for more articles in this series where we will explore CLR Integration in more detail.