(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.
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:
- starting in SQL Server 2012
- 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) ↩
8 thoughts on “SQLCLR vs SQL Server 2017, Part 8: Is SQLCLR Deprecated in Favor of Python or R (sp_execute_external_script)?”
[…] Solomon Rutzky makes me invoke Betteridge’s Law of Headlines: […]
Another argument that SQLCLR is not deprecated – both Azure Sql Managed Instance and Amazon RDS support SQLCLR and don’t allow Python/R (at least at the moment).
Thanks. I had, at one point, thought to mention that the ability to do SQLCLR was noted in the features list of Azure SQL Database Managed Instances, but then forgot about it. So thanks for bringing that up :-). Not sure I would consider support by Amazon RDS as an indicator for, or against, deprecation since they are just implementing what’s available in that particular version, but certainly good to know that it’s available there (
SAFEassemblies only) while the new stuff isn’t. Probably for the same reason that not even
EXTERNAL_ACCESSassemblies are allowed: they reach outside of SQL Server. And I’m sure there are other logistical / technical issues given that “external scripts” execute from a separate service ( Launchpad ).
Impersonate the caller’s Windows Account (when accessing external resources ; at least it is highly unlikely that this would be possible) – its wrong. Im using ODBC connection with trust (in R, Python)
Hello Ilya. Can you please add more detail? Are you saying that you can execute code in R or Python using execute_external_script, and have code that accesses the file system use your security that you logged into SQL Server with?
Unfortunately, even if SQLCLR support isn’t removed from SQL Server, it’s stuck on the .Net Framework and the .Net world is moving on to .Net Core – rebranding to just “.Net” for 5.0 to emphasize this is the “future”. Significant new features mean that it’ll get harder and harder to find libraries that still support Framework over time.
And .Net core is actively removing leftover code that supported SQLCLR.
Hi James. Thanks for providing that info. That’s quite unfortunate, and would be even more unfortunate if Microsoft doesn’t find a way to update this, or even worse, abandons SQLCLR support (though that seems unlikely given what the dependencies that they have built on top of it) given that most (maybe all?) other major RDBMS options have support for extending functionality via C and other languages.
Loopback Connections that allow you to query the database within the user context of sp_execute_external_script are described here:
@language = N’Python’,
@script = N’
from revoscalepy import RxSqlServerData, rx_data_step
data_set = RxSqlServerData(
sql_query = “SELECT 1”,
connection_string = “Driver=SQL Server;Server=SERVER;Database=DB;Trusted_Connection=Yes;”
OutputDataSet = rx_data_step(data_set)