I am aware that CLR stored-procedures is more efficient in procedural code and T-SQL is better for queries – however I am having a hard time deciding what to use for my specific needs, and I would like to know if there are other things I should consider when making my decision.
The code I am planning on creating involves querying a set of data, implementing some algorithms (that will become more complex over time) and insertion of the data to the database. This stored procedure will be deployed by a scheduler – most probably azure webJobs since as far as I know Azure SQL does not support db jobs.
why I think of using C#
Assuming I can load DAL libraries from my server, and leveraging OOP – I guess I can implement a more coder friendly solution to this problem – and as for performance I assume it would be better or at least not noticeable in comparison.
Why I think of using T-SQL
I don't actually believe it is a better solution, but there might be something I am overlooking that might be a good case for T-SQL, because it makes no sense to me that it would have none.
Most importantly
Is there a way I can create a T-SQL stored procedure, call a C# stored procedure with complex objects as parameters – do my algorithms and return the data to be inserted, and insert it from T-SQL? seems like the most elegant, and best performing(?) solution (assuming it's possible and there are no drawbacks to calling CLR from T-SQL).
Best Answer
First off, just to be clear: SQLCLR / .NET / C# / VB.NET cannot query the database. Only T-SQL can query SQL Server. So in order for SQLCLR code to get data or interact with SQL Server in any way, it must establish a
SqlConnection
, like any other .NET app, and submit T-SQL, or execute a Stored Procedure.Yes, you can call a SQLCLR (whether it is C# or VB.NET does not matter) Stored Procedure, or Scalar Function, or Table-Valued Function (TVF), etc from T-SQL. In fact, that is the only way you can call SQLCLR objects. Classes representing the SQLCLR objects, defined in Assemblies, need to have T-SQL wrapper objects defined that point to those classes so that they can be invoked.
Sending complex objects into SQLCLR objects can be accomplished, but unfortunately not via Table Valued Parameters (TVPs). However, you can either create a local temporary table in T-SQL, populate it, and select from it in the SQLCLR object, OR you can package up the complex data in XML and pass that in as a parameter to the SQLCLR object that will traverse it via an
XmlReader
.For getting the data back efficiently, you can either:
INSERT
statements from the SQLCLR Stored ProcedureINSERT INTO ... EXEC
for a SQLCLR Stored Procedure, orINSERT INTO ... SELECT
for a SQLCLR TVF.As far as loading you DAL libraries goes, that could work, but there could also be complications. It all depends on what you libraries are doing and what Framework libraries are being referenced. Only a small subset of the .NET Framework libraries are supported in SQL Server's CLR host, so if your libraries reference other libraries that are not "approved", then you will need to load those libraries manually, along with any of their dependent libraries (if there are any). The complicating factors here are that loading unsupported .NET Framework libraries requires that they be set to
UNSAFE
, which in turn requires that the Database be set toTRUSTWORTHY ON
. The other complication is that loading the .NET Framework library might not even be possible in the first place since the CLR host inside of SQL Server only allows pure-MSIL Assemblies, and so you cannot load mixed-mode Assemblies. Some .NET Framework libraries are currently mixed-mode and won't load. Others are currently "pure" and will load, but there is no guarantee that those will not ever be changed into a mixed-mode Assembly in a future .NET Framework update. If/when that happens (and it has happened, such as with System.ServiceModel), then you will need to re-code you Assembly. So it's best to stick with the supported Framework libraries as those are guaranteed to continue working through future upgrades.Keep in mind that App Domains are per Database, per Owner (of the Assembly). This means that, in terms of any particular SQLCLR object, all sessions / SPIDs will be calling the same code and sharing the same memory. This is why the methods need to be declared as
static
. And this is why attempting to use non-readonly
static class variables requires the Assembly to be marked asUNSAFE
: because the value of such a variable can be overwritten by another session / SPID (case in point: SQLCLR assembly throws error when multiple queries run simultaneously).With respect to this statement in the Question:
I wouldn't assume anything here. There are a lot of factors that influence performance in either direction:
IsDeterministic=true
, then you can't get the benefit of it being able to participate in a parallel plan (something T-SQL UDFs can't do).Hence: don't assume; test!
If you are looking to learn more about working with SQLCLR, you might want to check out a series that I am writing on this topic on SQL Server Central: Stairway to SQLCLR (free registration is required to access their content). There are several articles and more coming, and plenty of examples to illustrate various features, security, etc.
Regarding Azure SQL Database: please note that while support for SQLCLR (
SAFE
Assemblies only) was added in late 2014, it was recently (and supposedly temporarily) removed. Customers were emailed on April 8th, 2016 of the feature being removed on April 15th, 2016. The most official notice I could find was this thread on reddit:Customer:
Jan, a PM in the Azure SQL DB team working on performance and Elastic Pools:
and Tommy, a PM in the Azure SQL DB Security team: