Database Reference
In-Depth Information
Chapter 13
CLR
There are several different programming paradigms that exist nowadays. Some languages, such as SQL or XQuery, are
declarative
. They define
what
needs to be done without specifying
how
it needs to be achieved. Other languages, such
as C# or Java, are
imperative
. This model requires specifying exact step-by-step control flow of the execution, defining
how
to achieve the results.
As an example, think about a scenario where you need to read all of the data that belongs to a specific customer. In
the declarative model implemented in SQL, you would use
where
clause with the predicate on the
CustomerId
column.
In the imperative model, you would process all records, one by one, comparing
CustomerId
using the
IF
operator.
SQL is a declarative language, and it has been optimized for set-based declarative logic. Even though Transact
SQL has a set of constructs that allows us to develop imperative code, the constructs are not very efficient. Moreover,
the T-SQL language is very limited compared to modern imperative development languages. The Common Language
Runtime (CLR) helps address some of these challenges by providing the execution environment for .Net code within
SQL Server, and it allows us to develop various database objects using .Net programming languages.
Implementation of imperative, procedural-style code in CLR is usually more efficient than in T-SQL. Moreover,
CLR outperforms T-SQL in the computation-intensive areas, such as mathematical calculation, string manipulation,
serialization, byte-level manipulation on large objects, and others.
Covering all aspects of CLR development easily merits a book by itself. This chapter provides an overview of CLR
integration in SQL Server, discusses several security-related questions, and compares the performance of T-SQL and
CLR routines in a few different areas.
CLR Integration Overview
SQL Server loads the .Net runtime environment inside its own process, and it manages memory and other resources
there. It has full control over the environment, and it can shut down .Net application domains if needed.
■
Application domain is the key concept in .Net, and it represents the isolated environment where .Net code is
executed. It provides a similar level of isolation with Windows processes for native Windows code.
Note
CLR code is compiled into assembly DLLs, which are stored within the database. You can register and catalog
assemblies there using the
CREATE ASSEMBLY
statement, specifying either path to the file or binary sequence of
assembly bits that represent assembly code. SQL Server loads assembly into a separate application domain at that
time for the validation and checks that DLL or assembly bits represent compiled .Net code. In addition, it performs
code verification to ensure that assembly does not perform unauthorized actions.
