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.