Monday, March 7, 2011

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:
User-defined types
User-defined aggregates
Visual Studio 2005 supports the following tasks for managed code intended
for use in SQL Server 2005:
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
The developer compiles the code that he has written. This creates an
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
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
public static void SQLProcTest()
SqlContext.Pipe.Send("The  SQLProc example
Notice the use of the System,, 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
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:
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:
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
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
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

No comments :

Post a Comment