(last updated: 2019-01-10 @ 18:00 EST / 2019-01-10 @ 23:00 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.
How do we know / determine that a feature or product is deprecated?
According to the Deprecated Database Engine Features in SQL Server 2017 documentation:
- The feature is in maintenance mode only. No new changes will be done, including those related to inter-operability with new features.
- We strive not to remove a deprecated feature from future releases to make upgrades easier. However, under rare situations, we may choose to permanently remove the feature from SQL Server if it limits future innovations.
- For new development work, we do not recommend using deprecated features.
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:
This feature will be removed in a future version of Microsoft SQL Server. Do not use this feature in new development work, and modify applications that currently use this feature as soon as possible. Use CLR integration instead.
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
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:
- The feature is in maintenance mode only.
- 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_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:
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
One feature that does not fit the description of “deprecated” is SQLCLR:
- SQLCLR is still being used internally for some newer built-in functions, such as the following (both starting in SQL Server 2016):
- 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:
- Use the data types:
- custom SQLCLR UDTs
- Create User-Defined Aggregates (UDA)
- Create User-Defined Types (UDT)
- Create Scalar Functions / UDF
- Create Table-Valued Functions (TVF)
- Create Triggers (as a stored procedure,
sp_execute_external_scriptcan be executed within a Trigger, but it most likely doesn’t have access to the
deletedtables, while a SQLCLR Trigger does)
- Return results with named columns (results set columns from
sp_execute_external_scriptare all unnamed, so if you need the result set columns to have names, you need to use the
WITH RESULT SETSclause, 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)
- Access the current SPID and transaction via the in-process connection (i.e. access to local temporary objects,
SESSION_CONTEXT, etc ;
context connection = true;)
- Impersonate the caller’s Windows Account (when accessing external resources ; at least it is highly unlikely that this would be possible)
- Restrict access to certain methods / code (SQLCLR methods are exposed through T-SQL objects which have their own permissions, whereas
sp_execute_external_scriptis just like
xp_cmdshelland the OLE Automation stored procedures in that you cannot restrict what code people pass into them).
- Use the data types:
- 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.
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).
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.
- Instead of using
Latin1_General_CI_AS(if you are on SQL Server 2005)
Latin1_General_100_CI_AS(if you are on SQL Server 2008 or 2008 R2)
Latin1_General_100_CI_AS_SC(if you are on SQL Server 2012 or newer) ↩