Using the Common Language Runtime in SQL Server

In SQL Server 2000 and earlier versions of SQL Server, you had one language

to use in the database layer: T-SQL (Transact-SQL). T-SQL is well

suited to tasks such as data storage and retrieval but it is not an all-purpose

programming language. Many programming tasks were difficult or impossible

to do with T-SQL.

If it was possible to do these tasks in T-SQL, often the code to carry out the

tasks was verbose and complex. Writing the necessary code was difficult and

maintaining the code was problematic. Another result of the limitations of

T-SQL for tasks other than data manipulation was that developers often

turned to extended stored procedures to carry out tasks that T-SQL was poorly

suited to undertake. Problems with extended stored procedures include lack

of security and reliability.

In SQL Server 2005, Microsoft has added support to allow you to use managed

code in the database layer. Managed code is code that runs in the .NET

Framework's Common Language Runtime (CLR). The support for the Common

Language Runtime means that you, or developer colleagues, can use code created

in Visual Basic .NET or Visual C# .NET inside SQL Server 2005.

Languages such as Visual Basic.NET and C#.NET are much better suited than

T-SQL to many programming tasks, such as numeric manipulation, just to

name one. So, for example, if you have complex number crunching that you

want to do on some data, how could you best get the job done? In SQL Server

2000, you would probably have had to use an extended stored procedure. In

SQL Server 2005, you have a new, more reliable, and more secure option to

use the built-in Common Language Runtime capabilities.

SQL Server 2005 controls how the code runs in the CLR. If a CLR process is

using too much memory or CPU cycles, SQL Server can shut the process

down, which ensures that SQL Server continues to run efficiently.

CLR Integration

SQL Server 2005 hosts the .NET Framework 2.0 Common Language Runtime

(CLR). This is the same version of the .NET Framework that Visual Studio

2005 uses. You can write code in any .NET language including

Visual Basic .NET

Visual C# .NET

Visual C++

Other .NET language can also produce the intermediate language (IL) that the

CLR supports. In Visual Studio 2005, it is the previously listed languages that

are supported in terms of creating .NET projects. Most developers of CLR

code that's intended to run in SQL Server 2005 use one of these three languages

and create the project in Visual Studio 2005.

You can possibly use a development environment other than Visual Studio

2005. However, Visual Studio 2005 provides such closely integrated support,

including a SQL Server Project template, that many developers make it their

first choice for creating managed code to run in SQL Server 2005.

You can use one of the .NET languages to create any of the following:

Procedures

Triggers

Functions

User-defined types

User-defined aggregates

Visual Studio 2005 supports the following tasks for managed code intended

for use in SQL Server 2005:

Development

Deployment

Debugging

Development

Visual Studio 2005 has a new project type — the SQL Server Project — for

development of CLR projects in SQL Server 2005. You use that to create a CLR
project as in Figure 1

Figure 1

The Visual Studio environment makes working with Visual C# code or Visual

Basic .NET code easy. The SQL Server project has many new screens.

Detailed steps of using the SQL Server project in the Visual Studio environment
are beyond the scope of this article as in Figure 2.

Figure 2

Visual Studio 2005 has support for many useful debugging features. You can

debug seamlessly across the language boundaries between T-SQL and Visual

Basic .NET or Visual C#. Equally, the type of connection to the SQL Server

isn't important because both HTTP (HyperText Transfer Protocol, the protocol

used on the World Wide Web) and TDS (Tabular Data Stream, the protocol

used by SQL Server) are supported.

Manual coding and deployment

If you choose to create your .NET code manually and deploy it in the same

way, you need to follow these broad steps:

When writing stored procedures, functions, and triggers, the .NET class

is specified as static if written in C# or specified as shared if written

in Visual Basic .NET.

User-defined types and user-defined aggregates are written as full

classes.

The developer compiles the code that he has written. This creates an

assembly.

After creating the assembly, you use the CREATE ASSEMBLY statement

to upload the assembly into SQL Server.

To create a T-SQL object corresponding to a procedure contained in an

assembly, you use the CREATE PROCEDURE statement. You use the

CREATE FUNCTION, CREATE TRIGGER, CREATE TYPE, and CREATE

AGGREGATE statements for the same purpose for functions, triggers,

types, and aggregates respectively.

After creating a T-SQL object, then you can use the object in your T-SQL

code in the normal way.

To create a simple Visual C# example and deploy it manually to the local SQL

Server instance, follow these steps:

1. Open a text editor and type the following C# code:

