As SQL Server became more widely used, database
professionals and developers demanded the ability to incorporate
advanced logic and functionality into their database objects.
Transact-SQL remains the best language to perform set-based tasks as it
was designed specifically for querying and manipulating large data sets
efficiently. You should always choose Transact-SQL when querying data.
As Transact-SQL is an immensely powerful query language, it is less
well suited to complex operations. Consider how difficult it would be
to implement the following tasks using Transact-SQL:
Validate whether a phone number is valid for a specific city and country
Store complex data types like GPS coordinates in a single column
Read data from the Windows registry
Split someone’s full name into first name, last name, and middle initials
Read a file from a network, and share and import its data and attributes
Write to a file in the file system
CLR
integration in SQL Server allows you to implement these tasks using
only a few lines of code. This extended functionality can then be
accessed in the same way as standard SQL Server database objects and as
part of Transact-SQL queries.
Implementing
advanced functionality using the CLR has many advantages. First of all,
.NET languages are fully featured programming languages and offer
elements like arrays, looping through collections, and object-oriented
programming, just to name a few. Transact-SQL, being a query-oriented
language, does not support those elements. Additionally, the Base Class
Library is available through CLR integration. Using the Base Class
Library you can access rich functionality for text manipulation, file
and registry access, cryptography, and much more. Using managed code
you can also define and manipulate complex data types like localized
dates and times, currencies, and spatial coordinates. Finally, because
managed code is compiled to native code, it may sometimes offer
performance advantages over Transact-SQL, which is interpreted at
runtime.
|
In
SQL Server 2008, CLR integration is disabled by default, and you must
explicitly enable this feature before you can use it. Only those
accounts that have ALTER SETTINGS permissions to the SQL Server server
can enable and disable CLR integration. By default, only members of
sysadmin and serveradmin server roles have this permission. To enable
CLR integration in SQL Server, use the sp_configure stored procedure with the clr enabled parameter set to the value of 1, as shown in the following example:
sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'clr enabled', 1;
GO
RECONFIGURE;
GO
The following output will be produced when the sp_configure succeeds:
Configuration option 'show advanced options' changed from 0 to 1. Run
the RECONFIGURE statement to install.
Configuration option 'clr enabled' changed from 0 to 1. Run the
RECONFIGURE statement to install.
To disable the CLR-integration feature, execute sp_configure with the clr enabled parameter set to 0.
When you disable CLR integration, all CLR object code executing within
SQL Server is immediately interrupted. This may lead to data
consistency issues, cause unexpected errors, and negatively impact
users of the system. Ensure that no current activity is interrupted
when you disable the functionality.
|
Note
that the .NET Framework is available in many versions. Managed code is
compiled for a specific version of the .NET Framework and cannot be
executed by a different version. For example, code compiled for .NET
Framework 1.1 will not execute
in .NET Framework 1.0 and vice versa. SQL Server 2008 supports .NET
Framework 2.0 and later. You can use Visual Studio 2005 or Visual
Studio 2008 to write code targeting .NET Framework 2.0, but you cannot
use Visual Studio 2003 or earlier.
