Years ago, I had a class project that used SQL Server as a back end and a website as a front end, and one of the...
incremental exercises I implemented on it was moving all those horrible ad hoc, in-line SQL Server statements to SQL Server stored procedures. Not only did this make the code cleaner on both ends and easier to maintain, but it got rid of a number of security problems I'd only just begun to learn about.
The whole experience left me with a profound appreciation for SQL Server and CLR stored procedures (SPs). Ever since, any similar teaching projects I've created have involved using stored procedures as single points of entry into the database.
Now, here we are in 2012, and SQL Server supports the use of common language runtime or CLR stored procedures from the .NET Framework -- a powerful but somewhat misunderstood bit of functionality. Some new admins see SQL Server CLR integration as a kind of fancy stored procedure system -- perhaps even a kind of next level of evolution for SPs in general. The impression they get is that eventually all stored procedure code could be moved into CLR assemblies.
For more on stored procedures
The advantages of stored procedures
Stored procedures or functions: Which should you use?
Dynamic SQL vs. stored procedures
The reasons they've come to believe this are not wholly wrong. The CLR provides the richness of the .NET framework's programming languages. T-SQL, on the other hand, can be clumsy to program in if you're trying to do things that don't lend themselves easily to a procedural language (as anyone who's wrestled with cursors likely knows). CLR assemblies are faster, highly secure and allow more effective reuse of code from other components you might have written.
So does that make CLR assemblies a candidate for dumping conventional SPs wholesale? Not really. Even with SQL Server 2012, the best reasons to use CLR assemblies over native Transact SQL (T-SQL) code remain pretty focused.
This catch-all word refers to a few different kinds of security. A SQL Server stored procedure that needs to address something outside of the database it runs on, like the file system, can be hard enough to write, but even trickier to implement in a secure way. Pass a malformed parameter to a stored procedure like this and you could end up silently trashing some of your files. The CLR provides built-in ways to do such things without sacrificing the overall security of your application or requiring you to tediously experiment with your own security.
T-SQL is best for fetching and performing basic manipulations of sets of data. It's less effective for doing the kinds of exotic transformations done more readily with a more robust language. It's not so much the language as the application that is ill-suited for this purpose. It's not that SQL Server or T-SQL are poorly suited to doing math. It's that you're better off freeing up SQL Server from doing such rote work so it can be done somewhere else -- in a CLR assembly running on an entirely different physical processor, for instance.
To that end, anything involving math, working with large data sets as arrays instead of cursors, large-scale string manipulations or operations that need to be scheduled in their own threads, are in the long run best off in an assembly. A common example of this: a stored procedure that returns a number of columns with computed values. If you're doing this with thousands or even millions of rows, you're best off using T-SQL to fetch the raw values, then performing the actual computations in an assembly.
Reusing existing CLR code
If you already have lots of code written in a CLR-enabled language, you are not stuck with either re-implementing it in T-SQL (which is never a good idea) or passing it back and forth between SQL Server and some external application; you can reuse that code directly by referencing it from a CLR assembly in your SQL Server app.
Separation of code and data
T-SQL code has to run on SQL Server itself; it can't be run on a remote machine, unless that machine is running SQL Server as well. CLR code, on the other hand, can be run on any client that runs CLR code. This means you can further enhance the separation of CPU-bound work from the database and run that work on an entirely different machine if needed.
An example of this would be a front-end client that fetches raw row-and-column data from a remote server, and then performs local transformations that would otherwise tie up SQL Server indefinitely.
Replacing extended stored procedures
This is a shoo-in, since extended stored procedures are a now-deprecated feature. Any legacy ESPs should be turned into managed CLR code whenever possible, since it provides just about all of the advantages of ESPs with none of their weaknesses.