Shamil Salakhetdinov
shamil at users.mns.ru
Fri Jan 25 15:09:30 CST 2008
Hello Gustav, CLR stored procedures can have output parameters (but not return values) of many different types... There are also CLR Scalar-Valued Functions (SVFs), which return a single value. SVFs can return any scalar type, excluding varchar, char, rowversion, text, ntext, image, timestamp, table, or cursor. This information is from the book: "Professional SQL ServerT 2005 CLR Programming with Stored Procedures, Functions, Triggers, Aggregates, and Types" By Derek Comingore and Douglas Hinson which is very good and covers all(?) the aspects on developing and deploying managed SPs, SVFs, triggers, ... for MS SQL 2005... I haven't yet used SQL 2005 CLR programming - I'm waiting for a customer to request for such programming :) - as far as I see from the book I mentioned above there is no any "black magic" there... BTW, this book has very detailed chapter (Chapter 5) comparing CLR and T-SQL performance - I'd dare to give here an short summary of this chapter: << As it relates to a performance comparison between SQL CLR and T-SQL, we have shown in this chapter that: - String parsing and processing perform better with less CPU resources using CLR base classes. - Computation of even simple proportions performs better in SQL CLR. - SQL CLR table-valued functions can stream results faster to client than T-SQL - T-SQL is still the king of data manipulation, extraction, and updating. >> I haven't seen yet/tried MS SQL 2008 - I will probably do that end of this year if not later... -- 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 10:38 PM To: dba-vb at databaseadvisors.com Subject: Re: [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 _______________________________________________ dba-VB mailing list dba-VB at databaseadvisors.com http://databaseadvisors.com/mailman/listinfo/dba-vb http://www.databaseadvisors.com