Design Common Language Runtime (CLR) table-valued and scalar functions
May include but is not limited to: assembly PERMISSION_SET, CLR vs. T-SQL, ordered vs. non-ordered
From SQL Server 2005 we have the nice feature of running managed code right inside SQL Server. This is something similar that Oracle did with Java, but much more cooler. You can think that if you can code in .NET, than T-SQL is certainly dying. This is not true, and because this objective covers the CLR vs. T-SQL topic, here’s a brief when and what:
Consider using SQL CLR when:
- Pulling a lot of data, and executing lots of operations on it, especially string operations and the like.
- Highly procedural code with lots of steps.
- Having existing XPs, or accessing external resources from SQL Server.
- Performing lots of calculations on pulled data.
T-SQL triumphs when:
- When you pull little data over the wire, and do little processing on it.
- You are dealing with set-based operations. T-SQL is always faster in it.
To work with SQL CLR, you need a DLL, you need to register that DLL with SQL Server, and then register each appropriate member of that DLL with the appropriate procedure.
There are three security levels (i.e. PERMISSION SETs) when you work with SQLCLR. Here are them:
- SAFE: the default, allows no external resource access from SQL Server.
- EXTERNAL_ACCESS: allows some external resources to be reached, such as registry entries, the fie system, environment variables, the network, etc.
- UNSAFE: allows for unmanaged code to execute. This is the least restrictive.
Note that you set these permissions on the assembly level. To set anything other than SAFE, you have two options. Either you set the database to TRUSTWORTHY and you need the correct permission, or you should sign the assembly with a certificate associated with the login. Go for the former!
OK, so we roughly know what SQLCLR is, than let’s dive into the objective here: table valued and scalar UDFs. We also know what a TVF is, so I won’t waste time on writing that again. To implement a SQL CLR TVF, you need some attribute working, and set the return value to the IEnumerable interface (my favorite). The main difference between a common TVF and a CLR TVF is that the common one stores its values in a temporary store table, while CLR TVFs don’t need to do so.
As a minimum, you need the return method (IEnumerable) marked with the SqlFunction attribute, with two named parameters: TableDefinition – a string for the table’s construction – and FillRowMethodName – a helper method to build each row.
The other type of CLR function here is scalar. A scalar method is even easier to implement. Just create a public static method with the appropriate return type, mark it with the SqlFunction attribute, do some calculations and return data.