Advantage of SQL SERVER CLR

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.

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.

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

Marc