Skip to content
October 24, 2006 / Bob Black

Sort it, Flip it, Fry it in SQL Server

I just found a little article in the .Net Framework 2.0 SDK Documentation called Reliability Best Practices (when authoring assemblies for use within SQL Server), which contained a little snippet I found interesting.

You may read along with me here.

 As a little backgrounder, in the latest and greatest version of SQL Server, SQL Server 2005, you have da powah to create Stored Procs, Funcs, Triggers, and even new types, using the CLR. The process, in a nutshell, is:

  • Create a CLR class with some functions and compile it to an assembly
  • Create a stored procedure, function or whatever using the normal syntax (CREATE PROC, or whatever), with a few extra syntax-ual extras that tell SQL Server to run the function in your little CLR assembly whenever the sql proc is called.

You can read more about that in your spare time, but as I was saying, according to the SDK you need to be careful when writing CLR-based code that will run within SQL Server 2005. And by careful I (and they) mean don’t use pointers. Unless it’s wrapped in a SafeHandle.

 In the case of an AppDomain unload, you cannot depend on finally blocks or finalizers being executed, so it is important to abstract all operating system resource access through the SafeHandle class rather than IntPtr, HandleRef, or similar classes. This allows the CLR to track and close the handles you use even in the AppDomain tear-down case. SafeHandle will be using a critical finalizer which the CLR will always run.

And further down the page, we’re instructed to ensure finalizers and finally clauses do not have to run to prevent leaking operating system resources.

So basically, I think what they’re trying to say is: don’t be bringin’ your ol’ nasty pointers around SQL Server. That’s how I interpret it. Yeah, you could use a SafeHandle, but a stored proc doesn’t seem like a healthy place to be doing pointer arithmetic or grabbing handles to system resources.

Actually, the more I think about it, the more I fail to see a good reason to run .Net code within SQL Server at all. I mean, hey, the coolness factor here is sky high, but how often do you really need to be able to fill a DataSet, sort it flip it and fry it, all within SQL Server?

Is this really a conspiracy by Microsoft to “dumb-down” SQL? So that those developers referred to by Joel Spolsky as “commodity developers” can circumvent writing complex SQL queries by just moving stuff into a CLR HashTable or an ArrayList and looping through it over and over until they get the right daters, right there within SQL Server?

Ah well. I’ll probably end up using this feature on my next project.



Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: