Are CLR stored procedures preferred over TSQL stored procedures in SQL 2005+ ?

Question

My current view is no, prefer Transact SQL stored procedures because they are a lighter weight and (possibly) higher performing option, while CLR procedures allow developers to get up to all sorts of mischief.

However recently I have needed to debug some very poorly written TSQL stored procs. As usual I found many of the problems due to the original developer developer having no real TSQL experience, they were ASP.NET / C# focused.

So, using CLR procedures would firstly provide a much more familiar toolset to this type of developer, and secondly, the debugging and testing facilities are more powerful (ie Visual Studio instead of SQL Management Studio).

I'd be very interested in hearing your experience as it's seems it is not a simple choice.

Answer 1

In general you use the CLR if you have something that doesn't need to interface with the database much. So let's say you are parsing, or decoding a value. This is easier to do in the CLR and then return the value.

Trying to do a compelx query in the CLR is just not the way to go.

BTW this didn't change in 2008 either.

Answer 2

CLR stored procedures are not meant to replace set-based queries. If you need to query the database, you are still going to need to put SQL into your CLR code, just as if it was embedded in regular code. This would be a waste of effort.

CLR stored procedures are for two main things: 1) interaction with the OS, such as reading from a file or dropping a message in MSMQ, and 2) performing complex calculations, especially when you already have the code written in a .NET language to do the calculation.

Answer 3

There are places for both well-written, well-thought-out T-SQL and CLR. If some function is not called frequently and if it required extended procedures in SQL Server 2000, CLR may be an option. Also running things like calculation right next to the data may be appealing. But solving bad programmers by throwing in new technology sounds like a bad idea.

Answer 4

It always comes down to the right tool for the job, so it really depends on what you are trying to accomplish.

However, as a general rule, you're right that CLR procs have a greater overhead and will never perform on set operations like T-SQL. My guideline is do it all in T-SQL unless what you need becomes overly complicated in T-SQL. Then, try harder to get the T-SQL approach to work. :-)

CLR procs are great and do have their place, but their use should be the exception, not the rule.

Answer 5

Hosting the CLR within SQL Server is meant to give database developers more flexible options in how they sought to accomplish tasks. Like others have mentioned, SQL is great for operations and modifications on sets of data. Anybody who has done extensive large application development with complex business/domain rules would likely tell you - trying to enforce some of these rules using pure SQL (some times into a single macro query) can get truly nightmarish.

There are just certain tasks that are better handled in a procedural or OO fashion. By having the choice of using .NET code to break down the sequence of logic, query operations can get easier to read and debug. Having used CLR stored procs I can tell you stepping through with the debugger really makes it easier to follow through with what is happening at the database level.

Just one example, we frequently use CLR stored procs here as a "gateway" for dynamic search queries. Say a search request that can have up to 30 different search parameters. Users obviously don't use all 30 of them, so the data structure passed in will have 30 parameters but mostly DBNULL. The client side has no option to generate a dynamic statement, for obvious security reasons. The resulting dynamic statement is generated internally without fear of external "extras".

Answer 6

The SQL Server Books Online's page on the subject lists these benefits:

• A better programming model. The .NET Framework languages are in many respects richer than Transact-SQL, offering constructs and capabilities previously not available to SQL Server developers. Developers may also leverage the power of the .NET Framework Library, which provides an extensive set of classes that can be used to quickly and efficiently solve programming problems.

• Improved safety and security. Managed code runs in a common language run-time environment, hosted by the Database Engine. SQL Server leverages this to provide a safer and more secure alternative to the extended stored procedures available in earlier versions of SQL Server.

• Ability to define data types and aggregate functions. User defined types and user defined aggregates are two new managed database objects which expand the storage and querying capabilities of SQL Server.

• Streamlined development through a standardized environment. Database development is integrated into future releases of the Microsoft Visual Studio .NET development environment. Developers use the same tools for developing and debugging database objects and scripts as they use to write middle-tier or client-tier .NET Framework components and services.

• Potential for improved performance and scalability. In many situations, the .NET Framework language compilation and execution models deliver improved performance over Transact-SQL.

Answer 7

I believe those two aren't equivalent... fit to square off against each other.
CLR Integration is supposed to phase out "extended stored procedures" of yore. We have some of these in our workplace... essentially blocks of processing/logic over SQL data that was too hard/impossible to do via conventional DB Stored procedures/T SQL. So they wrote it up as extended stored procedures in C++ DLLs that can be invoked similarly. Now they have been phased out and CLR integration is the replacement

• DB Stored procedures: if it can be done in T SQL Stored procs, do it.
• CLR Stored procedures: if the logic is too complex or tedious to do via T SQL... if its something that will take fewer lines of CLR code to tackle it (string manipulation, complex/custom sorting or filtering, etc.) use this approach.

Answer 8

Aside from the file system access (where CLR procs has a very pronounced advantage) I would use T-SQL procs. If you have especially complex calculations you could possibly put that piece into a CLR function and call this from within your proc (udf's are where I've found the CLR integration really shines). Then you get the benefits of the CLR integration for that particular part of your task but keep as much of your stored proc logic in the DB as you can.

Answer 9

Given what you said, I would rather you get the deveopers properly trained in t-SQl and databases in general than allow them to create posssibly much more damage to performance by allowing them to do t-sql tasks in CLRs. Developers who don't understand databases use that as an excuse to avoid doing things the way that is best for database performance because they want to take what they see as the easier route.