Design Common Language Runtime (CLR) table-valued and scalar functions.
This objective may include but is not limited to: assembly PERMISSION_SET, CLR vs. T-SQL, ordered vs. non-ordered
For this bullet point it seems like we should be looking at the costs and benefits of using SQL CLR vs T-SQL for the Table Valued and Scalar User Defined Functions. We will also want to look at the security settings for CLR and their implications. Finally we should determine what the consequences of using CLR user defined functions are in ordered vs unordered sets.
Here I am referencing the Create Assembly, Overview of CLR Integration, Performance of CLR Integration, CLR Hosted Environment, CLR Integration Security and Architecture of CLR Integration BOL pages. I also watched the SQLCLR MCM training video.
The assembly permission set is specified when the assembly is registered with the server and is used to identify the security for the assembly. The options for the permission set consist of SAFE, EXTERNAL_ACCESS and UNSAFE.
This is the most secure mode for the assembly and contains the most restrictions. These assemblies need to be type safe and can not call un-manage code. SAFE assemblies have enough permission to run, perform computations and access local database. SAFE is the default PERMISSION_SET option when no PERMISSION_SET option is specified.
The EXTERNAL_ACCESS mode essentially runs under the same requirements as that of the SAFE mode, with the exception that it can access external resources to the database (such as files, networks, environment variables, registry)
The UNSAFE mode is set up with no security restrictions and with the ability to call unmanaged code. Only members of the sysadmin fixed server role can create or alter UNSAFE assemblies.
CLR vs T-SQL
The calling overhead for a CLR user defined function is less than that of T-SQL due to a quicker invocation path. CLR managed code has a performance advantage for procedural code, computations and string manipulation. However, T-SQL code is much more efficient at accessing data. Note that Scalar UDFs probably shouldn’t access data since it will be called once per row… potentially more than once per row if there is an order by clause. If data access is to be used in SQL CLR, it must be specified through an assembly. Similarly, Determinism and Precision must also be specified through the assembly.
Table Valued UDF
First off, the inline (single statement) table valued user defined function is not supported in SQL CLR. When creating a CLR table valued user defined function it will be written as two static methods. The first method (InitMethod) returns Ienumerable / Ienumerator. SQL Server calls the Next method on the enumerator. The Second method (FillRow) will be called as long as Next returns true.
Ordered vs. Non-Ordered
When creating a CLR Table Valued Function you can specify the ORDER clause to specify the order that the results will be returned. If this order is specified, the results MUST be returned in this fashion, or SQL Server will generate an error. Using the ORDER clause allows the optimizer to take advantage of the ordered result set when inserting queries with a compatible index, when an ORDER BY clause is specified in the query, for aggregates where the GROUP BY is compatible, and where there are DISTINCT aggregates that are compatible.
Note that the ordered function can be MORE expensive than the unordered if the requested ordering is different than that which is returned. (As seen in the fibonacci sequence generator referenced in SQL Server 2008: Ordered SQLCLR table-valued functions (Bob Beauchemin) ). As such, it might be advisable to create a separate function for each order and one for an unordered set.
See Using Sort Order in CLR Table Valued Functions section of the Create Function BOL Page.