System

System.Data.SQLServer

Regular Expressions: CLR Super Strings

Hey you! Yes you, the good looking DBA ove there running that well tuned and efficient SQL Server 2000 installation. WAKE UP! The universe may be about to pass you by…

We know you’re good. Perhaps you’ve been a DBA for a while. Maybe even got to watch SQL Server come of age and naturally you were always able to stay one step ahead of the product features: soaking up all the new ANSI SQL functionality and productivity enhancing Microsoft extensions as they became available while blowing the sox off the boss with better up time and richer server side capabilities with each new release of SQL Server. These days you have the job well under control. You've had lots of time to get to know SQL Server 2000 very well. Could be you even have some time to help others on your favorite DBASIG, newsgroup or forum and have been playing around with SQL Server 2005 in your spare time. That’s all good stuff. Congratulations!

We just want to make sure you aren’t about to step out in front of the Battlestar Galactica disguised as a Mack truck class database server change coming from the blind side when SQL Server 2005 hits your door. We know you’ll be right on top of the many new engine improvements and T-SQL features coming and you’ll figure out the awesome new Integration Services toolset in short order as you master the Visual Studio-ish UI. You’re undoubtedly already using Reporting Services and even XML - in some flavor - so even Web Services and End Points really won’t be as much earth shattering as greatly appreciated. Service Broker, Snapshot Isolation, Database Mirroring (real time log shipping) and all the new data partitioning technology are likely to be nothing more than an exercise in formalizing things you are already doing. All in all, the good stuff gets better. However, CLR integration is a whole different story. Looking into the near future, it’s a safe bet that you and I are going to get to struggle to come to terms with .Net CLR integration.

This new suite of features for SQL Server 2005 is going to be the 900 pound gorilla on your back if you don’t get ready for it pronto. Some DBAs will only need to know how to turn the CLR off when SQL Server 2005 finally gets to their shop. Too bad for them! The rest of us are headed into a brave new world. We’re eventually going to need to accept that VB6 just won’t hold up the moon. We're going to have to actually know what Object Oriented Programming is (gasp) rather than just proudly state that the developers in our shop use that stuff - and then under our breath baselessly claim that we can read it. We will have to write .NET assemblies, debug .NET assemblies, compile .NET assemblies and deploy .NET assemblies in our SQL Servers. Hardest of all we’ll need to know when not to use the integrated CLR and to justify our position. We will have to support objects in our databases that we are not able to see through: in addition to metadata DDL and DML we’ll be dealing with tables full of Intermediate Language (IL) bits. In addition to executing scripts we’ll be loading .dlls using CREATE ASSEMBLY and trying to figure out how to manage version control and software lifecycle promotions for binary components within and between our SQL Servers. In addition to data access permissions we’ll be enforcing code safety. The sky is the limit!

As you already know, with the introduction of the CLR into SQL Server you are about to encounter a new day filled with truly compiled database executables - functions, stored procedures, triggers, user defined types, and aggregates. What’s not so clear - aside from how to create these new gizmos - is that you are going to be dealing with a new mind set from the developers in your organization not to mention the new attitude you must develop to survive and support the new day of CLR integration. The days of trying to get a good programmer to think in sets are not over. What’s coming is the day of getting a good programmer to know when set based is most appropriate, when compiler power is in order, when data access is the key operation of a piece of code, when the complex calculations and logic demand a true compiled code base are the right way to solve a data problem in addition to when complex algorithms are best done server side, middle tier, or at the users desktop. You are about to become a traffic cop at an intergalactic demolition derby.

The thing about the CLR from a DBA perspective is that there is no one thing you need to learn. It’s all new and it’s important that you understand many things if you intend to maintain a stable environment. You need to learn it all. Figuring out where to start is the next hard thing you should be doing today to get ready for SQL Server 2005.

Seems like if you start by looking at how the CLR can help you then the long road to understanding all that is coming will be not so steep. In this article – and in follow up articles if you provide feedback that approach us useful - is to take a look at a practical way to use the CLR to solve a real need. We’ve seen some examples of CLR code in web articles, Blogs and even the small collection of SQL Server 2005 books currently available. For the most part these pioneering articles have tended to be academic and mostly demonstrate the mechanics of creating a CLR class rather than how to solve business problems. As Billy Hollis wrote a few years ago in the excellent article, Using Stored Procedures in Visual Basic .NET:

