Advantage of SQL SERVER CLR

Question

What advantages does SQLServer CLR offer over T-SQL? Is using .NET syntax easier than T-SQL? I see that you can define user types, but I'm not quite clear on why that's better. For example, you could define an email type and it would have a prefix property and a domain property. You could then search on domain or prefix or both. However, I don't see how that's any different from adding a couple of columns one called prefix and one called domain and searching on them individually. Maybe someone has real world reasons why this is better.

Answer 1

Different purposes. A CLR stored procedure is useful for things where writing highly procedural code or using system facilities not accessible from T-SQL would be of benefit. Although there is no inherent reason why one can't write application sprocs against it, generally you would not view CLR sprocs as a merely a different language for writing application sprocs. Typically, most uses of a CLR sproc would be for system purposes rather than application components, although this is by no means a hard and fast rule.

The CLR integration layer does offer some facilities that are not directly available from T-SQL stored procedures, such as custom aggregate functions. It also offers access to .Net libraries, which may be useful to get access to capabilities that T-SQL cannot support.

T-SQL does traditional database stuff, and integrates with the query optimiser, so it is still most appropriate for set-oriented database code. There are API hooks for CLR sprocs to provide information to the query optimiser, but this adds some complexity.

One can also use CLR integration to define functions that are accessible to T-SQL code. In some cases these can be faster and more memory efficient than T-SQL functions. The Wrox press book on CLR integration discusses this in some depth.

Answer 2

I'll give one good example: CLR has a built in RegEx object, which is sorely lacking in SQL Server. Now it's trivial to write functions to do regex-based validation constraints/repairs.

Answer 3

You can also e.g. call an external Webservice from a SQLCLR method - not exactly possible in T-SQL :-)

Marc