Scalar UDFs can be used in DEFAULT constraints. The only limitation that you should be aware of is that a UDF cannot accept columns from the table as inputs when used in a DEFAULT constraint. As an example, the code in Example 6-1 creates a table called T1 and a UDF called fn_T1_getkey, which returns the minimum missing key in T1.

IF OBJECT_ID('dbo.T1') IS NOT NULL
  DROP TABLE dbo.T1;
GO
CREATE TABLE dbo.T1
(
  keycol INT NOT NULL CONSTRAINT PK_T1 PRIMARY KEY CHECK (keycol > 0),
  datacol VARCHAR(10) NOT NULL
);
GO

IF OBJECT_ID('dbo.fn_T1_getkey') IS NOT NULL
  DROP FUNCTION dbo.fn_T1_getkey;
GO
CREATE FUNCTION dbo.fn_T1_getkey() RETURNS INT
AS
BEGIN
  RETURN
    CASE
      WHEN NOT EXISTS(SELECT * FROM dbo.T1 WHERE keycol = 1) THEN 1
      ELSE (SELECT MIN(keycol + 1)
            FROM dbo.T1 AS A
            WHERE NOT EXISTS
              (SELECT *
               FROM dbo.T1 AS B
               WHERE B.keycol = A.keycol + 1))
    END;
END
GO

The following code adds a DEFAULT constraint to keycol, which invokes the fn_T1_getkey function:

ALTER TABLE dbo.T1 ADD DEFAULT(dbo.fn_T1_getkey()) FOR keycol;

Note

Note that this DEFAULT constraint will do its job only for single row inserts, not for multiple-row inserts. Also, reusing key values is almost never advisable in actual business scenarios. I’m using this example here for demonstration purposes.

The following code inserts three rows, generating the keys 1, 2, and 3; deletes the row with the key 2; and inserts another row, generating the key 2:

INSERT INTO dbo.T1(datacol) VALUES('a'),
INSERT INTO dbo.T1(datacol) VALUES('b'),
INSERT INTO dbo.T1(datacol) VALUES('c'),
DELETE FROM dbo.T1 WHERE keycol = 2;
INSERT INTO dbo.T1(datacol) VALUES('d'),

Query the table by using the following code, and notice in Table 6-3 (which shows the output) that key 2 was assigned to the row that was inserted last (datacol = ′d′), because the row with the key 2 was previously deleted:

SELECT * FROM dbo.T1;

Unlike UDFs used in DEFAULT constraints, UDFs used in CHECK constraints are allowed to refer to columns from the table as inputs. CHECK constraints with UDFs give you great power in enforcing integrity rules, allowing you in some cases to avoid using triggers, which are typically more expensive. Later in this chapter I will demonstrate using UDFs that match input strings based on regular expressions in CHECK constraints.

You can create a UNIQUE or PRIMARY KEY constraint on a computed column that invokes a UDF. Keep in mind that both constraints create a unique index underneath the covers. This means that the target computed column and the UDF it invokes must meet indexing guidelines. For example, the UDF must be schema bound (created with the SCHEMABINDING option); the computed column must be deterministic and precise or deterministic and persisted, and so on. You can find the details about indexing guidelines for computed columns and UDFs in Books Online.

The following code attempts to add to T1 a computed column called col1, which invokes the fn_add1 UDF, and create a UNIQUE constraint on that column:

ALTER TABLE dbo.T1
  ADD col1 AS dbo.fn_add1(keycol) CONSTRAINT UQ_T1_col1 UNIQUE;

The attempt fails with the following error:

Msg 2729, Level 16, State 1, Line 1
Column 'col1' in table 'dbo.T1' cannot be used in an index or statistics or as a partition
key because it is non-deterministic.
Msg 1750, Level 16, State 0, Line 1
Could not create constraint. See previous errors.

The reason for the error is that the function doesn’t meet one of the requirements for indexing, which says that the function must be schema bound. As you can see, the error message itself is not too helpful in indicating the cause of the error or in suggesting how to fix it. You need to realize that in order to fix the problem, you should alter the function by adding the SCHEMABINDING option:

ALTER FUNCTION dbo.fn_add1(@i AS INT) RETURNS INT
  WITH SCHEMABINDING
AS
BEGIN
  RETURN @i + 1;
END
GO

Try adding the computed column with the UNIQUE constraint again, and this time your code will run successfully:

ALTER TABLE dbo.T1
  ADD col1 AS dbo.fn_add1(keycol) CONSTRAINT UQ_T1_col1 UNIQUE;

It’s a bit trickier when you try to create a PRIMARY KEY constraint on such a computed column. To see how this works, first drop the existing PRIMARY KEY from T1:

ALTER TABLE dbo.T1 DROP CONSTRAINT PK_T1;

Next attempt to add another computed column called col2 with a PRIMARY KEY constraint:

ALTER TABLE dbo.T1
  ADD col2 AS dbo.fn_add1(keycol)
    CONSTRAINT PK_T1 PRIMARY KEY;

The attempt will fail, generating the following error:

Msg 1711, Level 16, State 1, Line 1
Cannot define PRIMARY KEY constraint on column 'col2' in table 'T1'. The computed column has
 to be persisted and not nullable.
