Introduction to SQL CLR Extensibility

This blog post provides additional resources and links for my Introduction to SQL CLR Extensibility webinar.

Background / Overview

There are a number of decent resources out there that introduce SQL CLR.

Greg Low’s SQL Server CLR Integration book is a great, high-level, overview.

Bob Beauchemin’s A Developer’s Guide to SQL Server 2005 provides some fantastic, low-level, info. (And I wouldn’t worry much about it being for SQL Server 2005).

I’ve also found that Derek Comingore’s and Douglas Hinson’s Professional SQL Server 2005 CLR Programming does a nice job of covering details along with providing a ‘soft’ introduction.

Sadly, I haven’t found too many online articles that do a great job of introducing SQL Server CLR, but one from SQL Server Magazine from a while back does serve as a great overview/introduction to SQL Server CLR.

Performance

DBAs worry about the performance overhead of the CLR. The tricky news is that the CLR is already LOADED into your SQL Server. It’s sitting there, in memory, just waiting to run your code. So, in that regard, just as you can run ‘dumb’ queries through the ‘T-SQL’ engine, you can run ‘dumb’ queries through the ‘CLR’ as well.

Moreover, it’s important to point out that the CLR can hands-down outperform T-SQL in a LARGE number of cases. Knowing whether or not it’s faster or not really depends upon you doing a thorough job of testing performance. Using estimated and actual execution plans to profile your performance is a good start – but since those execution plans LIE, you’ll want to make sure that you’re testing ALL implementations through SQL Server Profiler – so that you get an accurate feel for what’s up. (And I cover this in a bit more detail within the presentation linked at the top of this post.)

And, when it comes to performance, one thing you may find is that when using the CLR you’ll need to do lots of ‘SCANNING’ functionality. Scanning, of course, is a LESS performant (sometimes HORRIBLE) option. But, in some cases you may NEED to scan with increased capabilities that the CLR offers (to parse out business rules, handle special formatting, and so on). In cases like this, don’t abandon the CLR if you find that it’s scanning. Instead, try to use SQL Server intelligently by forcing it to SLICE data with index seeks, and the ‘DICE’ data with the CLR. That way you can get the best of ‘both worlds’ – in the sense that you can balance SQL Server perf benefits with CLR extensibility/flexibility benefits.

I actually wrote about this a while back on DevProConnections – and the approach outlined there works for both CLR and T-SQL queries/approaches where you need to generate high-performance results:

Generating High-Performance SQL Server Query Results

For additional information about just how well the CLR can handle itself from a performance perspective, as well as in terms of things to watch out for (given the fact that strings in the CLR are immutable), make sure to check out a recent blog post from Aaron Bertrand (where he talks about realizing the benefits of SQL Server CLR performance).

His post, in turn, points to a post (which, in turn, points to another post) by Adam Machanic that goes very heavily into issues with optimizing SQL Server CLR performance as well:

SQLCLR String Splitting Part 2: Even Faster, Even More Scalable

Faster, More Scalable SQLCLR String Splitting

Demos

Finally, I’m including a link to the demos I showcased in this presentation.

Download: Introduction to SQL CLR Extensibility Demo (.zip)

The demos are available as a .zip file – which includes the slides for this presentation (as a .pdf), and a ‘Demos’ folder where I’ve got the scripts I used during the demos, and where I’ve also included both of the VS 2008 Projects that I used in this presentation. (Well, actually, I only got around to using the first project – but I’ve outlined the use of the second project in this blog post – where I show how to add external references in SQL CLR Assemblies.)