“We writers tend to divide software into two categories—realware and demoware. Realware is the stuff that actually has to work in the real world. Demoware is written to illustrate programming concepts.

Most of the code you see in articles and books is demoware. It has to be simpler than realware; otherwise the reader would get bogged down in details that are irrelevant to the concept being illustrated. But sometimes demoware goes too far. The quest for simplicity can leave out details that developers need to write realware.”

I’d like to walk the line between Billy’s demoware and realware scenarios in the ensuing discussion about CLR integration. I do have some concepts to convey and at the same time I would like to bring them together in an almost real world example. I want to show you some powerful string manipulation tools that are very easy to get to using CLR integration yet are very different from the T-SQL we are accustomed to using.

One of the important points I‘d like to convey is that getting “realware” value out of CLR database components is not necessarily difficult. Even I can do it.

CLR Functions (UDF)

By far the easiest entry point to CLR programming for the DBA is the user defined CLR function. All you need are a basic familiarity with the Visual Studio 2005 (Whidbey) IDE, a rudimentary understanding of the .NET framework and a .NET programming language such as C# or VB.Net to begin building some amazingly powerful functions that promise to perform much better than comparable T-SQL functions. The Whidbey IDE will write code shells from templates for you and open many doors through intellisense dialogs, help you easily debug your function – stepping nimbly between T-SQL and .NET code, and even deploy your functions to a development SQL Server. Of course once you get a CLR function ready to go to production you’ll want to consider some safer ways to promote to production but no need to worry about that just yet. First you need to be able to create and open a SQL Server project, add a function partial class to your project and type a few lines of code to begin writing some very powerful stuff.

To explore the new magic consider the REGEX class. REGEX is the .NET engine for Regular Expressions. Conceptually, the combination of .NET and Regular Expressions is about as far from T-SQL as you can get in a single line of code still, regular expressions through UDFs add a mountain of capabilities to T-SQL with so much potential that we won’t fully appreciate until well after SQL Server 2005 is released to the wild even if we start today to identify those capabilities now. Consider the following example and see if you agree.

In Figure 1 you see the VB.NET code necessary to create three CLR UDFs.

The first function - GetTime - is a very simple bit of code intended to extract the time component from a datetime SQL data type. Not only is it useful to isolate the date and time components of the nullary getdate() built-in function into separate columns for indexing and query but it is also interesting to demonstrate the minimum amount of code necessary to define a function.

In the next function – IsIP – a regular expression is introduced and the string parameter is examined to determine if it is a valid IP v4 address or not. The code is still quite small but you have to admit this is considerably more difficult – if not downright impossible – to understand than the GetTime function.

In the third function – ExtractAreaCode – again a regular expression is used to parse a string and extract the area code from the first occurrence of a valid phone number in the string passed to the function.

Take a moment to look over the very few lines of code needed to create these relatively high powered sting functions.

Figure 1

Imports System

Imports System.Data.Sql

Imports System.Data.SqlTypes

Imports System.Text.RegularExpressions

Partial Public Class UserDefinedFunctions

<SqlFunction(DataAccess:=DataAccessKind.None, IsDeterministic:=True, IsPrecise:=True)> _

Public Shared Function GetTime(ByVal Value As SqlDateTime) As SqlString

' just want the time component of a valid SQL datetime

Return CType(CType(Value, DateTime).TimeOfDay.ToString, SqlString)

End Function

<SqlFunction(DataAccess:=DataAccessKind.None, IsDeterministic:=True, IsPrecise:=True)> _

Public Shared Function IsIP(ByVal Value As SqlString) As Boolean

' determine if the value is a valid IP4 formatted value

' 4 dot "." delimited values between 0 and 255

Dim rx As New Regex( _

"^((2[0-4]\d|25[0-5]|[01]?\d\d?)\.){3}(2[0-4]\d|25[0-5]|[01]?\d\d?)", _

RegexOptions.IgnoreCase _

Or RegexOptions.IgnorePatternWhitespace)

Return rx.Match(CType(Value, String)).Success

End Function

<SqlFunction(DataAccess:=DataAccessKind.None, IsDeterministic:=True, IsPrecise:=True)> _