Msg 1750, Level 16, State 0, Line 1
Could not create constraint. See previous errors.

You must explicitly guarantee that col2 will never end up with a NULL. You can achieve this by defining the column as PERSISTED and NOT NULL, as in:

ALTER TABLE dbo.T1
  ADD col2 AS dbo.fn_add1(keycol) PERSISTED NOT NULL
    CONSTRAINT PK_T1 PRIMARY KEY;

The PERSISTED option is new to SQL Server 2005; in SQL Server 2000, to successfully create a primary key constraint on the computed column, you must encapsulate the UDF invocation within the ISNULL function, as in:

ALTER TABLE dbo.T1
  ADD col2 AS ISNULL(dbo.fn_add1(keycol), 0)
    CONSTRAINT PK_T1 PRIMARY KEY;

When you’re done, run the following code for cleanup:

IF OBJECT_ID('dbo.T1') IS NOT NULL
  DROP TABLE dbo.T1;
GO
IF OBJECT_ID('dbo.fn_T1_getkey') IS NOT NULL
  DROP FUNCTION dbo.fn_T1_getkey;
GO
IF OBJECT_ID('dbo.fn_T1_datacol_count') IS NOT NULL
  DROP FUNCTION dbo.fn_T1_datacol_count;

The ability to develop CLR routines in SQL Server gives you great power, but at the same time it introduces great risk. .NET gives you a richer programming vocabulary and better performance than T-SQL in areas that T-SQL was never designed to cope with efficiently. These areas include complex calculations, iterative and procedural logic, string manipulation, external access to operating system resources, and so on. T-SQL is a declarative language. It’s much more powerful and better performing than .NET when the task at hand is data manipulation with set-based queries. The danger with .NET integration is that it is also a vehicle for programmers who have not yet adopted a SQL mindset, enabling them to introduce poorly performing code with inappropriate tools. In this book, I’ll give examples of where routines should be developed with .NET.

More Info

For information about set-based querying and efficient set-based solutions, please refer to Inside T-SQL Querying, which covers those topics in great detail.

Regular expressions give you a powerful way to match patterns of text with concise and flexible notation. Regular expression is a standard and meaningful term that has been around a long time. ANSI SQL defines a SIMILAR TO predicate that provides support for regular expressions, but unfortunately SQL Server 2005 hasn’t yet implemented this predicate in T-SQL. However, you can take good advantage of regular expressions in .NET code. For example, the following C# code defines a function called fn_RegExMatch:

// Validate input string against regular expression
[SqlFunction(IsDeterministic = true, DataAccess = DataAccessKind.None)]
public static SqlBoolean fn_RegExMatch(SqlString inpStr,
  SqlString regExStr)
{
    if (inpStr.IsNull || regExStr.IsNull)
        return SqlBoolean.Null;
    else
        return (SqlBoolean)Regex.IsMatch(inpStr.Value, regExStr.Value ,
          RegexOptions.CultureInvariant);
}

