How does this relate to SQL CLR?

[MgSam]

I just read the blog post announcing this project. (I would have asked this there but for some reason all commenting is disabled on the SQL blog)

Is this project a replacement for SQL CLR (which as we know is still on .NET Framework)? Is the expectation to have eventually have feature parity?

Thanks.

[charlesroddie]

Tacking on a question here: is the C# language extension C#-specific or is "C#" used to mean .Net?

[uc-msft]

The C# language extension is not a direct replacement for SQLCLR. The extensibility framework & the language extensions were intended as a way to extend the surface area of SQL Server and provide an execution environment for runtimes closer to the database engine. Other motivation is also provide an open source framework that can be used to onboard new runtimes without engine changes. Yes, currently the surface area is restricted to the system stored procedure - sp_execute_external_script. We have talked about allowing creation of procedures or functions that contains external script. We will look at it based on feedback. Integration with user-defined types or aggregates are out of scope for extensibility at the moment since the scenarios are different.

At high-level, below are some of the differences between SQLCLR & Extensibility:

Feature	SQLCLR	Extensibility
Platform support	Windows & Linux. Linux supports only SAFE assemblies	Windows & Linux. Full parity in terms of functionality
Mode of execution	In-proc	Out-of-proc
Isolation	CLR code executes within engine process. Instance administrator need to trust all assemblies/code.	Runtime execution is outside of the engine process. Further isolation is provided using App Containers in Windows or Namespaces in Linux. External network communication is also disabled by default
Declarative syntax (T-SQL)	User-defined types, user-defined aggregates, functions, procedures, triggers	Only ad-hoc execution using sp_execute_external_script
DDL support	CREATE ASSEMBLY to upload user code and define other objects (functions, procs, triggers UDTs, UDAggs)	CREATE EXTERNAL LANGUAGE, EXTERNAL LIBRARY to manage extensions and libraries
Library support	Achieved via assemblies	Libraries for specific runtime can be used (Ex: R or Python packages, Java libraries)
Runtimes supported	.NET Framework	R, Python, Java, C# or Bring your own runtime (BYOR)
OSS framework	N/A. Can be extended via user-defined .NET Assemblies	Yes. Extension SDK provides authoring of new extensions or integration with runtimes without engine changes
QO integration	Operator level integration for various syntax including parallelism	Single external script operator to send/receive results and data from runtimes. This operator supports batch mode execution and parallelism
Resource Governance	None. Few knobs outside of resource governor	Provides EXTERNAL RESOURCE POOL object as a separate mechanism to govern resources consumed by the runtime/external scripts. Policies can be defined for external runtimes in addition to the SQL workload.
Permission model	Instance level control with db scoped objects	Instance level control with db scoped objects
Performance	SQLCLR code will typically outperform Extensibility due to nature of execution.	Ideal for batch oriented execution.
Monitoring capabilities	sys.dm_clr* DMVs & limited SQLCLR specific perfmon counter	sys.dm_external* DMVs, external resource pool DMVs, Windows Jobobject perfmon counters

Hope this gives you better idea of the differences and when SQLCLR / Extensibility can be used.

With respect to using the C# extension or SQLCLR, it depends on your scenarios and goals. I will give you few examples. If you have need to extend T-SQL surface area with your own aggregates or types then SQCLR is the way to go since type or aggregate cannot be defined using extensibility mechanism. If you want to leverage existing data science expertise in your organization or team then using R/Python integration with extensibility is desirable. Similarly, evaluate your performance goals and decide. Implementing regex function in C# and using SQLCLR will be much faster than using extensibility to invoke Python script that performs the same regex functionality.