I use CLR to create a node type that models the SMO object model hierarchy. My application has been in use since before the vendor supplied assembly with the hierarchyid, actually since before the SQL 2005 RTM... I probably could have made the hierarchyid type work but with the one I coded I also get a tree representation. The advantages of the node as a CLR type are that I get a single compact, index-able numeric value that is a pkey and can also used to index based only on mid level nodes of the tree - if it helps my queries - yet never have to parse a string, recurse a parent-child hierarchy or mess with constructing/deconstructing a bundle of character columns into the node when I move the hierarchy between the database and the application tree views. I also get the ordering and DML operation advantages of the hiererchyid. My storage efficiency is not as good as the hierarchyid, in part because when I started the project I was stuck with the 8000 byte requirement that has since been improved considerably, but it kept my eye off the storage ball early on and have just not had a reason to revisit yet.

I have also used the CLR for proxied IPC communications between database instances to mange SQLTrace rather than having to resort to the command-line, OLEDB or IS. Makes it easy to tell if something actually worked on the other end or if not, what the error was when trying to manage many instances with out rolling a bunch of cookie cutter stored procedures out to every target and then trying to keep them in sync. In this usage only have to enable the CLR on the instance that the DBAs use to manage the environment. Thinking about developers using the CLR too much will cost me the other two hours of sleep so I try not to go there...