SQL: What types of objects are useful in SQL CLR?
I’ve recently been talking to clients about SQL CLR objects. When these were first introduced in SQL Server 2005, many of us had high hopes for them. SQL Server has never been great in regard to extensibility and this provided some way to extend the product.
Nowadays, I avoid SQL CLR. And that’s a real pity. But it’s no longer supported in Azure SQL Database, apart from the system CLR objects of geometry, geography, and hierarchyid. (Note: I’m also not a fan of hierarchyid). I need to use extensibility methods that are available in the different environments that I work in, and Azure SQL Database is one of those. The same applies to Fabric SQL Database.
But if you’re going to use it?
If you are going to use SQL CLR, for deciding what does and doesn’t make sense, the rules that I normally apply are as follows:
| Database Object | Transact-SQL | Managed Code |
|---|---|---|
| Scalar UDF | Generally poor performance | Good option when limited or no data-access |
| Table-valued UDF | Good option if data-related | Good option when limited or no data-access |
| Stored Procedure | Good option | Good option when external access is required or limited data access |
| DML Trigger | Good option | Rarely a good option as most perform substantial data access |
| DDL Trigger | OK option if only limited processing of XML EVENTDATA | Good option for extensive processing of XML EVENTDATA |
| Aggregate | Not possible | Good option |
| User-defined Data Type | Only alias types | Good option |
Scalar UDFs written in Transact-SQL are well-known for causing performance problems in SQL Server environments. Recent versions of SQL Server have attempted to correct this but it’s still a big issue. By comparison, managed code is often a good option (and generally a much faster option) for implementing scalar UDFs as long as the functions do not depend on heavy data access.
Table-valued Functions that are data-oriented are likely to be best implemented in Transact-SQL. A common use case for managed code in table-valued UDFs is for functions that need to access external resources such as the file system, environment variables, registry, etc.
Stored procedures have traditionally been written in T-SQL. Most stored procedures should continue to be written in T-SQL. There are very few good use cases for managed code in stored procedures. The exceptions to this are stored procedures that need to access external resources or perform complex calculations. There should be consideration, however, about whether code that performs these tasks should be implemented within SQL Server at all.
Almost all DML triggers are heavily-oriented towards data access and are best written in T-SQL. There are very few valid use cases for implementing DML triggers in managed code.
DDL triggers are also often data-oriented. Some DDL triggers though, need to do extensive XML processing, particularly based on the XML EVENTDATA structure passed to these triggers by SQL Server. The more that extensive XML processing is required, the more likely the DDL trigger would be best implemented in managed code. Managed code would also be a better option if the DDL trigger needed to access external resources but this is rarely a good idea within any form of trigger.
Transact-SQL offers no concept of user-defined aggregates. These need to be implemented in managed code. A common use case for user-defined aggregates is to create functionality that exists in other database engines while migrating to SQL Server. For example, if a database engine provides a RANGE function, a replacement could be written in managed code to avoid the need to rewrite all the application code. I remember seeing the SQL Server Migration Assistant using managed code to ease Oracle migration. At first (SQL Server 2005), user-defined aggregates were limited to those that could be serialized in 8KB of data. SQL Server 2008 changed this limit to 2GB and also introduced the ability to create multi-column aggregates. There is no way to do this in Transact-SQL instead.
Transact-SQL offers the ability to create alias data types but these are not really new data types. They are more like subsets (or subclasses) of existing built-in data types. Managed code offers the ability to create entirely new data types and determine not only what data needs to be stored but also the behavior of the data type. It is important to understand however that the intention of user-defined CLR data types is not to convert SQL Server into an object-oriented database. Data types should relate to storage of basic data, not to objects such as employees or customers.
2026-05-04