what are the possible problems with CLR integration in Sqlserver

Question

I read an article about using CLR integration in sqlserver and was wondering what some of the potential problems might be, if any. My thought was using it for validating potentiality bad data in a legacy Database. an example is a persons name in the phone number column.

Edit: I don't think there are any problems, but it's not something I see a lot of discussion about, and want to make sure I'm not opening a can worms that's going to cause problems later on. The reason I ask is my DBA looked at me like I was crazy when I asked about it.

Answer 1

CLR integration in SQL Server per se is not unstable. As evidence I'll point you to the fact that in SQL Server 2008 a bunch of system data types were implemented as CLR data types, like the new geography and geometry types. So the CLR was deemed safe enough to allow new core functionality to be based on it.

That being said, the CLR brings into the SQL programming a whole new arsenal to shoot yourself in the foot. You can start threads, block on IPC communication (events, mutexes, semaphores), connect externally and wait for I/O, read/write in memory, call various Win32 APISs and in general behave recklessly and wreak havoc. The old T-SQL programming required a much bigger hacking talent to achieve the same.

Are you looking at implementing a new datatype that exposes nice, constrained, behavior like, say, regex validation of a field? Go ahead. Are you looking at making Web service requests from inside SQL hosted CLR? You have it coming and you'll deserve all you'll get!

The rule of thumb is if your assembly will load and validate without trustworthy requirements (no EXTERNAL_ACCESS, no UNSAFE) then you should be OK. Of course, you can still write while(1) {;} loops in SAFE assemblies, but then so could a T-SQL stored proc...