Chapter 11: Understanding the SQL Server Common Language Runtime

Overview

This chapter describes the .NET Framework and its Common Language Runtime (CLR) and how SQL Server 2005 now integrates this environment into its processing engine The incorporation of a version of the CLR directly into SQL Server (in-process) has catapulted the product into the future, years ahead of itself. To fully understand how it works, this chapter looks at the various aspects of this runtime environment, because it is important to us going in from the ground up to create functions, stored procedures, triggers, data structures (such as arrays) and .NET SQL Server integrated applications, components, and other services.

With this concise coverage of the.NET Framework’s runtime, you will be able to design and code applications with the runtime in mind, especially in the area of memory management, which represents the biggest change in the way we write any SQL Server-based applications. Knowing about the runtime is also especially important for programming with the correct security model, exception handling, referencing the correct assemblies to target namespaces, debugging assemblies, and otherwise managing assemblies (deployment and maintenance). All of these subjects make programming the SQL Server CLR far more complex than standard T-SQL.

This chapter examines the following key components of the Framework:

• The Common Type System (CTS)   The system that provides the type architecture of the Framework and type safety.

• The Common Language Specification (CLS)   The specification all NET language adopters and compiler makers adhere to so that their languages can be seamlessly integrated into the .NET Framework.

• The Common Language Runtime (CLR)   The runtime and managed execution environment in which all .NET managed applications are allowed to process.

We will then break down the Common Language Runtime into several components to be discussed as follows:

• Managed execution   This section discusses what managed execution means, as well as how it differs from other execution environments such as VBRUN, SmallTalk’s runtime, and the Java Virtual Machine (JVM). It also introduces the garbage collector.

• The runtime environment   This section discusses how the CLR works with metadata and Microsoft Intermediate Language (MSIL) to execute code. It also investigates the just-in-time (JIT) compilation architecture. We also briefly look at application domains and what they mean for your deployment requirements. And we also touch on the subject of attributes-a facility for allowing programmers to have more control over the execution and management of their code in the runtime environment.

• Assemblies   This section goes into assemblies in some depth and examines how .NET applications, class libraries, and components are packaged.

• CLR and security   This section introduces the security architecture of the CLR and how it affects your code and ability to deploy.

Even if you have not had any experience writing and compiling a.NET application outside the SQL Server engine, this chapter will give you the necessary information to hit the ground running-writing, compiling, and executing any stored procedure, function, trigger, or user-defined data type (UDT).