CLR vs T-SQL

Hi,

I bought Pro ADO.NET2.0 but could not find a clear description of the optimum use-case for CLR and T-SQL Stored Procedures in general. (although the book is great)

It seems for complex or recursive tasks CLR SP is useful. But would you recommend using CLR SP for basic INSERT, UPDATE, DELETE, SELECT queries?

Thanks,
Bahadir

...very common question these days.

Simple answer lies in the fact that let the each person(in this case CLR/T-SQL engines) do what it knows best!

Say for e.g. if you have lot many string manipulation activities to be accomplished. Now of course, in such a case writing a T-SQL UDF or a SP won't help much in performance gains. On all grounds for such activities, the CLR would be much stronger than the T-SQL engine.

Another example -- consider a scenario where a T-SQL cursor is pretty much evident in your SP logic. Try considering writing a CLR SP instead -- looping etc would be very much optimized when in use with CLR rather than T-SQL.

Does than mean T-SQL is always the 2nd choice ? NO Not at all !!

Please recall that T-SQL engine is primarily a SET basaed operational engine. Therefore as mentioened above, CLR was the best choice for row based operations, while consider fetching multiple rows in a set based patter using various joins or order by ann group by options -- of course, T-SQL is the best choice. Similarly, update of columns hitting several rows -- T-SQL better than CLR SP.

I hope I am able to answer your doubt.

Regd. the books you were ref. to -- try reading Pro SQL Server 2005 (A Press)

and SQL Server Magazine (www.sqlmag.com)

cheers,

Ujwal

|||Thanks, this was the kind of rule-of-thumb answer I was expecting.

Bahadir|||

well, I don't know how much are you convinced with the answer or do you still have doubts.

anycase, I would like to pass on a pointer to one more thread which I happened to come across very much on similar topic on this very forum try browsing the same as well -- Where to use SQLCLR ? Data access is not recommended?

|||

hi bahadir,

i think i discussed those thing in my blogs

kindly check

look for the SQL CLR topic

regards,

joey

|||

Hi Bhadir,

You get some insight from the whitepaper that we published on CLR integrationyou can find it here.

The basic guidelines are that for procedural, computational work, the CLR will outperform T-SQL. For data access, or things that can be written declaratively, or that perform data acces, T-SQL will win. Obviously there's a lot of grey area in this answer, but there's a lot of grey area in practice as well.

Cheers,

-Isaac

|||

Hi there,

check this post http://www.yukonizer.com/dasblogce/PermaLink,guid,59a1fbee-1df3-4f9c-be64-de1c778c198e.aspx

Might help you.

Cheers, -DS