SQL CLR is the ability to create SQL Server programmability objects using .NET languages and framework.
Objects you can create with this technology:
- Stored Procedures
- User Defined Functions
- Aggregate Functions
- User Defined Data Types
When to use SQL CLR vs T-SQL
- User Defined Data Types – These are native .NET objects. There is no other option for creating a UDDT than this one. The already built-in XML data type is an example of a UDDT.
- Aggregate Functions - There is no other option for creating an Aggregate Function than this one.
- Use of external resources – Say you already have a .NET library that accomplish a certain operation. It won’t make sense to try to emulate what that component does in T-SQL (in the case you could). Using SQL CLR you can leverage on already made component while maintaining process centralization and data integration at the server side. Also, you can leverage on the use of all the functionality found in the .NET Framework Library and the use of the power and flexibility of some native .NET constructs such as arrays, collections and for-each loops.
- Complex calculations and logic - String manipulation and advanced math operations not available in T-SQL.
When NOT to use SQL CLR
- Direct data operations – T-SQL is specifically designed for that.
- Simple data manipulations or logic – T-SQL already provides some basic string manipulation, math operations and logic elements, such as string concatenations, math functions, IFs and loops. You should try to use built in T-SQL functionality as much as possible.