SQL Server stored procedures vs. CLR stored procedures: Time to migrate?
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
Premium Access
Register now for unlimited access to our premium content across our network of over 70 information Technology web sites.
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States.
Privacy
This was first published in October 2012
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.
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.
Security
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.
CPU-bound work
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.
Disclaimer:
Our Tips Exchange is a forum for you to share technical advice and expertise with your peers and to learn from other enterprise IT professionals. TechTarget provides the infrastructure to facilitate this sharing of information. However, we cannot guarantee the accuracy or validity of the material submitted. You agree that your use of the Ask The Expert services and your reliance on any questions, answers, information or other materials received through this Web site is at your own risk.
Join the conversationComment
Share
Comments
Results
Contribute to the conversation