SQLCLR, UDTs, and UDFs (OLTP)---a Technical Reference Guide for Designing Mission-Critical OLTP Solutions
Want more guides like this one? Go to Technical Reference Guides for Designing Mission-Critical Solutions.
In some application scenarios that require computation-intensive capabilities over relational and non-relational data stored in the database, the Transact-SQL language may not be able to provide the necessary semantic richness to ease development effort while achieving the desired performance characteristics. These scenarios typically include complex mathematical calculations, string manipulations, or use of regular expressions but may also involve the development of new types of database object such as data types and aggregates. In general, it is desirable to perform such processing in the application layer rather than in the database layer. However, the deployment of such computations in the application layer introduces a costly transition between boundaries that can prove expensive for large volumes of data.
With the integration of Microsoft .NET Common Language Runtime (CLR) in the Microsoft SQL Server runtime, the solution architect has a new powerful set of tools to meet the needs of such scenarios, letting the architect avoid the need to deploy the code in the application layer. The .NET Framework offers a very rich set of prebuilt and pretested classes, many of which can be utilized within SQL Server.
The SQL Server CLR (SQLCLR) capability can extend existing Transact-SQL functionalities. Additionally, SQLCLR streamlines access to external resources and systems, such as interacting with the operating system or existing service oriented solutions through full web services support. CLR based code is commonly referred to as managed code.
This section provides some best practice guidance and resources for more information. (Note that the full URLs for the hyperlinked text are provided in the Appendix at the end of this document.)
Overview of SQL Server CLR Integration
For a general introduction to SQL Server CLR integration, see the article Common Language Runtime (CLR) Integration Programming Concepts1 Unlike other database engines, SQL Server takes the approach of directly hosting the CLR engine. This means that SQL Server has control over the actions of the CLR. From the perspective of the CLR, SQL Server is providing the environment normally provide by the operating system. The level of control provided is a key reason for the safety and stability of SQL Server CLR integration.
It is important to understand the security framework provided by SQL Server CLR integration. The article CLR Integration Code Access Security2 provides a good introduction.
Accessing database data from within managed code within SQL Server is similar to accessing the same data from outside SQL Server. However, while a standard database connection could be made from the managed code using standard network libraries, this is not necessary for accessing the database because the managed code has been installed in. A special type of connection known as a "context connection" is provided to directly access data in that database. A good summary is provided in the article Data Access from CLR Database Objects3 along with a link to a discussion on impersonation and credentials for connections.
SQL Server 2008 introduced the concept of "system CLR objects" separate to "user CLR objects." System CLR objects are enabled regardless of the "clr enabled" system configuration setting. System CLR objects currently include geography, geometry, and hierarchyid.
General Guidance on Transact-SQL versus Managed Code
In general, use Transact-SQL SELECT, INSERT, UPDATE, and DELETE statements whenever possible. Procedural and row-based processing should be used only when the logic is not expressible using the T-SQL language. If the procedure is simply a wrapper for Transact-SQL commands, it should be written in Transact-SQL. It is common for developers to avoid building cursor-based code in Transact-SQL. It is important to avoid excessive row-based processing in managed code for the same reason.
The types of objects that can be created using managed code in SQL Server are described in the article Building Database Objects with Common Language Runtime (CLR) Integration4
Management of SQL CLR Integration
Many database administrators have hesitated in installing extended stored procedures and continue to be cautious in enabling SQL Server CLR. Extended stored procedures execute directly within the memory space of the SQL Server process. Minor errors in extended stored procedures could cause failure or instability of the database engine. Extended stored procedures are now deprecated and should be rewritten using managed code. It is important to understand that managed code object is inherently safer than extended stored procedure code.
Administrators concerned with deployment, ongoing management, and monitoring of SQL Server CLR integration will find the following useful:
Although now somewhat dated, the white paper Using CLR Integration in SQL Server 20058 provides code examples of typical managed code objects for use within SQL Server.
While managed code provides a mechanism for creating user-defined data types, the data types cannot be directly indexed. Some other database engines provide a mechanism for creating user-defined index types. Care must be taken when considering the migration of such applications as no such capability is currently available in SQL Server. While the data types cannot be indexed directly, the properties of user-defined data types can be exposed via persisted calculated columns which can then be indexed.
Case Studies and References
Using SQLCLR has helped solve many customer problems. The following case studies provide examples:
The case study MySpace Uses SQL Server Service Broker to Protect Integrity of 1 Petabyte of Data9 describes how MySpace coordinates the flow of data to its users through an application tier created internally by MySpace developers using Microsoft Visual Studio 2005, the Microsoft .NET Framework 2, and SQLCLR.
The case study McLaren Electronics Fuels Analysis of Formula One Racing Data with SQL Server10 describes how an Advanced Telemetry Linked Analysis System (ATLAS) database offers McLaren Electronics a relational database for Engine Control Unit (ECU) data that provides an easily searchable central repository. The solution also uses the CLR hosted within SQL Server.
The case study Austrian Broadcaster Gains High Availability with SQL Server 2005 Database Mirroring11 describes how the Austrian Broadcasting Corporation Radio & Television took advantage of SQLCLR, the common language runtime feature built into SQL Server 2005, to move some data manipulation from the business tier to the database tier.
Questions and Considerations
This section provides questions and issues to consider when working with your customers.
The more data-oriented the code is, the more likely that it should be written in Transact-SQL. The more calculation, string-oriented, or external-access-oriented the need is, the more likely it is that managed code will be the better solution. Customers have observed that the article What types of objects are useful in SQL CLR?12 is useful when deciding which types of objects should and should not be implemented in managed code.
Investigate the richness of the code needed to achieve a desired function or feature. For rich programming constructs, CLR is the preferred choice. The .NET Framework languages are, in many respects, richer than Transact-SQL, offering constructs and capabilities previously not available to SQL Server developers. Developers may also leverage the power of the .NET Framework Library, which provides an extensive set of classes that can be used to quickly and efficiently solve programming problems.
The ability to define data types and aggregate functions is available through CLR. This capability can be very powerful in assisting with migrating code from other database engines.
User-defined functions (UDFs) allow faster execution. Similar to stored procedures, Transact-SQL UDFs reduce the compilation cost of Transact-SQL code by caching the plans and reusing them for repeated executions. This means that the UDF does not need to be re-parsed and re-optimized with each use, resulting in much faster execution times. Based on customer requirements, you may prefer to deploy CLR-based UDFs.
Another compelling usage of SQLCLR is UDAs, with which you can define new aggregation functions that are not available in the Transact-SQL environment (for example, statistical operations such as mean, and product). With UDAs, you can deliver very good performance and the ability to define multiple inputs (columns) for a single function.
Following are the full URLs for the hyperlinked text.
1 Common Language Runtime (CLR) Integration Programming Conceptshttp://msdn.microsoft.com/en-us/library/ms131102.aspx
2 CLR Integration Code Access Securityhttp://msdn.microsoft.com/en-us/library/ms345101.aspx
3 Data Access from CLR Database Objectshttp://msdn.microsoft.com/en-us/library/ms131109.aspx
4 Building Database Objects with Common Language Runtime (CLR) Integrationhttp://msdn.microsoft.com/en-us/library/ms131046.aspx
5 Deploying CLR Database Objectshttp://msdn.microsoft.com/en-us/library/ms345099.aspx
6 Managing CLR Integration Assemblieshttp://msdn.microsoft.com/en-us/library/ms131107.aspx
7 Monitoring and Troubleshooting Managed Database Objectshttp://msdn.microsoft.com/en-us/library/ms403284.aspx
8 Using CLR Integration in SQL Server 2005http://msdn.microsoft.com/en-US/library/ms345136(v=SQL.90).aspx
9 MySpace Uses SQL Server Service Broker to Protect Integrity of 1 Petabyte of Datahttp://www.microsoft.com/casestudies/Case_Study_Detail.aspx?casestudyid=4000004532
10 McLaren Electronics Fuels Analysis of Formula One Racing Data with SQL Serverhttp://www.microsoft.com/casestudies/Case_Study_Detail.aspx?CaseStudyID=4000001476
11 Austrian Broadcaster Gains High Availability with SQL Server 2005 Database Mirroringhttp://www.microsoft.com/casestudies/Microsoft-SQL-Server-2000-64-bit-Edition/Austrian-Broadcast-Corporation-ORF/Austrian-Broadcaster-Gains-High-Availability-with-SQL-Server-2005-Database-Mirroring/48607
12 What types of objects are useful in SQL CLR?http://sqlblog.com/blogs/greg_low/archive/2011/02/25/what-types-of-objects-are-useful-in-sql-clr.aspx