Introduction

SQL Server CLR executables are probably the most widely anticipated and possibly the most widely misunderstood of the new Visual Studio and SQL Server 2005 features. Over the last few years (long before the final release of SQL Server 2005), every developer on this side of the galaxy was exposed to magazine articles, technical conference sessions, and street performers that detailed how the .NET Framework's Common Language Runtime (CLR) was to be incorporated into SQL Server. As the sessions extolled, for the first time, stored procedures, triggers, functions, user-defined datatypes, and aggregates could be written in a non-T-SQL language. Based solely on the enthusiasm of the presenters, some developers left the sessions early so that they could get back home to rip out all of their existing stored procedures and replace them with C# code. We all have been cleaning up the mess ever since.

On a more conservative note, I wrote my share of this content in an attempt to return sanity to these presentations and portray the new CLR coding feature in a more realistic, down-to-earth light. It seems that others (and too many from Microsoft) could not dampen their unbridled enthusiasm for stored procedures written in C# or Visual Basic .NET. Those leaving these Microsoft sessions were still convinced this technology could solve all of the world's problems, including global warming and the war in the Mideastbased on the current state of the planet, I don't think it's working.

As I'm about to demonstrate, CLR executables can't lower ocean levels and they aren't a good substitute for your run-of-the mill stored procedures and T-SQL functions. This chapter tells you why. Don't get me wrongthere are excellent reasons to leverage the power and performance of the .NET Framework and CLR executables, but you'll find that not all situations call for them. In this chapter, I'll discuss the hows, whys, and whens of this new SQL Server CLR executable technology. For those cases where CLR executables make sense, I'll show you how to design, code, debug, and deploy a number of executables. All along the way, I'll compare performance among various approaches and show you situations where a CLR stored procedure or function can make a positive difference and those where it just gets in the way. We'll see how DBAs can control this new feature (to an extent) and how developers must gain the trust and rights from DBAs to get access to the technology.

Can Visual Basic .NET Developers Play?

A senior data-access manager at Microsoft once suggested that CLR executables were not really for "Visual Basic" programmershe implied this new technology was too complex for them to grasp. I (and others) took umbrage at this remark and nearly rushed the stage with drawn weapons. We made it abundantly clear to this "expert" that many Visual Basic developers were fully capable of dealing with the most complex issues Microsoft could throw at them. That said, I also agreed that some developers (regardless of their language of choice) were not ready to dive head-long into the complexities of SQL Server CLR executables. I understand that many application developers are not SQL Serveraware or competent DBAs. Yes, I expect that the discussions in this chapter are possibly some of the most complex in the entire book. I suggest you read it anywayregardless of your skill levelespecially if you plan to use SQL Server CLR executables anytime in the future. In addition, after having read this chapter, I expect that virtually any developer will have enough information to really screw up their production SQL Server.

What Role Can Visual Studio Play?

For reasons I fully understand (but do not excuse), many magazine articles and MSDN help topics on this new technology act as if Visual Studio 2005 does not exist. They walk through the fairly laborious process of code, testing and deploying the CLR executables via command-line and SQL Enterprise Studio queries. They must think it's more macho to not use the tools Microsoft has made available. I suspect that those writing these articles work strictly with SQL Server and don't lower themselves to work with Visual Studio. Ironically, it turns out that Visual Studio 2005 has eliminated much of the grief we all face when working with CLR executables. It actually makes development easiereven dangerously easy. I'll be sure to show you the easy and safe ways.

Just one more point on Visual Studio 2005. Since I wrote this chapter, I've spent countless hours working with the design, coding, deployment, and debugging steps needed to create over a dozen CLR executables. Yes, Visual Studio 2005 works better than ever before. Given that I've worked with the earliest (pre-public) versions of Yukon and Whidbey, I've also spent countless hours trying to help Microsoft make these products work well together. The end-result is pretty goodbut just that. I endured any number of incidents where Visual Studio 2005 locked up, got stuck in the mud, or rolled over in the ditch smoking with its wheels in the air, tossing hours of coding into the ditch as it crashed. No, Visual Studio 2005 is far from perfect, but even with these problems, it was really pretty easy to create most CLR executables. This does not include User-Defined types (UDTs), as Visual Studio does not know how to let one debug a CLR UDT without reinstalling it firstwhich is not an option due to dependency issues. Yes, Microsoft is still working on these issues. Let's hope they figure them out soonhopefully without reinventing the wheel (again)s.

IMHO

Making it easy for developers of all skill levels to screw up their production servers with ill-conceived CLR executables is not such a good idea.