SQLCLR, UDTs, and UDFs (OLTP)---a Technical Reference Guide for Designing Mission-Critical OLTP Solutions

  • Article

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.

Best Practices

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 Concepts.1 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) Integration.4

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.

Limitations

  • 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:

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.

Appendix

Following are the full URLs for the hyperlinked text.

1 Common Language Runtime (CLR) Integration Programming Conceptshttps://msdn.microsoft.com/en-us/library/ms131102.aspx

2 CLR Integration Code Access Securityhttps://msdn.microsoft.com/en-us/library/ms345101.aspx

3 Data Access from CLR Database Objectshttps://msdn.microsoft.com/en-us/library/ms131109.aspx

4 Building Database Objects with Common Language Runtime (CLR) Integrationhttps://msdn.microsoft.com/en-us/library/ms131046.aspx

5 Deploying CLR Database Objectshttps://msdn.microsoft.com/en-us/library/ms345099.aspx

6 Managing CLR Integration Assemblieshttps://msdn.microsoft.com/en-us/library/ms131107.aspx

7 Monitoring and Troubleshooting Managed Database Objectshttps://msdn.microsoft.com/en-us/library/ms403284.aspx

8 Using CLR Integration in SQL Server 2005https://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 Datahttps://www.microsoft.com/casestudies/Case_Study_Detail.aspx?casestudyid=4000004532

10 McLaren Electronics Fuels Analysis of Formula One Racing Data with SQL Serverhttps://www.microsoft.com/casestudies/Case_Study_Detail.aspx?CaseStudyID=4000001476

11 Austrian Broadcaster Gains High Availability with SQL Server 2005 Database Mirroringhttps://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