Recently, eWeek author Lisa Vaas emailed my wife regarding an article she was writing about CLR Stored Procedures in SQL Server 2005. Well, my wife didn’t get back to her, and I’m sort of glad, since the article was released with the title: DBAs Bar Door Against Big Bad .Net Wolf, and is very misleading. Basically, Lisa tries to state that all .Net programmers are ignorant of T-SQL and will wind up using CLR Stored Procs instead of bothering to learn T-SQL. Yes, there are developers that have never learned SQL (i.e. former Access developers), and when they learned .Net continued to ignore T-SQL. These are the folks Lisa seems to be talking about, but I wouldn’t group them with the rest of the .Net developers. They are the same folks that don’t understand concurrency and try to open one connection and keep it open for the whole life of the application. These are folks that don’t write enterprise ready applications, and border on the fringe of the “hobbyist” developer (I wouldn’t even call them Morts). That being said, yes CLR Stored Procs can be a crutch that developers can use rather then learning how to do it correctly with a language designed around querying and manipulating relational data, T-SQL (what I would call a domain specific language, in this case the domain being querying and manipulating relational data).
Kimberly Tripp has stated (and I agree with her) that the 2 most abused features of SQL Server 2005 will be CLR Stored Procs, and XML data types, and to help developers decide if a CLR Stored Proc is needed,
I have this one rule to follow regarding CLR Stored Procs
If you would not have built it as an extended stored proc in SQL Server 2000, odds are that you don’t need to create a CLR Stored Proc in SQL Server 2005.
MSDN has a very good article that goes into more detail behind the process of determining if you need a CLR Stored Proc, if you need more details. Oh, and if you don’t know what an extended stored proc is, you really don’t need a CLR stored proc.
That being said, I’ve got one killer implementation of a CLR stored proc in the works (and should be ready for the PDC). It’s code name is Ramapo, and uses this open source project as it’s core.
update:
This post seems to have cause a bit of a conversation arounnd CLR Sprocs, which is a good thing. Some are misinterpreting my rule that you should not use CLR Sprocs, which is not the case. The original format of the rule (which I talked about at the April Philly Code Camp) was a little more lengthy, and I trimmed it up to help people to remember it, but I think I lost something important when I restructured it. It was something like:
If you have never had to coded an Extended Stored Proc, then you probably don't need a CLR stored proc. Extended stored procs were a real pain, and if you would have used them, except for things like security, then you may want to take a look at CLR sprocs. Otherwise, play with extended sprocs first, feel the pain, and then you will have a better idea when the use of CLR stored procs is warranted.