When I first heard about “stored procedures being written in C#, VB.Net, … etc.” I remember thinking; Is Transact-SQL dead? Am I out of a job? What does this mean for me and for DBAs in general? Luckily, I learned quickly that not only is Transact-SQL not dead but it’s got some significant improvements that might make code written in client applications A LOT easier – for example:
1. New syntax to traverse hierarchical/recursive relationships,
2. Extended XML integration,
3. Much improved error handling,
4. Easier in-database pivoting of data
I was worried that the CLR would cause problems for the stability of the database, acting like badly written extended stored procedures which can leak memory, crashing the server or even worse, potentially become a vector for worms and viruses. However as I learned more about the technology, I think that Microsoft has put in the work to give the DBA control over this technology. SQL Server controls the memory used by the CLR, there is a fully integrated security model and the DBA has control over what the CLR code can do and can even turn off the CLR completely on systems that will not use it. In fact, I also learned that the SQLCLR was going to be “off by default” as well. And, for me, as well as many DBAs, I think these points have squashed many of our fears. But then I almost felt like I was missing something? If things didn’t need to change then why are they changing?
So, then I just wanted to know more about why the extension was being made to begin with and when and why it might be useful. From considering that point alone came the most important thing I’ve learned about SQL Server 2005; there are many new ways of doing things: Transact-SQL; SQLCLR; XML; Full-Text; Integration Services; Analysis Services; and all of them have different benefits and implementation effects. DBAs need to learn when to choose each of the technologies and even more importantly when their use is not appropriate, even when they’re not an expert in all of them (and I truly think it will be a challenge to be an expert in every area of SQL Server 2005).
SQL Server 2005’s feature set has been expanded such that C# or VB.NET can natively run in the server. This gives you immediate access to the power of the .NET Framework and helps to allow better integration with existing utility components and web services already in place. Some of the greatest benefits are that it allows you to place complex computations on the server – close to the data – without having to send large amounts of data to a separate application server. And all of this is done while SQL Server manages memory and resources ensuring a more balanced server system. So, my main recommendation for DBAs who are skeptical is to not worry about knowing everything about SQLCLR but instead focus on why it might be proposed and determine if SQLCLR is really the best way to handle it!
I’m still convinced that Transact-SQL wins when interacting with SQL Server using set-based SQL commands, but if you have a stored procedure with a cursor it may be a candidate for the CLR, if you have code that loads all the data in a table to perform special aggregations it may be a candidate for the CLR, as might your table valued functions (as the CLR does a really optimized job here). To sum up: the DBA has to get a grip of these new technologies and not let the development community overturn years of best practices in database system design – the same principles apply, there is just a richer toolset that, when used wisely, should improve performance and developer productivity.