The attributes in the header will tell SQL Server that the function is deterministic and that there’s no data access involved. Note the usage of RegexOptions.CultureInvariant option to get a culture-independent match. If the match would be culture-dependent, the function would not be deterministic. (See http://msdn2.microsoft.com/en-us/library/z0sbec17.aspx for details.)

The function accepts a string (inpStr) and a regular expression (regExStr) as inputs. The return type of this function is SqlBoolean, which has three possible values: 0, 1, and Null; the return value is Null if regExStr or inpStr is null, 1 if the pattern regExStr was found in inpStr, and 0 otherwise. As you can see, the function’s code is very simple. The code first tests for NULL input parameters and returns NULL if either parameter is NULL. If neither input parameter is NULL, the function returns the result of the Regex.IsMatch method. This method checks whether the string provided as the first parameter contains the pattern provided as the second parameter. The RegEx.IsMatch method returns a .NET System.Boolean value, which must be explicitly converted to SqlBoolean.

Here’s the function’s code using Visual Basic, in case that’s your language of preference:

' Validate input string against regular expression
<SqlFunction(IsDeterministic:=True, DataAccess:=DataAccessKind.None)> _
Public Shared Function fn_RegExMatch(ByVal inpStr As SqlString, _
  ByVal regExStr As SqlString) As SqlBoolean
    If (inpStr.IsNull Or regExStr.IsNull) Then
        Return SqlBoolean.Null
    Else
        Return CType(Regex.IsMatch(inpStr.Value, regExStr.Value, _
          RegexOptions.CultureInvariant), SqlBoolean)
    End If
End Function

If you followed all the instructions described in Appendix A, you’re ready to test and use the function. Those instructions include: enabling CLR in SQL Server (disabled by default), creating a test database called CLRUtilities, developing your code in Microsoft Visual Studio 2005, building an assembly in a .dll file on disk, loading the Intermediate Language (IL) code from the assembly into a SQL Server database, and registering routines from the assembly in the database. Here’s the code you need to run to enable CLR in SQL Server and create the CLRUtilities test database, in case you haven’t yet done so:

SET NOCOUNT ON;
USE master;
EXEC sp_configure 'clr enabled', 1;
RECONFIGURE;
GO
IF DB_ID('CLRUtilities') IS NOT NULL
  DROP DATABASE CLRUtilities;
GO
CREATE DATABASE CLRUtilities;
GO
USE CLRUtilities;
GO

Note

Note that by turning on the ′clr enabled′ server configuration option (disabled by default) you specify that user assemblies can be run by Microsoft SQL Server at the instance level. You cannot control this option at a more granular level, so by enabling this option, you enable it for the whole SQL Server instance. Enabling this option might impose a security risk. The level of risk depends on what you will allow each individual assembly to do. When creating assemblies with the CREATE ASSEMBLY command, you can control code access permissions by setting the PERMISSION_SET option to SAFE, EXTERNAL_ACCESS or UNSAFE. Here’s a security note from Books Online describing the three:

SAFE is the recommended permission setting for assemblies that perform computation and data management tasks without accessing resources outside an instance of SQL Server.

We recommend using EXTERNAL_ACCESS for assemblies that access resources outside of an instance of SQL Server. EXTERNAL_ACCESS assemblies include the reliability and scalability protections of SAFE assemblies, but from a security perspective are similar to UNSAFE assemblies. This is because code in EXTERNAL_ACCESS assemblies runs by default under the SQL Server service account and accesses external resources under that account, unless the code explicitly impersonates the caller. Therefore, permission to create EXTERNAL_ACCESS assemblies should be granted only to logins that are trusted to run code under the SQL Server service account. For more information about impersonation, see CLR Integration Security.

Specifying UNSAFE enables the code in the assembly complete freedom to perform operations in the SQL Server process space that can potentially compromise the robustness of SQL Server. UNSAFE assemblies can also potentially subvert the security system of either SQL Server or the common language runtime. UNSAFE permissions should be granted only to highly trusted assemblies. Only members of the sysadmin fixed server role can create and alter UNSAFE assemblies.

None of the functions that will be discussed in this chapter requires external access, so the assembly will be created with a SAFE permission set. In the following chapters, I will demonstrate a stored procedure that requires EXTERNAL_ACCESS permission set and a trigger that requires UNSAFE permission set, but I’ll do so only for demonstration purposes. When demonstrating those routines, I’ll alter the assembly to support the required permission set. Bear in mind the security risk involved in allowing external access to your assembly.

In all the following examples in the book, I’ll assume that the CLRUtilities database exists and that you already built the assembly from Visual Studio.

Next run the following code to load the assembly into the database if you haven’t done so yet:

USE CLRUtilities;
GO
CREATE ASSEMBLY CLRUtilities
FROM 'C:CLRUtilitiesCLRUtilitiesinDebugCLRUtilities.dll'
WITH PERMISSION_SET = SAFE;
-- If no Debug folder, use instead:
-- FROM 'C:CLRUtilitiesCLRUtilitiesinCLRUtilities.dll'

Of course, if the CLRUtilities.dll file containing the assembly was created in a different folder, specify the relevant folder instead. The CREATE ASSEMBLY command loads the IL code from the .dll file into the database. Once it’s loaded, you no longer need the external file. Note that if you rebuild the assembly later after adding routines and do not use the automatic deployment option from Visual Studio Professional edition, you will need to issue an ALTER ASSEMBLY or DROP and CREATE ASSEMBLY commands manually to reload the IL code into the database. This step is not necessary if you followed the instructions in Appendix A and already created all routines that are discussed in the book. I will not refer further to this step.

Whenever I discuss a new routine, I will provide the T-SQL code required to register it in the database (CREATE FUNCTION | PROCEDURE | TRIGGER command), although you don’t actually need to run such code if you followed the instructions in Appendix A fully.

Here’s the code you need to run to register the C# version of the fn_RegExMatch function in the CLRUtilities database:

USE CLRUtilities;
GO
IF OBJECT_ID('dbo.fn_RegExMatch') IS NOT NULL
  DROP FUNCTION dbo.fn_RegExMatch;
GO
CREATE FUNCTION dbo.fn_RegExMatch
  (@inpstr AS NVARCHAR(MAX), @regexstr AS NVARCHAR(MAX))
RETURNS BIT
EXTERNAL NAME CLRUtilities.CLRUtilities.fn_RegExMatch;

And here’s the code that registers the Visual Basic version:

CREATE FUNCTION dbo.fn_RegExMatch
  (@inpstr AS NVARCHAR(MAX), @regexstr AS NVARCHAR(MAX))
RETURNS BIT
EXTERNAL NAME CLRUtilities.[CLRUtilities.CLRUtilities].fn_RegExMatch;

Note

Notice the discrepancy between the external name specified when registering a function developed with C# compared to one developed with Visual Basic (CLRUtilities.CLRUtilities.fn_RegExMatch vs. CLRUtilities.[CLRUtilities.CLRUtilities].fn_RegExMatch). This is quite confusing. In previous .NET versions (2002, 2003), whenever you created a class library, C# added a root namespace with the same name as the class, and Visual Basic didn’t. Now the behavior is different: Visual Basic creates a root namespace, and C# does not. To make the T-SQL code consistent regardless of the .NET language you used, you must prevent the creation of the root namespace when programming with Visual Basic. In Visual Studio, right-click the project, select Properties, Application page. Clear the "Root namespace" textbox. In this book I will assume that you did not clear this option, hence when registering objects you will see a discrepancy in the specified external names.

At this point, you can start using the fn_RegExMatch function.

More Info

You can find many useful regular expressions on the Internet—for example, at this "library": http://www.regexlib.com.

As an example of using your new function, suppose that you want to check whether a certain e-mail address is valid. To do so, use the regular expression: N′^([w-]+.)*?[w-]+@[w-]+.([w-]+.)*?[w]+$′.

The regular expression checks whether the address starts with a word, contains the "at" (@) symbol, and has at least two words delimited with a dot (.) after the @ symbol. It can have additional dot-separated words before and after the @ symbol. Note that this regular expression is simplistic and is provided here for demonstration purposes. To learn how to write more robust and complete regular expressions, I suggest that you visit http://www.regularexpressions.info/.

The following code returns 1 because the e-mail address provided is valid:

SELECT dbo.fn_RegExMatch(
  N'[email protected]',
  N'^([w-]+.)*?[w-]+@[w-]+.([w-]+.)*?[w]+$'),

And the following code returns 0 because the address is invalid:

SELECT dbo.fn_RegExMatch(
  N'dejan#solidqualitylearning.com',
  N'^([w-]+.)*?[w-]+@[w-]+.([w-]+.)*?[w]+$'),

You can also use the function in a CHECK constraint. For example, the following code creates the table TestRegEx with a CHECK constraint that limits the values of the jpgfilename column to file names with an extension "jpg":

IF OBJECT_ID('dbo.TestRegEx') IS NOT NULL
  DROP TABLE dbo.TestRegEx;
GO
CREATE TABLE dbo.TestRegEx
(
  jpgfilename NVARCHAR(4000) NOT NULL
  CHECK(dbo.fn_RegExMatch(jpgfilename,
    N'^(([a-zA-Z]:)|(\{2}w+)$?)(\(w[w ]*.*))+.(jpg|JPG)$')
      = CAST(1 As BIT))
);

The values in the jpgfilename column must meet the following pattern: the value must start with either a letter in the range A through Z followed by a colon (drive letter), or with two backslashes and a word (network share). Then the value must have at least one backslash denoting the root folder of the drive or the share. After that, the value can have additional backslash-word combinations denoting multiple subfolders. Finally, after the last word there must be a dot followed by the letters "jpg" (uppercase or lowercase).

The following INSERT containing a valid JPEG file name is accepted:

INSERT INTO dbo.TestRegEx(jpgfilename) VALUES(N'C:TempmyFile.jpg'),
INSERT INTO dbo.TestRegEx(jpgfilename) VALUES(N'\MyShareTempmyFile.jpg'),
INSERT INTO dbo.TestRegEx(jpgfilename) VALUES(N'\MySharemyFile.jpg'),
INSERT INTO dbo.TestRegEx(jpgfilename) VALUES(N'C:myFile.jpg'),

The following INSERT containing a .txt file name is rejected:

INSERT INTO dbo.TestRegEx(jpgfilename) VALUES(N'C:TempmyFile.txt'),
INSERT INTO dbo.TestRegEx(jpgfilename) VALUES(N'\MyShare\TempmyFile.jpg'),
INSERT INTO dbo.TestRegEx(jpgfilename) VALUES(N'\myFile.jpg'),
INSERT INTO dbo.TestRegEx(jpgfilename) VALUES(N'C:myFile.jpg'),

When you’re done, run the following code for cleanup:

IF OBJECT_ID('dbo.TestRegEx') IS NOT NULL
  DROP TABLE dbo.TestRegEx;
GO
IF OBJECT_ID('dbo.fn_RegExMatch') IS NOT NULL
  DROP FUNCTION dbo.fn_RegExMatch;

When you develop CLR objects in SQL Server 2005, you might think that you can use either .NET native types or .NET SQL types for your input/output parameters and variables. .NET SQL types map more accurately to SQL Server types. Using .NET native types in the routines’ interfaces will cause implicit casting of the values when passed from or to SQL Server. Some programmers prefer to stick to .NET SQL types because they believe that there’s overhead in the implicit conversions. Such a choice limits you in some cases because .NET SQL types are not as rich as .NET native types in their functionality. For example, the .NET native System.String type (string in C#, String in Visual Basic) has the Substring method, while the .NET SQL type SqlString doesn’t.

It is not performance that makes a real difference. Usage of SQL types in .NET code for CLR objects inside a database is highly recommended because native .NET types do not support NULL values. For example, if you would implement the fn_RegExMatch function with the .NET string native type instead of the SqlString type for the parameters, you would get a compile error where the code tests whether any of the parameters is NULL (the first if statement in the function’s body). If you use the .NET string type and skip NULL testing, the function returns an exception when you call it with NULL arguments. And if you need additional functionality provided by .NET native types, you will have to do some explicit casting. Additionally, you can get the value in .NET native type using the Value property of a SQL type variable, store this value in another variable of the native .NET type, and then all the normal properties and methods of a native type will be available. In the fn_RegExMatch function, the RegEx.IsMatch method expects native .NET string types as input; therefore, the Value property of the .NET SQL types is used. The return type of the method is a .NET native Boolean value, so the code casts it explicitly to SqlBoolean.

This section will show you that the performance difference between implicit and explicit casting is not significant. The following C# code defines the functions fn_ImpCast, which uses .NET native types and implicit conversion, and fn_ExpCast, which uses .NET SQL types and explicit conversion:

// Compare implicit vs. explicit casting
[SqlFunction(IsDeterministic = true, DataAccess = DataAccessKind.None)]
public static string fn_ImpCast(string inpStr)
{
    return inpStr.Substring(2, 3);
}

[SqlFunction(IsDeterministic = true, DataAccess = DataAccessKind.None)]
public static SqlString fn_ExpCast(SqlString inpStr)
{
    return (SqlString)inpStr.ToString().Substring(2, 3);
}

And here’s the Visual Basic code that defines the functions:

' Compare implicit vs. explicit casting
<SqlFunction(IsDeterministic:=True, DataAccess:=DataAccessKind.None)> _
Public Shared Function fn_ImpCast(ByVal inpStr As String) As String
    Return inpStr.Substring(2, 3)
End Function

<SqlFunction(IsDeterministic:=True, DataAccess:=DataAccessKind.None)> _
Public Shared Function fn_ExpCast(ByVal inpStr As SqlString) As SqlString
    Return CType(inpStr.ToString().Substring(2, 3), SqlString)
End Function

Here’s code that registers the C# functions in the database:

IF OBJECT_ID('dbo.fn_ImpCast') IS NOT NULL
  DROP FUNCTION dbo.fn_ImpCast;
GO
IF OBJECT_ID('dbo.fn_ExpCast') IS NOT NULL
  DROP FUNCTION dbo.fn_ExpCast;
GO
-- Create fn_ImpCast function
CREATE FUNCTION dbo.fn_ImpCast(@inpstr AS NVARCHAR(4000))
RETURNS NVARCHAR(4000)
EXTERNAL NAME CLRUtilities.CLRUtilities.fn_ImpCast;
GO
-- Create fn_ExpCast function
CREATE FUNCTION dbo.fn_ExpCast(@inpstr AS NVARCHAR(4000))
RETURNS NVARCHAR(4000)
EXTERNAL NAME CLRUtilities.CLRUtilities.fn_ExpCast;

Here’s code that registers the Visual Basic functions:

-- Create fn_ImpCast function
CREATE FUNCTION dbo.fn_ImpCast(@inpstr AS NVARCHAR(4000))
RETURNS NVARCHAR(4000)
EXTERNAL NAME CLRUtilities.[CLRUtilities.CLRUtilities].fn_ImpCast;
GO
-- Create fn_ExpCast function
CREATE FUNCTION dbo.fn_ExpCast(@inpstr AS NVARCHAR(4000))
RETURNS NVARCHAR(4000)
EXTERNAL NAME CLRUtilities.[CLRUtilities.CLRUtilities].fn_ExpCast;

The following code invokes the fn_ImpCast function a million times in a loop, running for 16 seconds:

SET NOCOUNT ON;
GO
DECLARE @a AS NVARCHAR(4000);
DECLARE @i AS INT;
SET @i = 1;
WHILE @i <= 1000000
BEGIN
 SET @a = dbo.fn_ImpCast(N'123456'),
 SET @i = @i + 1;
END

The following code invokes the fn_ExpCast function, running for 17 seconds:

DECLARE @a AS NVARCHAR(4000);
DECLARE @i AS INT;
SET @i = 1;
WHILE @i <= 1000000
BEGIN
 SET @a = dbo.fn_ExpCast(N'123456'),
 SET @i = @i + 1;
END

As you can see, the difference is not significant, and in this test the implicit casting method even performs a bit better than the explicit casting method.

When you’re done, run the following code for cleanup:

IF OBJECT_ID('dbo.fn_ImpCast') IS NOT NULL
  DROP FUNCTION dbo.fn_ImpCast;
GO
IF OBJECT_ID('dbo.fn_ExpCast') IS NOT NULL
  DROP FUNCTION dbo.fn_ExpCast;

You can find the T-SQL implementation of the SQL Signature function in Example 6-2. I’d like to thank Stuart Ozer, who authored the function, for allowing me to cover it in this book. Stuart is with the Microsoft SQL Server Customer Advisory Team.

IF OBJECT_ID('dbo.fn_SQLSigTSQL') IS NOT NULL
  DROP FUNCTION dbo.fn_SQLSigTSQL;
GO

CREATE FUNCTION dbo.fn_SQLSigTSQL
  (@p1 NTEXT, @parselength INT = 4000)
RETURNS NVARCHAR(4000)

--
-- This function is provided "AS IS" with no warranties,
-- and confers no rights.
-- Use of included script samples are subject to the terms specified at
-- http://www.microsoft.com/info/cpyright.htm
--
-- Strips query strings
AS
BEGIN
  DECLARE @pos AS INT;
  DECLARE @mode AS CHAR(10);
  DECLARE @maxlength AS INT;
  DECLARE @p2 AS NCHAR(4000);
  DECLARE @currchar AS CHAR(1), @nextchar AS CHAR(1);
  DECLARE @p2len AS INT;

  SET @maxlength = LEN(RTRIM(SUBSTRING(@p1,1,4000)));
  SET @maxlength = CASE WHEN @maxlength > @parselength
                     THEN @parselength ELSE @maxlength END;
  SET @pos = 1;
  SET @p2 = '';
  SET @p2len = 0;
  SET @currchar = '';
  set @nextchar = '';
  SET @mode = 'command';

  WHILE (@pos <= @maxlength)
  BEGIN
    SET @currchar = SUBSTRING(@p1,@pos,1);
    SET @nextchar = SUBSTRING(@p1,@pos+1,1);
    IF @mode = 'command'
    BEGIN
      SET @p2 = LEFT(@p2,@p2len) + @currchar;
      SET @p2len = @p2len + 1 ;
      IF @currchar IN (',','(',' ','=','<','>','!')
        AND @nextchar BETWEEN '0' AND '9'
      BEGIN
        SET @mode = 'number';
        SET @p2 = LEFT(@p2,@p2len) + '#';
        SET @p2len = @p2len + 1;
      END
      IF @currchar = ''''
      BEGIN
        SET @mode = 'literal';
        SET @p2 = LEFT(@p2,@p2len) + '#''';
        SET @p2len = @p2len + 2;
      END
    END
    ELSE IF @mode = 'number' AND @nextchar IN (',',')',' ','=','<','>','!')
      SET @mode= 'command';
    ELSE IF @mode = 'literal' AND @currchar = ''''
      SET @mode= 'command';

    SET @pos = @pos + 1;
  END
  RETURN @p2;
END
GO

The fn_SQLSigTSQL function accepts two input parameters: @p1 is the input query string, and @parselength is the maximum number of characters that you want to parse. If @parselength is smaller than the length of the query string stored in @p1, the function will parse only the @parselength leftmost characters. The function iterates through the characters of the string one at a time. It keeps a state value in a variable called @mode, which can be set to one of the following values: ′command′, ′number′, or ′literal′.

Command is the default state, and it simply means that the current character will be concatenated to the output string as is. Number means that a number literal is identified, in which case the # symbol will be concatenated. A number literal is identified when a digit follows a comma, an opening parenthesis, a space, or an operator. The state changes from number to command when the next character is a comma, a closing parenthesis, a space, or an operator. Literal means that a character string literal is identified, in which case the string #′ will be concatenated. A character string literal is identified when an opening quote is detected. The state changes from literal to command when a closing quote is detected.

To test the fn_SQLSigTSQL function, run the following code:

SELECT dbo.fn_SQLSigTSQL
  (N'SELECT * FROM dbo.T1 WHERE col1 = 3 AND col2 > 78', 4000);

You will get the following output:

SELECT * FROM dbo.T1 WHERE col1 = # AND col2 > #

Example 6-3 and Example 6-4 have the C# and Visual Basic implementations of the SQL Signature function. The .NET versions of the function are adaptations of Stuart’s algorithm. These adaptations were developed by Andrew J. Kelly and Dejan Sarka, both of whom are mentors with Solid Quality Learning and distinguished SQL Server MVPs. The .NET versions of the function described here are logically similar to the T-SQL version in Example 6-2 and are provided for performance testing and comparison purposes only. They do not represent good CLR writing. Later in this chapter, I’ll describe a much more powerful CLR-based solution using regular expressions to produce query signatures.

// SQL Signature
[SqlFunction(IsDeterministic = true, DataAccess = DataAccessKind.None)]
public static SqlString fn_SQLSigCLR(SqlString inpRawString,
  SqlInt32 inpParseLength)
{
    if (inpRawString.IsNull)
        return SqlString.Null;
    int pos = 0;
    string mode = "command";
    string RawString = inpRawString.Value;
    int maxlength = RawString.Length;
    StringBuilder p2 = new StringBuilder();
    char currchar = ' ';
    char nextchar = ' ';
    int ParseLength = RawString.Length;
    if (!inpParseLength.IsNull)
        ParseLength = inpParseLength.Value;
    if (RawString.Length > ParseLength)
    {
        maxlength = ParseLength;
    }
    while (pos < maxlength)
    {
        currchar = RawString[pos];
        if (pos < maxlength - 1)
        {
            nextchar = RawString[pos + 1];
        }
        else
        {
            nextchar = RawString[pos];
        }
        if (mode == "command")
        {
            p2.Append(currchar);
            if ((",( =<>!".IndexOf(currchar) >= 0)
               &&
                (nextchar >= '0' && nextchar <= '9'))
            {
                mode = "number";
                p2.Append('#'),
            }
            if (currchar == ''')
            {
                mode = "literal";
                p2.Append("#'");
            }
        }
        else if ((mode == "number")
                  &&
                   (",( =<>!".IndexOf(nextchar) >= 0))
        {
            mode = "command";
        }
        else if ((mode == "literal") && (currchar == '''))
        {
            mode = "command";
        }
        pos++;
    }
    return p2.ToString();
}

' SQL Signature
<SqlFunction(IsDeterministic:=True, DataAccess:=DataAccessKind.None)> _
Public Shared Function fn_SQLSigCLR(ByVal inpRawString As SqlString, _
  ByVal inpParseLength As SqlInt32) As SqlString
    If inpRawString.IsNull Then
        Return SqlString.Null
    End If
    Dim pos As Integer = 0
    Dim mode As String = "command"
    Dim RawString As String = inpRawString.Value
    Dim maxlength As Integer = RawString.Length
    Dim p2 As StringBuilder = New StringBuilder()
    Dim currchar As Char = " "c
    Dim nextchar As Char = " "c
    Dim ParseLength As Integer = RawString.Length
    If (Not inpParseLength.IsNull) Then
        ParseLength = inpParseLength.Value
    End If
    If (RawString.Length > ParseLength) Then
        maxlength = ParseLength
    End If
    While (pos < maxlength)
        currchar = RawString(pos)
        If (pos < maxlength - 1) Then
            nextchar = RawString(pos + 1)
        Else
            nextchar = RawString(pos)
        End If
        If (mode = "command") Then
            p2.Append(currchar)
            If ((",( =<>!".IndexOf(currchar) >= 0) _
               And _
                (nextchar >= "0"c And nextchar <= "9"c)) Then
                mode = "number"
                p2.Append("#")
            End If
            If (currchar = "'"c) Then
                mode = "literal"
                p2.Append("#")
            End If
        ElseIf ((mode = "number") And _
                (",( =<>!".IndexOf(nextchar) >= 0)) Then
            mode = "command"
        ElseIf ((mode = "literal") And _
                (currchar = "'"c)) Then
            mode = "command"
        End If
        pos = pos + 1
    End While
    Return p2.ToString
End Function

Use the following code to register the C# version of the fn_SQLSigCLR function:

IF OBJECT_ID('dbo.fn_SQLSigCLR') IS NOT NULL
  DROP FUNCTION dbo.fn_SQLSigCLR;
GO
CREATE FUNCTION dbo.fn_SQLSigCLR
  (@rawstring AS NVARCHAR(4000), @parselength AS INT)
RETURNS NVARCHAR(4000)
EXTERNAL NAME CLRUtilities.CLRUtilities.fn_SQLSigCLR;

And use the following code if you implemented the function with Visual Basic:

CREATE FUNCTION dbo.fn_SQLSigCLR
  (@rawstring AS NVARCHAR(4000), @parselength AS INT)
RETURNS NVARCHAR(4000)
EXTERNAL NAME CLRUtilities.[CLRUtilities.CLRUtilities].fn_SQLSigCLR;

Run the following code to test the fn_SQLSigCLR function:

SELECT dbo.fn_SQLSigCLR
  (N'SELECT * FROM dbo.T1 WHERE col1 = 3 AND col2 > 78', 4000);

You will get the following output:

SELECT * FROM dbo.T1 WHERE col1 = # AND col2 > #

Remember that .NET code is much faster than T-SQL in string manipulation. The SQL Signature function is a perfect example for demonstrating the performance difference, especially because both versions implement the same algorithm. You will be able to observe the net performance difference in string manipulation.

First, run the following code to create the table Queries and populate it with 100,000 query strings:

IF OBJECT_ID('dbo.Queries') IS NOT NULL
  DROP TABLE dbo.Queries;
GO
SELECT CAST(N'SELECT * FROM dbo.T1 WHERE col1 = 3 AND col2 > 78'
         AS NVARCHAR(MAX)) AS query
INTO dbo.Queries
FROM dbo.Nums
WHERE n <= 100000;

Turn on the Discard Results After Execution option in SSMS.

When I ran the following code with the T-SQL version of the function, it took almost 100 seconds to finish:

SELECT dbo.fn_SQLSigTSQL(query, 4000) FROM dbo.Queries;

The CLR C# version finished in 1 second, and the Visual Basic version finished in 2 seconds:

SELECT dbo.fn_SQLSigCLR(query, 4000) FROM dbo.Queries;

Turn off the Discard Results After Execution option in SSMS.

As you can see, the CLR version of the function is about 100 times faster than the T-SQL version.

As I mentioned earlier, the fn_SQLSigCLR function implements the same algorithm implemented by the fn_SQLSigTSQL function and is provided mainly for performance comparison purposes. You can implement a much more powerful CLR-based solution using regular expressions. Earlier I showed how you can use regular expressions to do pattern matching; that is, to check whether a certain string matches a certain pattern. You can also use regular expressions to do pattern-based replacement; that is, you can replace all occurrences of a pattern within a string with another pattern. Here’s the C# definition of the fn_RegExReplace function, which invokes the Replace method of a Regex object:

// fn_RegExReplace - for generic use of RegEx-based replace
[SqlFunction(IsDeterministic = true, DataAccess = DataAccessKind.None)]
public static SqlString fn_RegExReplace(
    SqlString input, SqlString pattern, SqlString replacement)
{
    if (input.IsNull || pattern.IsNull || replacement.IsNull)
        return SqlString.Null;
    else
        return (SqlString)Regex.Replace(
        input.Value, pattern.Value, replacement.Value);
}

And here’s the Visual Basic definition of the function:

' fn_RegExReplace - for generic use of RegEx-based replace
<SqlFunction(IsDeterministic:=True, DataAccess:=DataAccessKind.None)> _
Public Shared Function fn_RegExReplace( _
  ByVal input As SqlString, ByVal pattern As SqlString, _
  ByVal replacement As SqlString) As SqlString
    If (input.IsNull Or pattern.IsNull Or replacement.IsNull) Then
        Return SqlString.Null
    Else
        Return CType(Regex.Replace( _
            input.Value, pattern.Value, replacement.Value), SqlString)
    End If
End Function

The function accepts three input arguments: input, pattern, and replacement. The function first checks whether one of the inputs is Null, and if so, returns a Null. If none of the inputs is Null, the function invokes the Regex.Replace method, which substitutes each occurrence of pattern within the string input with the replacement pattern. Here I’ll demonstrate how you can use the fn_RegExReplace function to generate query signatures, but of course you can use the function for general pattern-based string replacement purposes.

Use the following code to register the C# version of the fn_RegExReplace function:

IF OBJECT_ID('dbo.fn_RegExReplace') IS NOT NULL
  DROP FUNCTION dbo.fn_RegExReplace;
GO
CREATE FUNCTION dbo.fn_RegExReplace(
  @input       AS NVARCHAR(MAX),
  @pattern     AS NVARCHAR(MAX),
  @replacement AS NVARCHAR(MAX))
RETURNS NVARCHAR(MAX)
WITH RETURNS NULL ON NULL INPUT
EXTERNAL NAME CLRUtilities.CLRUtilities.fn_RegExReplace;

And use the following code if you implemented the function with Visual Basic:

CREATE FUNCTION dbo.fn_RegExReplace(
  @input       AS NVARCHAR(MAX),
  @pattern     AS NVARCHAR(MAX),
  @replacement AS NVARCHAR(MAX))
RETURNS NVARCHAR(MAX)
WITH RETURNS NULL ON NULL INPUT
EXTERNAL NAME CLRUtilities.[CLRUtilities.CLRUtilities].fn_RegExReplace;

Here’s an example of how you can use the function to generate query signatures out of the query strings stored in the Queries table:

SELECT
  dbo.fn_RegExReplace(query,
    N'([s,(=<>!](?![^]]+[]]))(?:(?:(?:(?#    expression coming
     )(?:([N])?('')(?:[^'']|'''')*(''))(?#      character
     )|(?:0x[da-fA-F]*)(?#                     binary
     )|(?:[-+]?(?:(?:[d]*.[d]*|[d]+)(?#     precise number
     )(?:[eE]?[d]*)))(?#                       imprecise number
     )|(?:[~]?[-+]?(?:[d]+))(?#                integer
     ))(?:[s]?[+-*/\%&|^][s]?)?)+(?#   operators
     ))',
    N'$1$2$3#$4')
