SQL Server User Group December 2005 - SQL CLR Integration

This month's Melbourne SQL Server User Group meeting was all about the CLR integration in SQL 2005. There has been a lot of hype about this feature and you can find various views ranging from “use it for everything” to “don't even enable it”. The talk we given by one of our local members, Greg Obleshchuk from A & G Software and I think he did a great job of presenting the good points of the CLR integration and highlighting when to use it and when not to use it.

As a general rule of thumb T-SQL is still the best for set based operations. Where the CLR really shines is when complex mathematics, string manipulations or access to external resources are involved.

The following are a list bullet points from the notes that I took during the talk:

  • Namespace declarations are ignored in assemblies that are compiled in order to be hosted in SQL Server. All this really means is that you cannot create two different methods with the same name that are only differentiated by namespaces. I don't think this is a big issue.
  • When you are writing CLR stored procedures:
    • the Pipe object is used to return messages and recordsets.
    • the SqlMetaData object is used to create columns
    • SqlRecord is used to create a record (with SqlMetaData columns)
  • The standard tools that are distributed with SQL Server do not provide debugging support for CLR based procedures. You need to have Visual Studio .Net 2005 Professional or above.
  • CLR stored procedures are much faster than equivalent extended stored procedures. The rough speed test that Greg did showed the CLR being twice as fast as an extended stored proc in SQL 2000 and 3-4 times faster than the same extended stored proc in SQL 2005. Yes, existing extended stored procedures will run roughly half as fast on SQL 2005 than on SQL 2000. We did not really establish why this was, I suspect that SQL 2005 is probably taking extra steps to try and protect itself against rogue procedures, although Greg was able to demonstrate how a small programming error could cause a buffer overrun that would crash the SQL Server.
  • We went through an interesting example of calculating complex sales commissions using a custom aggregation and one interesting bit of information that I picked up was that aggregates maintain their state between calls by serializing and de-serializing themselves. Since all other SQL Server CLR methods are declared as static (C#) or Shared (VB.Net) I had been wondering how aggregation functions maintained state.
  • During the discussion at the end of the session it was revealed that CLR procedures will most likely out perform server side cursors due to the fact that CLR procedures will do most of their processing in memory if possible, while even the simplest cursor will have to materialize itself in tempdb, effectively writting the resultset out to disk before it processes each row.

Print | posted on Wednesday, December 21, 2005 7:03 PM