[dba-VB] Performance of .Net Code in SQL Server 2005

Hi Shamil

True, and the benchmarks in article shows this too.
But for this case with only about 5000 records speed is not important and then I learn something new. It took me a while to find out that you have to use this syntax to create the procedure:

CREATE PROCEDURE Something
AS
EXTERNAL NAME assembly.[namespace.class].method

This is for SQL Server 2005 and .Net 2.0. Don't know if it has improved in SQL Server 2008.

/gustav


>>> shamil at users.mns.ru 25-01-2008 17:23:10 >>>
Hello Gustav,

I'd use T-SQL CASE and built-in T-SQL functions: REVERSE and CHARINDEX and
SUBSTRING and/or I'd write UDFs using the listed above and other string
functions, which are many in T-SQL...

...I'd use .NET code for something more like business functionality or when
advanced parsing is needed etc. ...

Just my opinion...

--
Shamil
 
-----Original Message-----
From: dba-vb-bounces at databaseadvisors.com 
[mailto:dba-vb-bounces at databaseadvisors.com] On Behalf Of Gustav Brock
Sent: Friday, January 25, 2008 2:02 PM
To: dba-sqlserver at databaseadvisors.com; dba-vb at databaseadvisors.com 
Subject: [dba-VB] Performance of .Net Code in SQL Server 2005

Hi all

I came across this interesting article:

http://www.novicksoftware.com/coding-in-sql/Vol3/cis-v3-N14-performannce-of- 
dot-net-code-sql-server-2005.htm 

But is anyone here running .Net code in SQL Server?

A client has an mdb file with poorly designed tables and sloppy data. The
task is to clean these and transfer them to SQL Server tables with a better
structure.
I'm not so familiar in T-SQL, so I created queries in Access to test and
view the possible output. A typical query contains code like this:

SELECT .. IIf(InStrRev([dessNr],"-")>0 And
IsNumeric(Right([dessNr],1)),Left([dessNr],InStrRev([dessNr],"-")-1),[dessNr
]) AS dessinNoBase

T-SQL knows nothing about InStrRev so I looked for options on how to
translate such functions.

One option is to use .Net code. Speed is not important (small tables).
How do I approach this? Well, the top links at that page link to
presentations on:

  SQLCLR Programming with SQL Server 2005

Still, has anyone been working with this?

/gustav