FROM dbo.Queries;

The pattern is self-documented with inline comments. It identifies (and substitutes with a # symbol) more types of literals than the fn_SQLSigCLR did. It identifies character literals, binary ones, precise numbers, imprecise numbers, and even folds expressions involving literals and substitutes them with a # symbol. This solution has another advantage over the fn_SQLSigCLR function–you can maintain the regular expressions yourself and enhance them to support more cases without having to alter the definition of the function. However, the enhanced capabilities you get from regular expressions do come at a certain cost; the above query ran in about 12 seconds–12 times slower than the fn_SQLSigCLR function, but still 8 times faster than the fn_SQLSigTSQL function.

Tip

You might want to create functions that serve a generic purpose such as making the fn_RegExReplace function accessible in all databases without the need to database-qualify the function name (CLRUtilities.dbo.fn_RegExReplace). To achieve this, create a synonym to the function in each database where you want to make it available. For example, to make the function available in the Northwind database, run the following code:

USE Northwind;
GO
CREATE SYNONYM dbo.fn_RegExReplace
  FOR CLRUtilities.dbo.fn_RegExReplace;

If you create a synonym in the model database, the synonym will be created in every new database that you create in the future because a new database is created as a copy of the model. This also applies to the tempdb database, which is created every time you restart SQL Server.

When you’re done, run the following code for cleanup:

USE Northwind;
GO
IF OBJECT_ID('dbo.fn_RegExReplace', 'SN') IS NOT NULL
  DROP SYNONYM dbo.fn_RegExReplace;
GO
USE CLRUtilities;
GO
IF OBJECT_ID('dbo.fn_SQLSigTSQL') IS NOT NULL
  DROP FUNCTION dbo.fn_SQLSigTSQL;
GO
IF OBJECT_ID('dbo.fn_SQLSigCLR') IS NOT NULL
  DROP FUNCTION dbo.fn_SQLSigCLR;
GO
IF OBJECT_ID('dbo.fn_RegExReplace') IS NOT NULL
  DROP FUNCTION dbo.fn_RegExReplace;
GO