A CLR Alternative To The SQL Server
I'd like to express a word of caution here...
I realize that VB and C# are both compiled languages that can be made to run at near machine language speeds. The problem is that calling CLRs does have some overhead and not everyone writes good VB or C#. CLR is not the performance panacea that so many make it out to be.
On the flip side, there are a large number of places where SQL Server will also run at near machine language speeds. Just because it's an "interpreted" language, doesn't mean that it can't use objects that have been compiled to run at near machine language speeds.
To wit and quite contrary to urban legend, there are a lot of places where doing something in T-SQL will actually blow the doors off a CLR equivalent. Just because something is written as a CLR doesn't mean the CLR will automatically win the proverbial performance or resource footrace.
Even if a CLR does end up beating T-SQL in one fashion or another, you might also want to ask yourself if it's going to be worth maintaining code in two places. For example if T-SQL takes 2 seconds to do something to a million rows and the CLR takes 1 second to do the same thing to those million rows, is the CLR worth it? Of course, Only you and your team can answer that.
My suggestion is that before you implement any CLR in production, that you do performance, resource, scalability testing, and a little checking on reality. Depending on what you're doing, you may have a huge surprise and sudden appreciation for what T-SQL can actually do for you. Usually with T-SQL, all you have to do is not suck real bad at using it. As with all else in SQL Server, "It Depends" ;-)
commented on May 30 2012 7:11PM