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.

Continue reading


Design T-SQL table-valued and scalar functions

May include but is not limited to: inline table-valued functions vs. views, multi-statement table-valued functions, determinism

Another feature of SQL Server which is very useful, but I’m not using it daily – User Defined Functions (UDFs). A UDF is like any other function. It takes parameters, works on them, and then return some results. There are three types of UDFs: inline table-valued functions, multistatement table-valued functions and scalar functions.

Let’s start with scalar UDFs, because they can be described very easily. They are functions that accepts zero or more parameters, and returns a single scalar value at the end. There are some built-in scalar functions in SQL Server, like ABS or SUM or COUNT, so you can have a good grasp on what I’m talking about. UDFs have a more rigid syntax than stored procedures, so here’s a brief one:
Continue reading