Given that I develop currently in SQL Server (over 9 years now) including its predecessor Sybase and even spent a couple years on PL/SQL.  I am well aware of what TSQL is and is not. 

That said, my comparisons are NOT language specific, but historical.  People rooted in the belief that TSQL will stay around IMO are using the same mentality used by COBOL programmers a decade ago.  COBOL fell fast when new tools came on the scene and I predict that TSQL will do the same.  It will go slow at first, but then the conversion from an older not so well built language like TSQL to a modern language will happen quickly. 

I loath COBOL (save for the evaluate statement) as a language as it was restrictive and limited just like TSQL.  If you needed to do something out of the box, you had to take the long way around to solve it.  CLR will open up the database.  In some cases this will be bad, but for the most part it will be a wonderful addition to a developers arsenal.

Personally, I know many DBAs and all are looking forward to ditching TSQL.  It has its place, but in truth when trying to do more delicate coding it is a pain in the rear.  Personally, I welcome the flexibility and power that a programming language brings to the SQL architecture rather than fear it.