CLR Integration in SQL Server 2005


SQL Server 2005 leverages integration with CLR to allow the development, deployment, and execution of database programmatic objects. It supports the following object types:

  • Stored procedures

  • User-defined functions

  • Triggers

  • User-defined (data) types

  • User-defined aggregates

Pros and Cons

Some advantages of CLR integration in SQL Server 2005 are listed here:

  • Rich programming model .NET languages are very productive since they offer constructs and features that are not available in Transact-SQL (such as arrays, classes, and collections).

  • Access to the .NET Framework library This library contains a vast set of classes that cover all sorts of programming challenges.

  • Cross-language integration You can integrate code developed in different programming languages.

  • Improved security and stability CLR manages various aspects of the execution of IL assemblies. As a result, the number of potential security holes is reduced, and the stability of the whole system is improved.

  • Object lifetime management Automatic garbage collections, destructors, and lease policies simplify management.

  • Potential for performance and scalability improvements Some solutions can be developed to execute faster or to scale better in .NET Framework languages, or by using the .NET Framework Library. Managed code is a better selection for CPU-intensive operations or complex tasks such as string manipulation, cryptography, and file access.

However, managed code may also degrade the system. The performance or scalability of a system can be degraded if .NET database objects are used for operations for which they are not optimal. One typical example is using .NET code to get something from a database and processing it record by record, when the same type of operation could be done using a set operation (a SQL statement).

In addition, the complexity of the system could be increased and maintainability degraded if you make uncritical use of CLR user-defined types for data that should actually be stored as separate columns in an existing or a new table.

Supported and Not Supported

In this section, I will give you an overview of the functionality that is supported or not supported when you create CLR database objects. If you are new to .NET languages, you should skip this section.

It is not possible to use all base class libraries from CLR database objects, but most System.* assemblies/namespaces are available, including:

  • mscorlib.dll

  • system.data.dll

  • system.dll

  • system.xml.dll

  • system.security.dll

  • Microsoft.visualbasic.dll

  • system.web.services.dll

Support for functionality that is not appropriate for database server objects is eliminated:

  • System. Windows.Forms

  • System. Drawing

  • System. Web

Even in supported assemblies, some APIs are not supported, such as the Environment.Exit() method and the Console class. The .NET Framework contains a new class of attributes—HostProtectionAttribute (HPA)—designed to declare which methods and classes are too risky to be loaded into an unmanaged host such as SQL Server.

Potentially unreliable constructs are also disabled:

  • No thread creation

  • No listening on sockets in server

  • No finalizers

  • No shared state or synchronization

As a final approval step, SQL Server checks assemblies through a verification process when the assembly is being attached to a database object (in the Create statement).

Minimally Required Namespaces

To be able to create CLR database objects, you need to add references to the following namespaces in your modules:

 Using System.Data; Using System.Data.Sql; Using System.Data.SqlTypes; Using Microsoft.SqlServer.Server; 

These namespaces are all part of the system.data.dll assembly, which is a part of the .NET Framework. You do not have to reference it either in Visual Studio or in the command line because it is referenced automatically.

Naturally, in Visual Basic .NET you would insert something like this:

 Imports System.Data Imports System.Data.Sql Imports System.Data.SqlTypes Imports Microsoft.SqlServer.Server 

It is also possible to register .NET Framework assemblies and namespaces that are not by default available for development of CLR database objects. I will demonstrate this in Chapter 13.




Microsoft SQL Server 2005 Stored Procedure Programming in T-SQL &  .NET
Microsoft SQL Server 2005 Stored Procedure Programming in T-SQL & .NET
ISBN: 0072262281
EAN: 2147483647
Year: 2006
Pages: 165

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net