A SQL Server Language Extension function is executed in an external process. Does it mean that when such a function is called in a Select clause it creates a new process for every row in the recordset on which it is applied?

CodePudding user response：

I think you are confusing two different features / technologies.

• SQLCLR is the ability to run .NET code (most often C# or VB.NET, but sometimes Visual C and occasionally F#, though F# is not officially supported) within the SQL Server process. This code can be called as stored procedures, triggers, scalar functions, table-valued functions, aggregate functions, and even user-defined types. This has the ability to execute T-SQL within the calling session, even if there's an active transaction (i.e. true in-process execution).

• External Scripts (language extensions) is newer than SQLCLR and is not .NET-specific. Languages that can be used are: R, Python, Java, and most recently C#. This code can only be executed via the sp_execute_external_script stored procedure; there is not option to call these external scripts via a function. Hence, there is no concept of per-row execution. These scripts are executed by a separate service (i.e. external), hence there is no option for in-process T-SQL code execution (meaning: executing T-SQL via an external script will connect as a separate session).

Additional details can be found in my post:
SQLCLR vs SQL Server 2017, Part 8: Is SQLCLR Deprecated in Favor of Python or R (sp_execute_external_script)?