I use CLR extensively, and while it may be possible to kludge some convoluted solutions together to accomplish the same thing, if coded correctly, and used appropriately, it makes code immensely simpler and allows for solutions to be deployed quicker. I have created a couple of very specialized CLR's for specific encryption requirements (the requirements dictated the usage of a specific encryption methodology, the key length, the seed requirements, # of hash iterations, etc).

But my biggest time saver has been using the SQL# libraries from www.sqlsharp.com . ** disclaimer: I am in no way affiliated with SQLSharp, and paid full price for my license **

I have been using the library for years now, and Solomon Rutzky (the creator) is immensely helpful when it comes to adding functions, procs, features, and/or fixing issues. Here's some sample examples of what I use it for:

Development

* Reqular Expressions (try validating (or even semi-validating) email formats without it)

* String splitting (yes, there are fast Tally solutions out there, but they're still slower than CLR (small and large strings))

* String concatenation (yes, there are other ways to accomplish this, but the CLR function is simple and fast)

* Validation of data type conversions (Mostly used before the SQL 2012 TRY_CONVERT function existed)

* Creating ranges of Dates/Times (very handy for some scheduling applications)

* Creating ranges of numbers (always useful, especially if you want the numbers to be stepped (e.g. 5,10,15,etc)

* Date manipulation and formatting (you can do all of this manually, but...why?)

DBA

* File/Directory manipulation (yes, you can enable xp_cmdshell, but the results are cleaner and easier to work with in CLR, and so are the error messages)

* Internet access (the ability for me to easily call a CLR proc that goes and retrieves OR posts!! something across the internet is to me worth the price right there!)

* XML/XSL transformations (I actually requested this functionality, and Solomon graciously added it to SQL#) (For me, this comes in handy when I want to send a formatted html email message that is created based upon data in the db. It's basically a way to do mail merges. I can retrieve the necessary data elements (some scalar, some set based) and then apply an XSLT to generate a HTML email subject. This is a PITA to do manually)

ETL

* FTP (yes, you can do all of this via xp_cmdshell, but it's easier through SQL#)

* Internet access (see above) (some examples are retrieving a file from a website over port 80 through the firewall)

* Dynamic bulk importing and exporting of data (SSIS, BULK INSERT, bcp all have their pros and cons, there ARE ways to get the best of both worlds!)

The list goes on. Obviously, I'm a fan of SQL# and highly recommend it! I look at it this way: If Microsoft announced that there were 230+ new functions within SQL Server, the blogosphere would go crazy. To me, having this library is like having the complete tool box that everybody else either wishes they had, or doesn't even know they need because they've been hammering nails with a screwdriver so long, they just think that's *the way* to do it.

Now, I'm not advocating install SQL# on every database and let your developers go crazy with it, but it seems short sighted to not at least examine it, test it out, profile it, and release only what is necessary in your environment. Once you do, you'll wonder why you didn't sooner.

I've done a few presentations at our local user groups and SQL Saturdays and I have to say that I see people's eyes light up with ideas on how they can use this library and the time that it could save them.