SQL Server 2008 : Using the CLR - CLR Integration

10/11/2010 2:16:28 PM
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.

Configuring & Implementing...: Enabling CLR Integration

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;
sp_configure 'clr enabled', 1;

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.

Top 10 Video Game
-   Minecraft Mods - MAD PACK #10 'NETHER DOOM!' with Vikkstar & Pete (Minecraft Mod - Mad Pack 2)
-   Minecraft Mods - MAD PACK #9 'KING SLIME!' with Vikkstar & Pete (Minecraft Mod - Mad Pack 2)
-   Minecraft Mods - MAD PACK #2 'LAVA LOBBERS!' with Vikkstar & Pete (Minecraft Mod - Mad Pack 2)
-   Minecraft Mods - MAD PACK #3 'OBSIDIAN LONGSWORD!' with Vikkstar & Pete (Minecraft Mod - Mad Pack 2)
-   Total War: Warhammer [PC] Demigryph Trailer
-   Minecraft | MINIONS MOVIE MOD! (Despicable Me, Minions Movie)
-   Minecraft | Crazy Craft 3.0 - Ep 3! "TITANS ATTACK"
-   Minecraft | Crazy Craft 3.0 - Ep 2! "THIEVING FROM THE CRAZIES"
-   Minecraft | MORPH HIDE AND SEEK - Minions Despicable Me Mod
-   Minecraft | Dream Craft - Star Wars Modded Survival Ep 92 "IS JOE DEAD?!"
-   Minecraft | Dream Craft - Star Wars Modded Survival Ep 93 "JEDI STRIKE BACK"
-   Minecraft | Dream Craft - Star Wars Modded Survival Ep 94 "TATOOINE PLANET DESTRUCTION"
-   Minecraft | Dream Craft - Star Wars Modded Survival Ep 95 "TATOOINE CAPTIVES"
-   Hitman [PS4/XOne/PC] Alpha Gameplay Trailer
-   Satellite Reign [PC] Release Date Trailer