SQL CLR Integration Reference

This documentation provides a comprehensive reference for developing and deploying Common Language Runtime (CLR) integration features in SQL Server.

SQL CLR (Common Language Runtime) integration allows you to write stored procedures, triggers, user-defined functions (UDFs), user-defined types (UDTs), and user-defined aggregates in any .NET Framework language, such as C# or Visual Basic .NET. This enables you to leverage the power of the .NET Framework within your SQL Server database, extending its capabilities beyond Transact-SQL.

Introduction to SQL CLR

SQL CLR integration was first introduced in SQL Server 2005, providing a powerful mechanism for extending database functionality. By writing database objects in managed code, developers can harness the rich features of the .NET Framework, including advanced data manipulation, complex algorithms, custom validation logic, and interaction with external resources.

Benefits of SQL CLR

Increased Performance: For certain computationally intensive tasks, managed code can offer performance advantages over Transact-SQL.
Code Reusability: Leverage existing .NET libraries and your organization's .NET code base within the database.
Rich Functionality: Access the full power of the .NET Framework, including regular expressions, file I/O, networking, and more.
Improved Productivity: Developers familiar with .NET languages can be more productive when developing complex database logic.
Strong Typing and Object-Oriented Features: Benefit from the inherent advantages of object-oriented programming languages.

Getting Started with SQL CLR

To start developing SQL CLR objects, you will need:

SQL Server (2005 or later)
Visual Studio (with SQL Server Data Tools installed) or a compatible .NET development environment.
Understanding of the .NET Framework and your chosen language (C#, VB.NET).

The process generally involves writing your code, compiling it into a .NET assembly, and then deploying that assembly to SQL Server.

Core Concepts

Assemblies

A .NET assembly is a compiled code library that contains your SQL CLR objects. You register these assemblies with SQL Server using the CREATE ASSEMBLY statement. Assemblies are the fundamental unit of deployment for SQL CLR code.

CREATE ASSEMBLY MyCLRAssembly
FROM 'C:\Path\To\MyCLRAssembly.dll'
WITH PERMISSION_SET = SAFE;

Permissions

SQL CLR enforces security through permission sets. These determine what resources your managed code can access. Common permission sets include:

SAFE: The most restrictive. Allows access to database objects and limited .NET Framework functionality.
EXTERNAL_ACCESS: Allows access to network resources and the file system, but with additional restrictions.
UNSAFE: Allows full access to system resources, including unmanaged code. Use with extreme caution.

You specify the permission set when creating the assembly.

Deployment

Deploying a SQL CLR object involves several steps:

Write and compile your .NET code into an assembly (.dll file).
Register the assembly with SQL Server using CREATE ASSEMBLY.
Create the corresponding SQL CLR object (stored procedure, function, etc.) that references the registered assembly using CREATE PROCEDURE, CREATE FUNCTION, etc.

Developing SQL CLR Objects

Stored Procedures

Write stored procedures in .NET to perform complex logic, interact with external systems, or execute operations that are more efficiently handled in managed code.

-- Example C# stored procedure
using System.Data.SqlClient;

public class StoredProcedures
{
    [Microsoft.SqlServer.Server.SqlProcedure]
    public static void GetCustomerName(string customerId, out string customerName)
    {
        using (SqlConnection connection = new SqlConnection("context connection=true"))
        {
            connection.Open();
            SqlCommand command = new SqlCommand("SELECT Name FROM Customers WHERE CustomerID = @ID", connection);
            command.Parameters.AddWithValue("@ID", customerId);
            customerName = (string)command.ExecuteScalar();
        }
    }
}

Triggers

Create triggers in .NET to respond to data modification events (INSERT, UPDATE, DELETE) with sophisticated logic, logging, or complex validation.

User-Defined Functions

Develop scalar or table-valued functions in .NET to encapsulate complex calculations, data transformations, or custom logic that can be reused in Transact-SQL queries.

User-Defined Types (UDTs)

Define custom data types in .NET to represent complex data structures within SQL Server, such as geometric shapes, complex numbers, or custom date/time formats.

User-Defined Aggregates

Create custom aggregate functions (e.g., custom SUM, AVG, or new types of aggregations) that can operate on groups of rows.

Best Practices

Choose the right permission set: Always use the least permissive set necessary. Prefer SAFE whenever possible.
Minimize Context Switching: Frequent calls between SQL and CLR can impact performance.
Handle Exceptions: Implement robust error handling in your .NET code.
Dispose of Resources: Ensure that all managed resources (like connections and commands) are properly disposed of.
Test Thoroughly: Test your CLR objects extensively under various conditions.
Keep Assemblies Small: Deploy only the necessary code.

Troubleshooting

Common issues include permission errors, assembly loading failures, and runtime exceptions. Utilize SQL Server's error logs and debugging tools within Visual Studio to diagnose problems. Ensure your .NET code is compatible with the SQL Server version and CLR integration features.

Code Samples and Further Reading

Explore the official Microsoft documentation and community resources for detailed code samples, advanced techniques, and specific scenarios.

SQL CLR Reference