Public Shared Function ExtractAreaCode(ByVal matchString As String) As SqlString

Dim rx As Regex

Dim m As Match

' looking for a 10 digit phone number with or with out area code wrapped in parens

' delimiters allowed include - . space none

rx = New Regex("(?<AreaCode>\((\d{3})\)|\d{3})(\s*|.|-)(?<Number>\d{3}(-|\s*|.)\d{4})", _

RegexOptions.None)

' store result of expression match for extraction of returned area code

m = rx.Match(matchString)

If (m.Success) Then

Return CType(m.Value.ToString.Substring(InStr(m.ToString, "("), 3), SqlString)

Else

Return SqlString.Null

End If

End Function

End Class

I think you will agree that the first function is quite easy to digest even if you have little or no experience with coding in .NET. On the other hand, the last two functions are more challenging. Not because the .NET code is any more difficult to comprehend, but because the regular expression stuff if impossible to decipher without a significant amount of a priori knowledge of regular expressions. You’d have to agree though that if these functions do what they purport, and if regular expressions are as fast as those PERL geeks have been telling us for so long, the arcane syntax of regular expressions would sure be good to know if it can do such validations and extraction operations as these in so few development keystrokes. The good news is there are some cool tools that can help DBAs quickly bang out some super regular expressions.

My favorite of the many regular expression tool is Expresso 2.1, a freeware product from Ultrapico. This tool is cool for a number of reasons. It includes a great tutorial on regular expressions, features an easy to use expression builder, has an awesome ability to analyze and test a regular expression, and will generate nicely documented VB.NET or C# code once you’ve built the regular expression you desire. In total Expresso is DBA easy and .NET aware.

Just to give you a feel for the power and ease of Expresso, consider this analysis of the regular expression stated in the IsIP UDF above. All that is necessary is to paste the regular expression into the tool and hit analyze and immediately you see this nicely detailed break down of the expression. The tool has the same finely detailed support when building new expressions.

Figure 2

Regular Expression:

^((2[0-4]\d|25[0-5]|[01]?\d\d?)\.){3}(2[0-4]\d|25[0-5]|[01]?\d\d?)

Expresso Analysis:

Beginning of line or string

[1]: A numbered capture group. [(2[0-4]\d|25[0-5]|[01]?\d\d?)\.], exactly 3 repetitions

(2[0-4]\d|25[0-5]|[01]?\d\d?)\.

[2]: A numbered capture group. [2[0-4]\d|25[0-5]|[01]?\d\d?]

Select from 3 alternatives

2[0-4]\d

Any character in this class: [0-4]

Any digit

25[0-5]

Any character in this class: [0-5]

[01]?\d\d?

Any character in this class: [01], zero or one repetitions

Any digit

Any digit, zero or one repetitions

[3]: A numbered capture group. [2[0-4]\d|25[0-5]|[01]?\d\d?]

Select from 3 alternatives

2[0-4]\d

Any character in this class: [0-4]

Any digit

25[0-5]

Any character in this class: [0-5]

[01]?\d\d?

Any character in this class: [01], zero or one repetitions

Any digit

Any digit, zero or one repetitions

Check Expresso out. You won’t be sorry. Once you are comfortable with regular expressions the door to rich potential of the CLR UDF world is flung wide open.

The next important thing you’ll want to notice about the partial class above is that CLR code is strongly typed. The VB CType function appears in all three functions because we need to satisfy the data typing needs of the CLR which are much more stringent than the implicitly generous conversions we are used to in T-SQL. Understanding how important data types are in .NET is going to be one of the keys to successfully adopting the new technology for many DBAs. While data type purity will be very new to most DBAs that have lived and dies on T-SQL and VBScript, other things are more familiar. For example, we must have a deterministic functions to prevent the “side effects” as described in the SQL Server 2000 CREATE FUNCTION documentation and the functions must be either scalar or table valued. In our example the functions are all scalar since – as we will see a little later - they will be used in table column definitions.

