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

Hi Shamil

Oh, and some severe limitations too. This method returns an SqlBoolean:

CREATE PROCEDURE failed because a CLR Procedure may only be defined on CLR methods that return either SqlInt32, System.Int32, void.

So ...

/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