using System;

using System.Data;

using Microsoft.SqlServer.Server;

using System.Data.SqlTypes;

public class SQLProc

{

[Microsoft.SqlServer.Server.SqlProcedure]

public static void SQLProcTest()

{

SqlContext.Pipe.Send("The  SQLProc example

works!\n");

}

}

Notice the use of the System, System.data, Microsoft.SQLServer.

Server, and System.Data.SqlTypes namespaces. You use these

namespaces often when writing .NET code for use in SQL Server 2005.

2. Navigate to the location of the C# compiler. It's located in C:\Windows\

Microsoft.NET\Framework\v2.0.50727. At the command line, type

csc /target:library C:\location of CSharp

File\SQLProc.cs

A dll called SQLProc.dll is created in the .NET Framework folder.

More often, you would add the .NET Framework folder to your PATH

environment variable.

3. Open SQL Server Management Studio and click the Database Engine

Query button to create a new database engine query. Create an

assembly in the desired SQL Server 2005 instance, using this code:

CREATE ASSEMBLY SQLProc

FROM

'c:\windows\microsoft.net\framework\v2.0.50727

\SQLProc.dll'

WITH PERMISSION_SET = SAFE

Notice the permission setting is SAFE, because the procedure does not

need to access anything external to SQL Server.

4. Create a procedure called SQLProc by using this code:

CREATE PROCEDURE SQLProc

AS EXTERNAL NAME SQLProc.SQLProcTest.SQLProc

5. Try to execute the SQLProc procedure by using the following code:

EXEC SQLProc--Will fail since CLR is not enabled

Unless you have explicitly turned on the CLR support, attempting to run

the stored procedure fails.

6. To enable the CLR, run the following code:

sp_configure 'clr enabled', 1

GO

RECONFIGURE

GO

7. Execute the SQLProc user-defined procedure that you created earlier.

EXEC SQLProc-- Now it will execute successfully

Comparison with Traditional Approaches

In this section, I look briefly at the potential benefits of CLR integration in

SQL Server 2005 and then briefly compare using CLR with each of three traditional

approaches:

T-SQL

Extended stored procedures

Middle tier techniques

Potential benefits of CLR integration

The integration of the CLR offers developers the following advantages:

A richer programming model: You have access to programming constructs

that are absent from T-SQL. In addition, you have access to the

classes of the .NET Framework and can use those classes as a basis for

your code.

Improved security: Compared to the extended stored procedures you

might have used with SQL Server 2000 to carry out tasks not possible or

convenient with T-SQL, the CLR offers improved security.

User-defined types and aggregates: You can use .NET languages to

create your own user-defined types and aggregates.

Development in a familiar development environment: Many developers

are already familiar with using one of the versions of Visual Studio

before Visual Studio 2005. For such developers, creating SQL Server

projects in Visual Studio 2005 is an easy step, building on what they

already know.

Potentially improved performance: The .NET languages potentially

offer improved performance and scalability.

T-SQL lacks many constructs used in more general purpose programming languages.

For example, it does not have arrays, for each loops, collections, or

classes. By contrast .NET languages, such as Visual Basic .NET and Visual C#

.NET, has support for the preceding constructs and also has object-oriented

capabilities such as inheritance, encapsulation, and polymorphism. When the

purpose of the code is not simply to manipulate data, the .NET languages and

the CLR can be a better choice.

The Base Class Library has many classes that support useful functionality

including numeric manipulation, string manipulation, file access, and cryptography.

If you need to carry out complex numeric manipulation of data, it is

likely that Visual Basic .NET or Visual C# .NET is a better choice than T-SQL.

Similarly, if you need to carry out complex text handling, the regular expression

support in Visual Basic .NET and Visual C# .NET provides much more

control than, for example, the LIKE keyword in T-SQL.

SQL Server 2005 doesn't support all the classes that are part of the .NET

Framework 2.0. The code is intended to run inside SQL Server 2005, so some

classes — for example, those for windowing — are inappropriate in that context

and are not supported.

The Common Language Runtime provides a safer environment for code to run

in. For example, it prevents code reading memory that hasn't been written and

helps avoid situations where code accesses unmanaged memory. In addition,

type safety in the CLR ensures that types are manipulated only in appropriate

ways. Taken together, these features of the CLR remove many causes of errors.

For larger projects, the ability to organize code by using classes and namespaces

allows the developer to structure the code in a way that is more easily

understood. Such improved code structure allows you to easily create code

and also more easily maintain the code