SQLCLR vs SQL Server 2017, Part 8: Is SQLCLR Deprecated in Favor of Python or R (sp_execute_external_script)?

(last updated: 2020-01-09 @ 13:15 EST / 2020-01-09 @ 18:15 UTC )

With the additional (and annoying) configuration step required to get SQLCLR Assemblies to load starting in SQL Server 2017, some people have been wondering what is going on with SQLCLR. Considering that this new restriction is the only real change to SQLCLR since SQL Server 2012 (three versions ago), then with (limited) support for languages such as R (starting in SQL Server 2016) and Python (starting in SQL Server 2017) being added, it might even look like SQLCLR is being deprecated (i.e. phased-out) in favor of these new languages.

Could this be true? There is no official indication, but could it be unofficially / “effectively” deprecated? Well, let’s take a look.

Investigation

How do we know / determine that a feature or product is deprecated?

Officially Deprecated

According to the Deprecated Database Engine Features in SQL Server 2017 documentation:

For example, in that same documentation, in the Features deprecated in a future version of SQL Server section, you can see that “Extended stored procedure programming” is officially deprecated. And, interestingly enough, the Replacement is:

And, in the Database Engine Extended Stored Procedures – Reference documentation, it has a notice at the top stating:

So, “officially deprecated” means that it is marked as such in the documentation, and where applicable, will show up in a performance counter, such as SQLServer:Deprecated Features.

Effectively Deprecated

There are also features that are not marked as deprecated in the documentation (in the “Deprecated Features” list) and do not show up in the SQLServer:Deprecated Features performance counter, yet still do meet one, or both, of the criteria noted above, namely:

1. The feature is in maintenance mode only.
2. The official recommendation is to not use the feature in new development.

Take, for example, the OLE Automation Stored Procedures (i.e. the sp_OA* procs, such as sp_OACreate, sp_OAMethod, etc). These are not in the “Deprecated Features” list, nor does the documentation for them recommend not using them (though, hopefully, you will never find someone who recommends using them). However, they are not being updated (they do not support any data types added in SQL Server 2005 or newer: MAX types, XML, etc), and they increment the SQLServer:Deprecated Features performance counter (for instance_name = 'XP_API' , at least as of SQL Server 2017). Instead of using OLE Automation stored procedures, you should either use SQLCLR, or handle the functionality outside of SQL Server.

Another example of an “effectively” deprecated feature are the SQL Server Collations (i.e. Collations having names starting with SQL_ ). These are hold-overs to provide compatibility with pre-SQL Server 2000 (which introduced the Windows Collations: names not starting with SQL_ ). These are also not in the “Deprecated Features” list, nor do they increment the SQLServer:Deprecated Features performance counter. However, they are not being updated, and the documentation for SQL Server Collation Name recommends against using them:

SQL Server collations are still supported for backward compatibility, but should not be used for new development work.

And yes, if at all possible, do not use Collations with names starting with "SQL_" ¹.

Not Deprecated

One feature that does not fit the description of “deprecated” is SQLCLR:

1. SQLCLR is still being used internally for some newer built-in functions, such as the following:
   • starting in SQL Server 2012
     • FORMAT
     • PARSE
     • TRY_PARSE (but not TRY_CONVERT)
   • starting in SQL Server 2016
     • COMPRESS
     • DECOMPRESS
     • AT TIME ZONE
     • sys.time_zone_info (DMV)
        
2. The sp_execute_external_script stored procedure, used to execute R and Python scripts, cannot be a replacement for SQLCLR because it cannot do the following:
   1. Use the data types:
      • XML
      • DATETIME2
      • DATETIMEOFFSET
      • TIME
      • SQL_VARIANT
      • HierarchyID
      • Geometry
      • Geography
      • custom SQLCLR UDTs
   2. Create User-Defined Aggregates (UDA)
   3. Create User-Defined Types (UDT)
   4. Create Scalar Functions / UDF
   5. Create Table-Valued Functions (TVF)
   6. Create Triggers (as a stored procedure, sp_execute_external_script can be executed within a Trigger, but it most likely doesn’t have access to the inserted and deleted tables, while a SQLCLR Trigger does)
   7. Return results with named columns (results set columns from sp_execute_external_script are all unnamed, so if you need the result set columns to have names, you need to use the WITH RESULT SETS clause, which cannot be dynamic unless you put the entire call into Dynamic SQL, limiting your ability to return named columns if the columns and their datatypes are not known ahead of time)
   8. Access the current SPID and transaction via the in-process connection (i.e. access to local temporary objects, CONTEXT_INFO, SESSION_CONTEXT, etc ; context connection = true; )
   9. Impersonate the caller’s Windows Account (when accessing external resources ; at least it is highly unlikely that this would be possible)
   10. Restrict access to certain methods / code (SQLCLR methods are exposed through T-SQL objects which have their own permissions, whereas sp_execute_external_script is just like xp_cmdshell and the OLE Automation stored procedures in that you cannot restrict what code people pass into them).
        
3. Finally, as we saw in the “Officially Deprecated” section above, SQLCLR is the recommended replacement for the Extended Stored Procedure API. It is also the unofficially recommended replacement for the OLE Automation stored procedures (i.e. sp_OA* ).

Conclusion

Microsoft is a company like most others. There is always more to do than there are resources available to accomplish everything. There are budgets, time constraints, priorities, and so on. SQLCLR is a feature like most others. Some people like it, some people don’t, and some have never even heard of it. It is great for some scenarios, not so great for others. It has been used to solve complex problems rather efficiently, and in other cases it has been horribly misused to create slow, convoluted technical debt that is used by anti-SQLCLR curmudgeons as validation of their opinion.

Sure, there are features that only survive for a version or two (e.g. vardecimal, introduced in SQL Server 2005, and then deprecated in the following version, 2008). But, there are plenty of features (even entire products, I would assume) that are valid and useful yet have not been improved nearly as much as some would like. While this is certainly frustrating, it does not indicate / imply the death of the feature (or product).

Also, just because a company comes out with a new product / feature that can do some of the same things as an existing product / feature does not necessarily mean that something is being replaced. This is especially true if the new product / feature does not do all (or most) of the same things. I remember back in 2011 or 2012 Microsoft either came out with something to do JavaScript on the server, or there was at least talk of such a thing (perhaps TypeScript?). And sure enough, there were folks who were predicting the end of C# / .NET, completely ignoring reality and the implications of replacing it.

So, while there are things that can certainly be improved with SQLCLR, and while it is frustrating that no resources are being devoted to it, there is no evidence to suggest that SQLCLR is being deprecated, even unofficially.