Executive Summary
SQL CLR is a feature which allows database developers to create powerful code that can be executed from within SQL statements, utilizing familiar programming languages.
More Detail
SQL CLR is a feature, added in SQL Server 2005, that allows a database developer to host .NET managed code inside of a SQL Server database instance. The hosted code can then be used within SQL DML statements to execute procedures/methods that were written in a .NET language. To understand what is going on here, a brief understanding of .NET, CLR, CIL, and CLI is useful.
Starting in the early 2000s, Microsoft released a set of languages under a common framework, called .NET (Visual Basic .NET, C# .NET, and J# .NET). The intent was to be able to bring a common set of features together from the different leading high-level programming languages at the time (Visual Basic, C/C++, and Java). Microsoft published their own versions of each of these languages (Microsoft already had versions of all of these languages in the market), some being changed more greatly than others.
They were all published under one framework so that features available to one language would be available to all, although with different implementations. Each of these languages would then compile down to a common code type, known as Common Intermediate Language (CIL). This is not to be confused with the Common Language Infrastructure (CLI) which is a standard, developed by Microsoft, in conjunction with the .NET endeavor. The CLI is the standard, .NET/CIL/CLR is just the way that Microsoft implemented this standard.
This Intermediate Language would then be converted by the Common Language Runtime (CLR) into machine code, which is specific to a particular processor family. Having this relationship between the CIL and the CLR is what makes .NET languages Interoperable and Portable. The languages can be said to be Interoperable in that any language that compiles down to CIL can be linked with other similarly compiled code. When I say Portable, I mean that, theoretically, a CLR-type interpreter (virtual machine) could be created for basically any platform you wish, and that new interpreter would be able to convert that CIL into machine code that would run on that platform.
This is where SQL CLR comes back into the picture. The SQL Common Language Runtime hosts a distinct engine within SQL Server that runs the CIL that the database developer has created. This enables programmers to work in the managed languages, with which they are already familiar. They can take advantage of many of the features that are only available to managed and compiled code. It is important to remember, however, that there is overhead in running through the CLR and poorly written or implemented methods can actually cause huge performance problems. Like anything in database development, you have to learn how to use the tools effectively. There are no magic bullets.
This post is part of a series of blog posts entitled SQL CLR: An Introduction, the other posts are as follows:
- The Origination and Uses of SQL CLR
- Infrastructure Requirements and Configuration
- Writing a Simple CLR Stored Procedure
- Security and Signing
- Deployment and Usage
- Table-Valued Functions and Other SQL CLR (Coming Soon)
- Incorporating Existing Libraries (Coming Soon)
4 Comments.