T-SQL Versus SQLCLR

SQL Server has traditionally shipped with the ability to support one native programming language, TSQL. With the release of SQL Server 2005, a big enhancement to the SQL Server 2005 engine is the ability to host the .NET common language runtime (CLR). This enables the writing of procedural code, such as stored procedures, functions, and triggers, using any of the .NET languages, such as Visual Basic .NET or C#. The addition of CLR support in SQL Server 2005 does not mean that T-SQL is no longer supported or that T-SQL should no longer be used. It is important to recognize that SQL Server database applications should use T-SQL as much as possible. Database applications should take advantage of the set-oriented query processor and resort to procedural programming only for expressing logic that cannot be expressed within the query language. This remains true with CLR support in SQL Server. The CLR should not be used to write procedural code that can be expressed in a single SELECT statement.

You should think of the CLR as an alternative for logic that cannot be expressed in T-SQL. A good point of reference is that the CLR should be considered to replace extended stored procedures in SQL Server or to build user-defined functions that perform logic not easily performed in T-SQL. The CLR is a complement to T-SQL, not a replacement for it.

The SQLCLR code benefits from the large number of classes and functions available as part of the .NET Framework base class library. The .NET Framework class library is much richer than the built-in functions supported in T-SQL. In addition, CLR programming languages provide rich constructs, such as arrays and lists, that are lacking in T-SQL.

The SQLCLR code is compiled code, whereas T-SQL is an interpreted language. Therefore, SQLCLR code yields better performance in certain scenarios.

Database administrators have the ability to enable or disable CLR integration in SQL Server 2005. By default, it is disabled. A new sp_configure advanced option has been added to SQL Server 2005 to control this setting. The following script enables the SQLCLR integration:

--Show advanced options EXEC sys.sp_configure 'Show Advanced Options', 1; RECONFIGURE WITH OVERRIDE GO --You can check the current value of CLR before setting EXEC sys.sp_configure 'clr enabled'; GO --Change CLR configuration EXEC sys.sp_configure 'clr enabled', 1; RECONFIGURE WITH OVERRIDE GO --Check the updated value EXEC sys.sp_configure 'clr enabled'; GO

You can also enable or disable CLR integration by using the SQL Server Surface Area Configuration tool. After the server is configured to allow CLR integration, the functions in a .NET assembly can be mapped to a SQL Server stored procedure, function, or trigger, which can then be called like any other T-SQL module. SQL Server verifies the clr enabled setting when a SQLCLR method is invoked and not when it is mapped to a T-SQL module. When a SQLCLR method is executed, if the clr enabled option is turned off, SQL Server raises an error.

When you have a .NET assembly that contains SQLCLR functions, the first step in using these functions from within T-SQL code is to import the .NET assembly by using the CREATE ASSEMBLY statement. Next, you use the AS EXTERNAL NAME clause with a CREATE PROCEDURE/FUNCTION/trIGGER DDL statement to map a T-SQL stored procedure or function or trigger to the specified .NET method. Let's assume that you have a SQLCLR .NET assembly called Test.dll, which contains a method that you want to use as a stored procedure from T-SQL code. Here is how you register the assembly, map the stored procedure, and invoke the SQLCLR code:

CREATE ASSEMBLY DotNetSQLServerSProc FROM 'C:\Dev\Test.dll' WITH PERMISSION_SET = SAFE; GO --Create procedure and map it to a method in the assembly CREATE PROCEDURE dbo.sqlclr_HelloWorld @Message NVARCHAR(255) AS EXTERNAL NAME DotNetSQLServerSProc.StoredProcedures.PrintMessage; GO --Execute the stored procedure EXEC dbo.sqlclr_HelloWorld N'Hello to the DotNet world!'; GO

The important things to note in this batch are the WITH PERMISSION_SET clause in the CREATE ASSEMBLY DDL statement and the AS EXTERNAL NAME clause in the CREATE PROCEDURE DDL statement. SQLCLR integration is discussed in great detail in Chapter 11, "SQL Server 2005 and .NET Integration."

The following section describes the T-SQL enhancements introduced in SQL Server 2005.