SQL Server 2005 is loaded with new features and enhancements, and when I was asked to pick one of these new features and write an article about it, I thought it would be easy! There is an abundance of new features to choose from, this making the decision more difficult than I would have liked. For example, I really like the new T-SQL enhancements such as Common Table Expressions (CTE) and recursive queries, PIVOT and UNPIVOT operators, Query Notifications, and DDL Triggers. In fact, my next article could possibly be on CTE’s since they have come in handy many times lately.
After some thought, however, two topics stood out that as a SQL/.NET developer made the decision much easier. Those two topics are:
- The new XML data type
- The integration of the Common Language Runtime (CLR)
Today’s column is on topic number two which has caused quite a stir within the SQL Server community. I’m not a DBA by any stretch of the imagination but there are many DBA’s who hit the panic button as soon as they found out that Microsoft was integrating the CLR into SQL Server 2005. For them, this meant anywhere from T-SQL being replaced to giving the application developers free reign to the database and everything in between. Fortunately, none of this is true. This article will discuss the integration of the CLR in SQL Server 2005 and what that means for both DBA’s and .NET developers alike.
CLR Integration Overview
SQL Server 2005 has integrated the Common Language Runtime, hosted within the SQL Server database Engine, providing the ability to create a plethora of database objects written in .NET languages like C# Visual Basic .NET. These objects include stored procedures, triggers, and functions and support logic and features that are either not available it native T-SQL or can be enhanced by utilizing the power and flexibility of the .NET Framework.
The CLR is the heart and soul of the .NET Framework. It provides the environment for the execution of all the .NET Framework code, and is also the foundation for many of the built-in services that are required for .NET Framework programs to run, such as thread management, memory management, and Just-In-Time (JIT) code compilation.
The question has been asked many times why it would be necessary to integrate the CLR into SQL Server, and depending on who you ask, you will get different answers. The goals of integrating the CLR into SQL Server boil down to the following:
- Scalability
- Reliability
- Performance
- Security
- Management Services
Scalability
One of the fundamental aspects is knowing how SQL Server and the CLR handle memory and other processes. When developing CLR components to be run within the SQL Server engine, a knowledge of how the two differ in managing memory is key.
SQL Server uses a non-preemptive threading model where threads occasionally give up execution to another thread, but the CLR uses a preemptive model. As well, the CLR cannot distinguish between virtual and physical memory where SQL Server has the ability to tell one from the other which allows physical limits to be set and managed by SQL Server. This information comes in handy because as you start adding CLR objects, you want to be cognizant of the performance that object has on SQL Server as well as allowing a conflict between two competing processes.
Reliability
As you develop CLR objects, one of your main objectives is to make sure those objects are “safe”, meaning that it should not and cannot compromise the integrity of the SQL Server engine and the process in which it is running.
For example, your CLR object should not change the structure of the underlying database.
Performance
It should to without saying that any CLR code you implement should not perform any worse that its T-SQL equivalent. Your managed code should perform as well or better than what you would have written in T-SQL, and given the fact that by integrating the CLR into SQL Server, both the data and the code are brought much closer together allowing you to take advantage of the processing power of server.
Security
I didn’t put this list in any particular order since they are all important, but if I were to rank this list, I would put security near the top of the list. This item is vital, in that, one of the primary security reasons for taking advantage of the CLR in SQL Server is that much less data needs to leave the server, lowering the risk of exposing your data.
CLR objects also must have a way of accessing machine resources that are outside of the database engine. To allow such access, managed code must following the same strict authentication and authorization rules to ensure that no unwanted process can gain access to the database without going through the proper channels.
To allow for this, three levels of access exist within the security model for managed code. This security model is called CAS which stands for Code Access Security and manages the security between the different types of CLR and non-CLR objects that run within a SQL Server instance. Those three levels are:
SAFE
SAFE permissions allow access to internal and local data, but no access to any external resources is allowed, such as files and directories. It is the most restrictive permission.
EXTERNAL_ACCESS
EXTERNAL_ACCESS permissions contain the same permissions as SAFE but also include access to external resources such as files and directories, network recourses and the registry.
UNSAFE
Not recommended, but available for use, UNSAFE permissions allow access to all external and internal resources, and as well can call unmanaged code.
Management Services
Last, but not least, is the ability to manage certain functionality such as threading and memory garbage collection (returning unused memory back to the operating system). Within the CLR, you as a developer have more control over functionality such as these whereas SQL Server handles these itself, and understanding how the CLR manages these can be an advantage to you as a developer when trying to get the most out of you application.
T-SQL Limitations
There is no argument that what T-SQL does, it does very well. Its strength is in data access and set-based operations within a database. SQL Server 2005 has even come out with a number of T-SQL enhancements that build on the already great T-SQL syntax such as CTE’s (Common Table Expressions), new analytical functions such as RANK, and relational operators such as APPLY, PIVOT, and UNPIVOT.
But within the same breath one has to admit that it does have its limitations. The following is an example of what cannot be done it T-SQL but can easily be done in .NET:
- Arrays
- Collections
- Classes
- FOR EACH loops
This list is certainly not a complete list, but does provide an example of where the strengths of T-SQL lie and where the CLR can come in and help fill those gaps.
By now you should have the concept that the CLR is not a replacement for T-SQL, but a compliment to it. T-SQL is not an inferior language at all; in fact it is quite the opposite. T-SQL, as stated earlier, shines in what it is good at, data access, manipulation, and set based operations. The CLR compliments T-SQL by filling the gaps where it is not strong at.
CLR Advantages
The CLR offers to T-SQL what T-SQL cannot do for itself; that is, better support for string manipulation and complex logic, and offer true object oriented functionality within SQL Server via the CLR integration. All of the functionality that exists in the .NET Framework can be accessed via managed code, and T-SQL based stored procedures and triggers have full access to any class in the .NET Framework.
The CLR also scrutinizes all user code prior to execution to make sure that it is safe, meaning that it won’t break anything when it executes, such as writing to memory that has already been written to.
The CLR also provides object-oriented functionality such as encapsulation (the ability to contain and control a group of related items), Inheritance (the capability for one class to inherit from another class), and Polymorphism (the ability to have multiple classes used in distinct ways even though the methods and properties are names the same).
T-SQL or the CLR?
So how do you choose between one or the other? This is certainly not an easy question to answer, but what it means is that you will need to do a little more research prior to starting a project. Choosing between T-SQL and the CLR (managed code) is not a cut and dry decision. What is helpful is realizing what the strengths and weaknesses are of each how then deciding how to apply the strengths of each to get the most out of you application.
Be prepared, however, to come to the realization that maybe the answer will be not to use the CLR at all. There may be scenarios where the CLR does not make sense. It would be foolish to implement CLR objects into your database just for the sake of doing so. Each case will need to be looked at in detail to ensure that choosing the CLR over T-SQL is the right choice. But as we found out, there are certainly advantages to using the CLR.
Enabling the CLR
Now that we have all of the groundwork out of the way it is time to get to the good stuff. By default, the CLR does not come turned on. If you plan on taking advantage of the CLR, you need to enable it. This can be done by selecting Surface Area Configuration tool from the Microsoft SQL Server 2005 -> Configuration Tools menu.
Selecting this menu option displays the Surface Area Configuration tool shown below in Figure 1.
Figure 1
From here, select the Surface Area Configuration for Features option which displays the form shown below in Figure 2.
Figure 2
On the form above, expand the MSSQLSERVER node, then expand the Database Engine node, and then select the CLR Integration option.
The right side of the form will display option to enable or disable the CLR Integration. As stated earlier, by default it is disabled. To enable CLR integration, click the “Enable CLR integration” check box and click either the Apply or OK button.
If you are a die-hard T-SQL person and like to do things via code, open up a query window in the SQL Server Management Studio and execute the following T-SQL:
EXEC sp_configure 'show advanced options', 1
GO
RECONFIGURE
GO
EXEC sp_configure 'clr enabled', 1
GO
RECONFIGURE
GO
Summary
The intent of this article is to provide a helpful explanation and overview of the CLR integration into SQL Server 2005 and hopefully dispel some of the confusion that has certainly existed regarding its integration. This article pointed out some of the main reasons for integrating the CLR both from a T-SQL limitation and CLR advantage standpoint and why it should be looked at as a complimentary feature rather than a replacement.