CLR Integration in SQL Server: When It Still Makes Sense

An assembly inside the database deserves a reason stronger than having worked there for years. CLR integration still has useful cases, but its security, deployment, and execution costs belong in the decision.

Start a CLR Integration Review with the Assembly's Work

CLR integration hosts supported .NET Framework code within SQL Server. It supports database objects such as functions, procedures, and custom aggregates. Hosting code inside the engine also places its behavior within an important shared process.

The relevant question is whether that code performs work the database genuinely needs. Complex text processing on an older SQL Server version is one possible case. A specialized aggregate with carefully defined state and merge behavior is another.

Do not use an assembly merely to wrap an ordinary relational query. Joins, grouping, filtering, and window calculations already have native implementations. Compare a specific computational requirement with the available built-ins before adding deployment and security work.

I inventory existing assemblies before discussing removal or replacement. I also identify their callers before accepting a claim that they are unused. A quiet assembly can still support a monthly process that has not run during the observation window.

Inventory Assemblies and Their Exposed Objects

The following queries run in the application database. Appropriate metadata visibility is required to produce a complete inventory. Start with user-defined assemblies rather than interpreting every built-in assembly as an application dependency.

SELECT assembly_id, name, clr_name, permission_set_desc,
       create_date, modify_date
FROM sys.assemblies
WHERE is_user_defined = 1
ORDER BY name;

SELECT a.name AS AssemblyName,
       SCHEMA_NAME(o.schema_id) AS SchemaName,
       o.name AS ObjectName, o.type_desc,
       am.assembly_class, am.assembly_method
FROM sys.assembly_modules AS am
JOIN sys.assemblies AS a ON a.assembly_id = am.assembly_id
JOIN sys.objects AS o ON o.object_id = am.object_id
WHERE a.is_user_defined = 1
ORDER BY a.name, SchemaName, ObjectName;

Record assembly references, supporting files, deployment ownership, and the source used to produce each binary. The exposed-object query identifies registered module entry points. It is not a complete inventory of CLR types or every indirect caller.

Review SQL dependency metadata and application call sites together. Dynamic SQL and external callers can escape a simple catalog dependency query. Confirm the actual workload before retiring an assembly or changing a function signature.

Keep an inventory of behavior as well as names. Document NULL handling, supported input lengths, error behavior, and output types. Those details determine whether a replacement preserves the data contract.

Keep Strict Security Enabled for CLR Integration

SQL Server 2017 and later enable clr strict security by default. Under this setting, SAFE and EXTERNAL_ACCESS assemblies receive the stricter authorization treatment associated with UNSAFE. Their declared permission labels remain visible in metadata.

A SAFE label therefore does not establish a dependable security boundary. Review the actual binary and its authorization path. Do not disable strict security to make an unexplained assembly load successfully.

SELECT name, value AS ConfiguredValue,
       value_in_use AS ActiveValue
FROM sys.configurations
WHERE name IN (N'clr enabled', N'clr strict security');

SELECT name, is_trustworthy_on
FROM sys.databases
WHERE database_id = DB_ID();

Prefer a signed assembly with a certificate or asymmetric key and the corresponding authorized login. A trusted-assembly entry is another supported authorization mechanism requiring deliberate administration. Neither mechanism removes the need to review the code being authorized.

Do not turn TRUSTWORTHY on as a shortcut for deployment. That changes the database's security relationship with the instance. Keep binary authorization distinct from application users' permission to execute the exposed function.

Understand the Signed Deployment Boundary

The following example assumes an approved, strong-name-signed .NET Framework assembly named TextTools.dll. Its verified file must already exist on the SQL Server computer. The database engine service account needs the required file access.

Run the master-level authorization steps with an appropriately authorized administrator. Replace the demonstration database name with the isolated test database. The signing key and corresponding login belong to the deployment design, rather than an application password. Calling the exposed functions later also needs the instance-level clr enabled option, which is a server change for the instance owner to approve.

