Playing around with SQLCLR

I had a lull in my assignments yesterday, which gave me a great opportunity to familiarize myself with SQLCLR (creating stored procedures, functions, triggers, and other SQL Server objects using .NET).

From the outside, the concept seems very cool (i.e., you can write everything in .NET code). But, a closer look shows that this is more similar to being a layer that just happens to run on the database to augment native database code instead of a full-fledged replacement for the database code.

Creating a CLR Stored Procedure is definitely a trick that you want to save for times when T-SQL just won't work, or would be overly complex. SQLCLR probably would not be appropriate for your everyday CRUD (Create, Read, Update, Delete) stored procs which are easily created using traditional methods. But, if you have some semi-complex processing, for example, where you need to do some looping (i.e., a for-loop or a while-loop), then a CLR Stored Procedure would definitely be appropriate.

Think about this:

The .NET code does not have native access to the database, like T-SQL does. As a result, the .NET code must still use ADO.NET to execute T-SQL in order to access data. So, since you never actually get away from T-SQL, you should probably plan to still create T-SQL stored procedures to access from your CLR stored procedures whenever possible (i.e., CRUD procs that your .NET code requires instead of building SQL in strings inside your code).

I also discovered a bug yesterday while trying to access CLR stored procedures from OSASPADO ("oh-SAS-pah-doh", or Old-School ASP/ADO), which was confirmed by Bob Beauchemin.

Don't misinterpret this post as thinking that SQLCLR is lackluster. It's an awesome addition to the SQL Server platform. But, it's very important to understand its limitations and design your systems around them.