I’m sure that not enough has yet been written about data types and CLR integration to provide adequate support for existing SQL Server DBAs. You can find a lot of good information in SQL Server 2005 Books Online and in the various Blogs (SQL Junkies and Bob Beauchemin's Blog are good places to jump into the blogs as well. Ther are many links to other good blogs at both locations.) by folks with a focus on Yukon. Plan to watch for additional information on data types in the months to come. For safe mode UDFs and CLR Stored procedures the rules are perhaps more complex than necessary yet manageable. In order to harness the full potential of EXTERNAL_ACCESS and UNSAFE mode I suggest you get to know the .Net guru in your shop quite well by the end of 2005. While the system assemblies available on the server side is limited, it’s a little like only being able to pick only from the stars you can see on a moonlit night.

Once you have created a useful CLR database component you must be concerned with getting the assembly into the database server. Assemblies are the basic unit of compiled .Net Code and assemblies are the basic CLR unit of deployment into the database. In fact, “Deploy” is a compiler menu option in Visual Studio 2005 along with “Build” and “Rebuild”.

CREATE ASSEMBLY and CREATE FUNCTION are easy to script once an assembly been deployed using the Visual Studio menu oprtion. Doing so will provide some good insight into not only what the Visual Studio UI is doing but also to what you will need to do to deploy CLR database components to acceptance and production databases using best practice scripting techniques. Deployment does seem to be a moving target in the still adolescent Whidbey/Yukon product mix. The ability to get a CLR Assembly from the Visual Studio workstation to the SQL Server seems to be getting smoother and easier as we get closer to the release version. I have a feeling in Beta 3 and beyond we will see it mature nicely.

Even with the post beta 2 builds it’s pretty easy to see what is going on and what we will need to do to manually deploy a CLR component. Taking a look at the SSMS generated script in figure 3 for the functions in figure 1:

Figure 3

CREATE ASSEMBLY:

USE [AdventureWorks]

CREATE ASSEMBLY [SqlServerProject1]

AUTHORIZATION [dbo]

FROM 0x{assembly bits as big hairy varbinary literal}

WITH PERMISSION_SET = SAFE

EXEC sys.sp_addextendedproperty

@name=N'AutoDeployed'

, @value=NULL

, @level0type=N'ASSEMBLY'

, @level0name=N'SqlServerProject1'

EXEC sys.sp_addextendedproperty

@name=N'SqlAssemblyProjectRoot'

, @value=N'D:\MyProjects\SqlServerProject1\SqlServerProject1'

, @level0type=N'ASSEMBLY'

, @level0name=N'SqlServerProject1'

The assembly can definitely be promoted using the varbinary literal. It is also quite easy to use the information provided in this script to produce a much more readable and supportable CREATE ASSEMBLY script for promotion. Note from the Books Online documentation for CREATE ASSEMBLY that a “client_assembly_specifier” may be provided in place of the assembly bits varbinary. Furthermore, the SqlAssemblyProjectRoot extended property provides the path to the assembly dll. All you need to do is tack the name of the .dll to the end of this path and you have a “client_assembly_specifier”. Most likely you’ll want to convert this local logical path to a UNC path and you’ll have a network location from which to deploy and store the IL for your CLR function. One final note here is that CLR assemblies containing user defined types (UDT) must be deployed to all clients that will use the type in addition to the server.

Going one step deeper into the CLR DDL metatdata, we can learn a bit more about the function by taking a look at the CREATE FUNCTION scripts in figure 4:

Figure 4

CREATE FUNCTION:

USE [AdventureWorks]

CREATE FUNCTION [dbo].[IsIP](@Value [nvarchar](max))

RETURNS [bit] WITH EXECUTE AS CALLER

EXTERNAL NAME [SqlServerProject1].[SqlServerProject1.UserDefinedFunctions].[IsIP]

EXEC sys.sp_addextendedproperty

@name=N'AutoDeployed'

, @value=NULL

, @level0type=N'SCHEMA'

, @level0name=N'dbo'

, @level1type=N'FUNCTION'

, @level1name=N'IsIP'

EXEC sys.sp_addextendedproperty

@name=N'SqlAssemblyFile'

, @value=N'Function1.vb'

, @level0type=N'SCHEMA'

, @level0name=N'dbo'

, @level1type=N'FUNCTION'

, @level1name=N'IsIP'

EXEC sys.sp_addextendedproperty

@name=N'SqlAssemblyFileLine'

, @value=17

, @level0type=N'SCHEMA'

, @level0name=N'dbo'

, @level1type=N'FUNCTION'

, @level1name=N'IsIP'

In the CREATE FUNCTION script’s extended properties we are able to learn a good deal about the Visual Studio source code file. The file name is available as is the line within that source file where the function can be located. The all important namespace for the function can also be seen in the CREATE FUNCTION statement.

Notice that the supplied parameter value for the function is by default a varchar(max) even though we can be certain that a valid IP4 Ip address will have at most 15 characters.

Performance Possibilities

Consider how these simple functions might be used in an application’s schema. The first function, GetTime provides a mechanism to support the columnar isolation of the date component and the time component of the intrinsic SQL Server datetime datatype. In the case where indexing by time or even grouping by date might be useful this function should provide an easy and compiled (e.g. speedy) mechanism to achieve that isolation. Likewise, the ExtractAreaCode function will provide an easy method to achieve indexing/grouping by area code as a computed column. In the example in figure 5 some additional rules necessary to achieve index-ability will be demonstrated. The IsIP function is used as a check constraint that promises to be a much better performer than a comparable T-SQL function that would provide the same string parsing capability.

In the example schema the CLR functions are implemented with the expectation that the compiled bits offer fundamental performance advantages over T-SQL scripts. It’s important to caution that the performance benefits are not quantifiable or even truly verifiable with the beta software available at the time of this writing. In the months ahead we will be able to quantify the performance benefits of compiled CLR database components. That would suggest some content for a follow-up article to see just how well the finished product meets expectations.

Figure 5

TEST SCRIPT AND SCHEMA:

-- no need to drop the procedure to recompile functions

-- drop procedure dbo.GetAreaCode

create procedure dbo.GetAreaCode

set nocount on

-- functions must be owner qualified

select dbo.ExtractAreaCode('1115551234')

select dbo.ExtractAreaCode('234-555-1234')

select dbo.ExtractAreaCode('333 555 1234')

select dbo.ExtractAreaCode('(456) 555-1234')

select dbo.ExtractAreaCode('789.555.1234x123')

select dbo.ExtractAreaCode('1-303-555-1234')

select dbo.ExtractAreaCode('(454)555-1234')

select dbo.ExtractAreaCode('My phone number is (999)888-7777')

set nocount on

exec dbo.GetAreaCode

select '255.255.255.255' [IP], dbo.IsIP('255.255.255.255') [Is Ip]

select '127.0.0.1' [IP], dbo.IsIP('127.0.0.1') [Is Ip]

select '10.257.0.1' [IP], dbo.IsIP('10.257.0.1') [Is Ip]

select '257.10.0.1' [IP], dbo.IsIP('257.10.0.1') [Is Ip]

select '256.256.256.256' [IP], dbo.IsIP('256.256.256.256') [Is Ip]

select 'A.B.C.D' [IP], dbo.IsIP('A.B.C.D') [Is Ip]

select dbo.gettime(getdate())

select dbo.gettime('2001-1-1')

select dbo.gettime('2013-13-13')

-- cannot have tables that reference the function to redeploy the CLR function

drop table dbo.DialUpLog

-- column that references a CLR function must be persisted to be index-able

-- the value derived in the calculated column must be an index-able data type

create table dbo.DialUpLog

(PhoneNumber varchar(20)

, AreaCode as cast(dbo.ExtractAreaCode(PhoneNumber) as smallint) Persisted

, IP varchar(20)

, RecCreatedDt smalldatetime default convert(varchar(10),current_timestamp,121)

, RecCreatedTime as cast(dbo.GetTime(getdate()) as varchar(12))

, constraint pkn_DialUpLog__AreaCode__IP

primary key (AreaCode, IP)

, constraint ckIP check (dbo.IsIP(IP) = 1))

create index ixn_DialUpLog__AreaCode

on dbo.DialUpLog (AreaCode)

-- the table does not have to be owner qualified if I own the schema

insert DialUpLog (PhoneNumber,IP) values ('555-666-7777', '10.20.30.227')

insert DialUpLog (PhoneNumber,IP) values ('1-719-555-1234', '192.168.18.5')

insert DialUpLog (PhoneNumber,IP) values ('1-719-555-1234', '192.168.500.5')

insert DialUpLog (PhoneNumber,IP) values ('1-719-555-1234', '192.168.5.500')

insert DialUpLog (PhoneNumber,IP) values ('1-719-x55-1234', '192.168.18.5')

select * from DialUpLog

The first item in the script is a stored procedure that uses the ExtractAreaCode function. In a real “realware” application the stored procedure would probably accept the input string as a parameter. Here we want to demonstrate that T-SQL procedures and functions that reference a CLR class do not need to be dropped if you redeploy the CLR assembly. This is interesting because – as you can see later in the test script and the test output below – other T-SQL database objects such as tables or constraints do have to be dropped – or at least the CLR component references must be removed – before the CLR assembly can be redeployed after a modification. In the interest of brevity we are also using the stored procedure to produce a variety of output results from calls to the CLR function. One final point concerning this procedure is that the output in figure 6 helps to show the possible problems with the behavior of varchar(max)/SQLString data types between CLR methods and T-SQL code. In the first result the header is untouched. Notice that even though the output value should always be 3 digits the header indicates that the returned SQLString is 255 characters long. Don’t be surprised if you hit a snag trying to stuff the area code into a char(3) column without converting it. (Note that in the other output values from the procedure call the headers have been modified by the author.)

After the GetAreaCode are a few select statements to demonstrate the out put from the IsIP and GetTime functions.

Finally we come as close as we will get to “realware” in this article. A table is created that is used to log information for calls by IP address and some data is inserted into this logging table. The ExtractAreaCode function is used to define a computed column that is declared as “Persisted”. This means that the column will actually be stored with each row and also that we will be able to index the column. The persisted computed column is used in the primary key as well as a non-clustered index. This is – as you know being the top notch DBA you are – a silly and senseless indexing scheme intended only to show that the persisted column created from the CLR function is index-able in both ways. The IP column uses the IsIP function as a check constraint. The date and time are stored in isolated columns through the use of the GetTime function however, since the time column is not persisted, we cannot index time column. Easy enough to change if desired.

Figure 6

TEST RESULTS:

Msg 2714, Level 16, State 3, Procedure GetAreaCode, Line 14

There is already an object named 'GetAreaCode' in the database.

----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

111

--…--

234

--…--

333

--…--

456

--…--

789

--…--

303

--…--

454

--…--

999

IP Is Ip

--------------- -----

255.255.255.255 1

IP Is Ip

--------- -----

127.0.0.1 1

IP Is Ip

---------- -----

10.257.0.1 0

IP Is Ip

---------- -----

257.10.0.1 0

IP Is Ip

--------------- -----

256.256.256.256 0

IP Is Ip

------- -----

A.B.C.D 0

--…--

22:12:43.1200000

--…--

00:00:00

--…--

Msg 242, Level 16, State 3, Line 14

The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.

Msg 547, Level 16, State 0, Line 1

INSERT statement conflicted with CHECK constraint 'ckIP'. The conflict occurred in database 'AdventureWorks', table 'DialUpLog', column 'IP'.

The statement has been terminated.

Msg 515, Level 16, State 2, Line 1

Cannot insert the value NULL into column 'AreaCode', table 'AdventureWorks.dbo.DialUpLog'; column does not allow nulls. INSERT fails.

The statement has been terminated.

PhoneNumber AC IP RecCreatedDt RecCreatedTime

--------------- --- ------------- ------------------- --------------

555-666-7777 555 10.20.30.227 2005-01-16 00:00:00 22:12:43.753

1-719-555-1234 719 192.168.18.5 2005-01-16 00:00:00 22:12:43.753

1-719-555-1234 719 192.168.5.500 2005-01-16 00:00:00 22:12:43.753

As you can see, if we try to insert values that result in errors in the CLR code the inserts fail and relevant error messages are raised. If we insert valid data we end up with some potentially useful data. All just as we would expect.

Hopefully you have seen something in this article that will inspire and compel you to begin – or continue - your journey into server side CLR programming. Perhaps a light or two went on as you read this article and you know just where you’d like to go next. Certainly there is a universe of new possibilities to explore on this topic. All we were able to look at here were but a few grains of sand on the moon of a small and obscure planet REGEX. SQL Server 2005 beta 3 should be streaking across the sky any day now. Don’t miss your chance to grab a copy when it comes your way to further prepare yourself for the coming jump.