USE master;
GO
CREATE ASYMMETRIC KEY TextToolsSigningKey
FROM EXECUTABLE FILE = 'C:\SqlClr\TextTools.dll';
CREATE LOGIN TextToolsSigningLogin
FROM ASYMMETRIC KEY TextToolsSigningKey;
GRANT UNSAFE ASSEMBLY TO TextToolsSigningLogin;
GO
USE ClrTestDatabase;
GO
CREATE ASSEMBLY TextTools
FROM 'C:\SqlClr\TextTools.dll'
WITH PERMISSION_SET = SAFE;
GO

This is a deployment example with explicit prerequisites, not a command to trust an arbitrary downloaded file. Build and sign the reviewed source through the approved process. Preserve the binary hash, signing identity, and rollback package with the deployment record.

Supported SQL Server CLR code targets the hosted .NET Framework environment. A library built for a different modern .NET runtime is not automatically compatible. Review referenced libraries and SQL Server hosting restrictions before deployment.

Assembly updates can affect every registered caller. Test replacement behavior and permissions in an isolated database first. Restore and failover testing must also verify that the required instance-level authorization exists at the destination.

Compare Built-In Features before Keeping Custom Code

Many historical assembly uses now have native alternatives. STRING_SPLIT, STRING_AGG, JSON functions, and window calculations address common parsing and aggregation requirements. Compare their version support and semantics with the existing assembly's behavior.

SQL Server 2025 includes regular-expression functions such as REGEXP_REPLACE and REGEXP_LIKE. These can replace some older custom regex functions. The following SQL Server 2025 example normalizes repeated ordinary spaces without requiring a custom assembly.

-- SQL Server 2025 example.
SELECT REGEXP_REPLACE(N'alpha   beta    gamma', N' +', N' ')
       AS NormalizedText;

A replacement regex engine does not guarantee identical pattern behavior. Compare supported constructs, character classes, flags, and replacement syntax. Test representative inputs and edge cases rather than copying a pattern and assuming equivalence.

Native string aggregation also needs deliberate ordering and NULL rules. A custom aggregate with specialized statistical state can still have a valid purpose. Explain why ordinary aggregates and window functions do not satisfy that exact requirement.

Measure the Execution Shape of CLR Integration

An assembly function called once per row can multiply a small computational cost across a large query. Check how many rows reach the function and which predicates run first. The engine does not receive unlimited resources merely because the code is compiled.

Benchmark complete queries under representative concurrency, not only one isolated function call. Include CPU, elapsed time, memory behavior, and error handling. Record measurements instead of promising that managed code is automatically faster.

Bound input size and execution effort for complex text processing. A pathological pattern or oversized input can turn useful computation into an expensive interruption. Prefer explicit failure over unbounded work that competes with unrelated database requests.

Keep file access, network calls, and external dependencies outside pure computation unless the requirement explicitly justifies them. Those actions create additional security and availability concerns. Database transaction duration should not depend casually on an external service responding.

Make Retention or Replacement a Supported Decision

Does the assembly supply a required capability with reviewed code and a maintained deployment path? If so, retain it with clear ownership and tests. If a built-in preserves the contract, compare a replacement through an approved migration.

Test NULL inputs, malformed values, maximum lengths, Unicode behavior, and concurrent execution. Verify callers' permissions after replacement. A function that returns the right sample value but changes failure behavior still changes the application contract.

CLR integration remains reasonable when its specific capability justifies its operational cost. Document that capability and reassess it when the SQL Server version changes. The database needs maintained behavior, not a museum of clever implementations.

Related reading on this blog: Pulling Values Out of Text With REGEXP_SUBSTR and REGEXP_INSTR and Introduction to CLR: Simple Example of CLR Stored Procedure.

A CLR assembly is not a default extension point, it is a reviewed dependency with a specific database purpose.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.