The stuff that I've used CLRs for are things that, as far as I know, can't be done using the native SQL environment.

In particular, at times I require code which sends a HTTP web request and receives a response. This task needs to be executed on a periodic basis - once per hour to be exact.

In order to accomplish this task, I've found only three solutions. One is to have the web request source executing the task on the periodic basis, and updating the database. This is not an option, since the web request is from a third-party, and I can't easily have that third party modify their application to communicate with mine. Another option is to have a Windows scheduled task which executes the web request and then updates the database. The final option was to have a database job which would execute on a periodic basis. This one proved easiest to manage, and all it required was for me to write a .NET CLR which would execute a HTTPRequest and return back the HTTPResponse string.

The other instance that I've used CLRs is when I needed to move files between servers. I couldn't find a way to do this through the native SQL interface, so I wrote a CLR which would handle moving the files between servers. I have found it possible to do it in native SQL using the xp_cmdshell extended stored procedure, but the limitations for that are that it becomes *very* tricky to handle mapping network drives and giving permissions to the users such that the files can be copied between servers.