SQL Server Meets the CLR
I’m sitting in a TechEd session by a couple of ‘Softies from the SQLCLR team about programming the CLR in SQL Server 2005 (DAT318 if you have access to the materials).
One thing that strikes me is the last bullet point on the current slide:
"No more need for XPs!"
That strikes me as a bit overstated and simplistic. I’ve never written an extended procedure but have looked at the specs about what it takes. Ugly, ugly, ugly, so I’ve always fought tooth and nail to find a way around doing it. Besides, I’m a reformed C++ programmer from decades(?) ago and doubt that I could write a decent C++ comment today and get it to compile. Good riddance.
So eliminating the need for XPs would seem to be a good thing. I’m certainly much more likely to throw together VB.NET or C# code than an XP. It remains to be seen whether I’ll leave behind my hard-earned knowledge of T-SQL very often either, since .NET is inherently a procedural-based language. (Language purists note: Yes, I know, I know. OOP, events, blah, blah. But you’re inherently doing things step by step unless you do very messy threading stuff.)
I’m looking forward to probing when I should use each, T-SQL or .NET. The only things I can’t do in T-SQL but can in .NET code are user-defined types and aggregates. Clearly those are huge benefits to these two features and I’ll be all over those.
Aside: Whoa! The first demo is in VB.NET!!!???! What’s up with that? (For the uninitiated--and therefore psychologically undamaged--souls: Microsoft has a very bad habit to have their devs do C# examples. As though it had any clear benefit over VB.NET for stuff like this.)
The first demo is a comparison of an email address validation function. Because of T-SQL’s rather, um, limited string handling functions, the T-SQL code is a bit messy while the VB.NET code is a single line of code after the class and assembly wrapper stuff. So clearly one of the main times to use .NET is when you need language features not available in T-SQL or features of the .NET framework. The .NET code is one line, of course, is because it uses the framework’s RegEx features.
So, first impression: if you know .NET, writing SQLCLR will be trivial.
Oops! Now there’s a C# example. That’s cool. I’m all for equal treatment.
The System.Data.SqlServer namespace is the key to the link between SQL and the CLR when creating stored procedures in .NET. I guarantee that for at least the first six months of using this stuff I’ll be typing SqlClient. Sigh. On the other hand, I’ll also forget the SqlFunction attribute that decorates a sproc definition. But it’s a great way to essentially configure the .NET code for running in SQL Server. I really like how the SQLCLR team has integrated with .NET, taking advantage of its features rather than doing lots of reinvention.
Aside: If you’re one of the people sitting around me in this session and now reading this, my apologies if I disturbed you during the session. I hope the result is worth it.
Geez, the sting handling features of the framework are sufficient reason to write sprocs in .NET!
Debugging SQLCLR code in VS.NET is simple and sweet. Very nice. This is getting scary. This is a version 1.0 product??? Wow.
<SET GUSH MODE OFF>
Sigh. I AM starting to sound like Microsoft marketing slime (to use Mindy Martin’s term), aren’t I?
Aside: I hate it when speakers say they’re not going to take questions until the end (this is an almost full, rather large room), and then answer them when attendees rudely raise their hand or call out question. Aargh.
Cool! SQLCLR table-value functions return an iterator not the table as such. So, in theory, no big performance penalty. We’ll see, but they’re thinking about this stuff.
Only a subset of the framework namespaces are available in SQLCLR code. Makes sense, since the three examples they list (no mention if this is a comprehensive list) should never be used in a sproc: System.Windows.Forms, System.Drawing, System.Web. (Wait! I can’t run a Web server inside of SQL Server? Bummer.) They achieve this through what they call a "fusion loader hook" in the CLR Hosting kernel in SQL Server. I kind of bet that they have erred on the side of conservatism, which is fine. But that means that there will be things that I’ll want to use and will not be able to figure out why they didn’t include them. I’m sure it’s carefully thought out, but still.
They talk about a "walled garden" to protect code, which is achieved through a new "HostProtection" attribute in the CLR. Must be a version 2.0 .NET feature, I guess. Also, various unsafe constructs are disabled: thread creation, shared state or synchronization, sockets listening, no finalizers. I think those all make sense. SQL Server checks these when you create the assembly, using a "verification" process. (This is the new CREATE ASSEMBLY and CREATE FUNCTION T-SQL statements. There may be others.)
SQL Profiler works fine to watch what your SQLCLR code is doing.
Performance: "Functions are ALWAYS faster." He repeated that several times, but then said it depends. It seems like when you have heavy dependence on computations in .NET framework code. Makes sense. This implies that if the code does heavy manipulation of SQL objects it may not be faster. Aggregate functions are WAY faster--orders of magnitude--than server-side cursors. Whew!
One consideration about whether you should move what is now client or middle-tier code to the server is that the server is going to put the processing load on the server. Duh, but it’s a big consideration on how you do things.
Specific recommendations: Use T-SQL for heavy data access. There are other tips in the slides. I suspect we’ll be seeing heavy analysis of this over the coming months. I may even jump into the fray.
I wonder...
-
How I should architect SQLCLR code? Should I do monolithic assemblies? Keep them as small as possible? Or doesn’t it matter? Is it better to partition out code into other assemblies? Is it even possible?
-
If there are going to be any language differences for SQLCLR code? IOW, will any advantages of using VB.NET over C#, for whatever reason, surface because some language feature is better for in-SQL database programming?
We’ve moved on from the developer portion of the session to the DBA portion. The presenter has made fun of the usual void between DBAs and developers.
It looks like SQLCLR integrates with code access security as I’d expect. I really hope that it doesn’t mess too much with the AppDomain policy level at load time. (I’ve been butting heads with the VSTO way of mangling AppDomain at load time. It makes eminent sense, but it can throw you off if you don’t know about it.)
SQLCLR has three "CAS permission buckets" (Aargh!!!! Why can’t different parts of Microsoft call the same thing the same name???? Sheesh.) Anyway, they define three permission sets: Safe, External Access, and Unsafe. The permission set is determined at create time. Hmm. Not load time? This is probably a concession to performance, but we’ll see how or whether it mangles things too much.
Now that I think of it, these three "buckets" are described more like policy levels. I don’t yet know if this is just an artifact of their using non-CAS terminology or if they really are blurring policy levels and permission sets. Something to explore later.
They recommend that you use "safe assemblies" most of the time, since it is closest to the existing T-SQL model, where you can do things like restrict use of the command shell. Makes sense.
There is plenty of support for DBAs to monitor and view what’s going on with SQLCLR in their systems. I’m not a DBA though--at least, most of the time--so I leave it to others to evaluate this.
Random notes:
-
SQLCLR is off by default. I’d expect nothing different, based on Microsoft’s security philosophy.
-
You need CREATE ASSEMBLY permission to create an assembly. Man, I LOVE the newly granular permissions in Yukon! Very, very seriously.
A generally fine presentation. The only down side is that all the ‘Softies are using their current version of beta 2. So those of us on the outside can’t run much of this code now, until the beta comes out. Should be soon, but we can only dream for now. OTOH, it worries me that so many things are changing between beta versions. It’s not a matter of new features being implemented in each new beta, but they are changing syntax all over the place. The jury's out...