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


Evaluate special programming constructs

May include but is not limited to: dynamic vs. prepared SQL (CREATE PROCEDURE… WITH EXECUTE AS) procedure, protecting against SQL injection

It won’t be so surprising, but I tell you that SQL Server has some great optimization features. One of these is that it caches query plans. A query plan is a compiled form of what the database engine should do. It stores cached plans in a normalized form, and performs hash lookups when it meets with the same hash to skip compiling the given query, and use an existing execution plan. Marvelous.

Sometimes you need quite a lot of flexibility – let’s say you are dealing with optimal parameters. Optimal parameters tend to result in different query execution plans, thus slower performance. This is the time when dynamic SQL comes into place.

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

Design views

May include but is not limited to: common table expressions, partitioned views, WITH CHECK OPTION, WITH SCHEMABINDING

A view is a piece of metadata (a simple SELECT query) stored by the database server. It can be used like a table, thus you can query it, update the underlying tables, delete from them through the view, etc. What is the point of a view (you really shouldn’t ask this question preparing to be MCTIP)? We all have common select queries we run every day with joins, functions, etc. involved, and it takes a lot of time to enter the exact same query every time. Create a view for it, and you can even control the access for it with great granularity.

With a view, you can restrict access to even specified columns of a table, or just given rows (with a WHERE clause). Now with the advent of Common Table Expressions (CTEs) – views got a strong opponent. But you can even use a CTE in a view, making the best of both worlds (the CTE can recursively call itself). There’s a catch, however – views can’t get parameters.

Continue reading