SQLCLR vs SQL Server 2017, Part 8: Is SQLCLR Deprecated to opt for Python or R (sp_execute_external_script)?

Because of the additional (and annoying) configuration step was required to get SQLCLR Assemblies to load starting in SQL Server 2017, some people were wondering what’s going on with SQLCLR. You are searching for this new restriction stands out as the only real alteration to SQLCLR since SQL Server 2012 (three versions ago), then with (limited) support for languages most notably R (beginning in SQL Server 2016) and Python (starting in SQL Server 2017) being added, it may possibly even mimic SQLCLR is being deprecated (i.e. phased-out) for the sake of these new languages.

Could this be true? There isn’t an official indication, but they can it be unofficially / ” effectively” deprecated? Well, let’s look over.
Investigation

How do you know / determine your feature or technique is deprecated?
Officially Deprecated

Acccording in to the Deprecated Database Engine Features in SQL Server 2017 documentation:
When a feature is marked deprecated, this in essence means:

The feature is inside maintenance mode only. No new changes will be performed, including those affiliated with inter-operability with latest features.
We strive by no means to remove a deprecated feature from future releases to make upgrades easier. However, under rare situations, we might choose to permanently remove feature from SQL Server no matter whether limits future innovations.
For new development work, we don’t recommend using deprecated features.

Just like, in that same documentation, within Features deprecated in some future version of SQL Server section, you can see that ” Extended stored procedure programming” is officially deprecated. And, oddly enough, the Replacement is:
Use CLR Integration instead.

And, inside Database Engine Extended Stored Procedures – Reference documentation, it provides notice start stating:
Important
This feature will be removed within a future type of Microsoft SQL Server. Do not use this selection in new development work, and modify applications that currently of one’s feature as quickly as possible. Use CLR integration instead.

So, ” officially deprecated” retail environment significantly it is marked the result in the documentation, and where applicable, can have up in a performance counter, that include SQLServer:Deprecated Features.
Effectively Deprecated

Remember, there are features which are not marked as deprecated around the documentation (within the ” Deprecated Features” list) and won’t show up in the SQLServer:Deprecated Features performance counter, whilst still being do meet one, or both, of an criteria noted above, namely:

The feature set in maintenance mode only.
The official recommendation is always not operate using the feature in new development.

Take, just like, the OLE Automation Stored Procedures (i.e. the sp_OA* procs, which include sp_OACreate, sp_OAMethod, etc). These aren’t in the ” Deprecated Features” list, nor does the documentation in order recommend not with their company (though, hopefully, begin watching find someone who recommends with them). However, functional indeed being updated (they won’t support data types included in SQL Server 2005 or newer: MAX types, XML, etc), they increment the SQLServer:Deprecated Features performance counter (for instance_name = ‘XP_API’ , no less as of SQL Server 2017). In lieu of using OLE Automation stored procedures, i suggest you either use SQLCLR, or handle the functionality over and above SQL Server.

Another style of an ” effectively” deprecated feature are considered the SQL Server Collations (i.e. Collations having names begining with SQL_ ). These are hold-overs to make available compatibility with pre-SQL Server 2000 (which introduced the Windows Collations: names not starting with SQL_ ). These are also not inside the ” Deprecated Features” list, nor would they increment the SQLServer:Deprecated Features performance counter. However, yet they can be being updated, as well as documentation for SQL Server Collation Name recommends against with them:

SQL Server collations are still supported for backward compatibility, but won’t be used for new development work.

Buyers ., if at all possible, avoid using Collations with names you start with SQL_ 1.
Not Deprecated

Beside each keyword . that does not fit the desciption of ” deprecated” is SQLCLR:

SQLCLR are still being used internally to many newer built-in functions, which follow (both starting in SQL Server 2016):
COMPRESS (but aren’t UNCOMPRESS)
AT TIME ZONE

The sp_execute_external_script stored procedure, previously used to execute R and Python scripts, shouldn’t be a replacement for SQLCLR since it cannot do the examples below:
Use the data types:
XML
DATETIME2
DATETIMEOFFSET
TIME
SQL_VARIANT
HierarchyID
Geometry
Geography
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 being a stored procedure, sp_execute_external_script can be executed within a Trigger, but it surely most likely doesn’t have access around the inserted and deleted tables, while a SQLCLR Trigger does)
Return results with named columns (results set columns from sp_execute_external_script each are unnamed, making more food . need the result set columns to experience names, you might use the WITH RESULT SETS clause, which cannot dynamic in case you put the entire call into Dynamic SQL, limiting your ability to return named columns once the columns also datatypes are not known early on)
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; )
Impersonate the caller’s Windows Account (when accessing external resources ; around it is highly unlikely that it would be possible)
Restrict access to particular methods / code (SQLCLR methods are subjected through T-SQL objects which all have their own permissions, whereas sp_execute_external_script is usually xp_cmdshell and the OLE Automation stored levels in that you cannot restrict what code people pass into them).

Finally, if we saw inside ” Officially Deprecated” section above, SQLCLR is considered the recommended option the Extended Stored Procedure API. Additionally, it’s the unofficially recommended replacement for the OLE Automation stored procedures (i.e. sp_OA* ).

Conclusion

Microsoft may be a company like others. Almost always there is more you can do than you have resources there for accomplish everything. You’ll discover budgets, time contraints, priorities, for example. SQLCLR is a feature a good number of others. Some like it, a number of people don’t, and some haven’t ever even noticed it. It is always great for some scenarios, not too great for others. Because of used to solve complex problems rather efficiently, and also in other cases experts agree it is horribly misused to put together slow, convoluted technical debt that is used by anti-SQLCLR curmudgeons as validation inside their opinion.

Sure, you’ll find features that just survive for your version or two (e.g. vardecimal, introduced in SQL Server 2005, and then also deprecated in the following version, 2008). But, there are many of features (even entire products, Phoning assume) that can be valid and useful yet were not improved nearly as much as some need. While this is certainly frustrating, it won’t indicate / imply the death on the feature (or product).

Also, wish company is developed with a new product / feature that might do some of the exact same things being a existing product / feature does not always mean that something is to be replaced. This is also true if the awesome product / feature wouldn’t do all (or most) in the things. I remember back in 2011 or 2012 Microsoft either brought out something in order to do JavaScript on the server, or there was clearly at least talk of this a thing (perhaps TypeScript?). And affirmed, there were individuals were predicting the end of C# / .NET, completely ignoring reality along with implications of replacing it.

So, while you’ll discover things that can typically be improved with SQLCLR, and even though it is frustrating that no resources are increasingly devoted to it, there is not a evidence to suggest that SQLCLR has deprecated, even unofficially.

Leave a Reply

Your email address will not be published. Required fields are marked *