One of the most anticipated features of soon to
be released SQL Server 2005 is the integration of the .Net CLR into
the SQL Server engine. This article is going to discuss just what
that means and give you a first peak into how write .Net code and
use it in SQL Server 2005. If you haven’t heard, SQL Server 2005
will ship on November 7th of this year along with the
.Net Framework 2.0, and the new Visual Studio 2005.
The Common Language Runtime (CLR) is the
execution environment of .Net. In .Net code is compiled from a high
level language such as C# or VB.Net into an abstract Intermediate
Language (IL). The framework SDK includes compilers for the C# and
VB.Net language, which can be used from the command line. However,
most developers use an Integrated Development Environment (IDE),
such as Visual Studio to work with high level language code and to
invoke the compiler.
Once IL code is compiled it isn’t executed
directly. Instead, when the time comes to run compiled code, the
CLR is invoked and the IL code is translated into machine language
by the Just-In-Time (JIT) compiler. The translated machine code is
what executes. However, it just doesn’t run on its own. It runs in
within the CLR of the.Net Framework.
It’s the CLR that “hosts” the code that
programmers create. And hosting involves much more than just
loading the program. The CLR is an environment that executes the
code in a very controlled way that includes memory management,
security, and many other aspects of running a program. The CLR
includes a large class library that .Net programs use to work with
the operating system and with other programs, such as databases and
web servers.
There are several CLR hosts in use today.
Among them are ASP.Net and Windows. SQL Server 2005 is another CLR
host. Before we get to how the CLR functions as a host, let’s
discuss the others.
When a program written in a .Net language, such
as C# or VB.Net is compiled and linked into an EXE file a standard
Windows PE file is created. This PE file includes a small amount of
compiled Windows code that starts up the CLR, causes the CLR to
invoke the JIT compiler and execute the code. This small piece of
Windows code is essential because the CLR isn’t really built into
Windows but on top of it.
ASP.Net is another environment that hosts the
CLR. The worker process host DLLs written to implement the code
behind ASP.Net pages. There can be additional hosts and that’s
where we come to SQL Server 2005.
SQL Server 2005 hosts the .Net CLR and will
load and execute DLLs, which can also be referred to as assemblies.
Right now assemblies maybe created in C# or VB.Net. Other .Net
languages will probably be supported in the future.
Before doing any .Net coding in SQL Server you
must enable the CLR. Along with many other features in SQL Server
2005, the CLR is “off by default”. This is done in an effort to
limit security vulnerabilities. Out first piece of code is the
script that you’ll need to enable at the CLR. Here it is:
EXEC sp_configure 'show advanced options' , '1';
go
reconfigure;
go
EXEC sp_configure 'clr enabled' , '1'
go
reconfigure;
go
Now let’s write a User-Defined Function (UDF).
You don’t have to use Visual Studio to write .Net code for SQL
Server but it makes the job much easier. Since this is a
newsletter, it’s difficult to show Visual Studio in action so we’ll
have to stick to the text.
The first step is to create a “SQL Server
Project” in your preferred language, either C# or VB.Net. For most
of this newsletter we’ll be using VB.Net. Although I use both
languages customer request have led me to use a lot of VB.Net on
recent projects. When you have the project created add an item and
you to pick one of the types of .Net based database objects that you
can create:
- Stored Procedure
- User-Defined Function
- Trigger
- User-Defined Type
- User-Defined Aggregate
I picked a User-Defined Function and Visual
Studio used a template to create the framework of a UDF. I’ve
changed the name of the function and added some functionality and
here’s the function:
Imports System
Imports System.Data
Imports System.Data.Sql
Imports System.Data.SqlTypes
Imports Microsoft.SqlServer.Server
Imports System.Text.RegularExpressions
Partial Public Class UserDefinedFunctions
<Microsoft.SqlServer.Server.SqlFunction()> _
Public Shared Function RegExMatch(ByVal Input As String, _
ByVal Pattern As String) As
SqlBoolean
Dim RegexObj As Regex = New Regex(Pattern)
Return RegexObj.IsMatch(Input)
End Function
End Class
Among the key things to pay attention to are
the attributes between the angle brackets <> on the line before the function declaration.
Attributes are .Net constructs that are added to the Metadata in the
assembly that we’re creating. Metadata is non-code information
about the code. In this case the attribute specifies that the
function RegExMatch is a SqlFunction. The function it self is a
simple function that takes an Input string and a pattern, written in
the .Net regular expression syntax. Regular expressions are
powerful pattern matchers similar to the LIKE operator in SQL but an
order of magnitude more powerful. I’ve picked regular expressions
for the function because it’s a function that couldn’t be duplicated
in T-SQL. As you can see in VB.Net it’s just two lines.
I’ve let Visual Studio compile and deploy the
function to the SQL Server. In the process the Assembly (the DLL)
is copied into SQL Server and the bits reside in a system table. As
part of process of deploying our function into the SQL Server,
Visual Studio writes and runs a small bit of T-SQL that creates the
SQL UDF and associates it with the .Net function. We’ll look into
that in more depth in a future article.
The T-SQL UDF has the same name of the
function, ReExMatch. Here’s a script that uses the function. The
regular expression pattern that’s in the script matches capitol
vowels followed by the letter B. Here’s the script:
select dbo.RegExMatch('EB5cde', '[AEIOU]B') [Test 1]
, dbo.RegExMatch('CBdef', '[AEIOU]B') [Test 2]
, dbo.RegExMatch('ODBdef', '[AEIOU]B') [Test 3]
, dbo.RegExMatch('UBdef', '[AEIOU]B') [Test 4]
, dbo.RegExMatch('KBdef', '[AEIOU]B') [Test 5]
(Results)
Test 1 Test 2 Test 3 Test 4 Test 5
------ ------ ------ ------ ------
1 0 0 1 0
This is just the beginning. As mentioned above,
there are several other types of .Net database objects and we’ll
explore them in the weeks to come.
