CLR vs. SP

[DrSql]

As DBA we have been debating with our developers whether we should be using CLR Stored procedures or SQL Server Stored procedures. Is there any have any experience with it or suggest the Pros and Cons.
Thanks in advance.

Dr.Sql
Good Luck.

 

[gmmastros]

The correct answer is.... Both.

Most of your queries are probably straight forward CRUD type queries. These should be SQL Server stored procedures. For heavy calculations and things that involve loops, you may be better off using SQL CLR procedures.

Bottom line is.... you shouldn't think of this as all or nothing. Use whichever one gives you the best performance.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[mrdenny]

Use the best technique for the job. The basic rule should be, if SQLCLR can do it faster than T/SQL then CLR will be the better option.

Now when I say T/SQL can do it faster, I mean properly written T/SQL not the crap that a 1st year .NET developer writes.

Denny
MVP
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / SQL 2005 BI / SQL 2008 DBA / SQL 2008 DBD / SQL 2008 BI / MWSS 3.0: Configuration / MOSS 2007: Configuration)
MCITP (SQL 2005 DBA / SQL 2008 DBA / SQL 2005 DBD / SQL 2008 DBD / SQL 2005 BI / SQL 2008 BI)

My Blog