CLR vs T-SQL

CLR vs T-SQL

• Nidonocu

  Hi Bhadir,

  You get some insight from the whitepaper that we published on CLR integration---you 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

  
  
  

• Andre de Beer

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

• Al Christoph

  ...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

  
  

• Chris Holt

  Hi there,

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

  Might help you.

  Cheers, -DS

  
  
  

• gazcuex

  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

  
  

• GameOver_1

  hi bahadir,

  i think i discussed those thing in my blogs

  kindly check

  look for the SQL CLR topic

  regards,

  joey

  
  
  
• CLR vs T-SQL