>> CLR integration was over-sold as an alternative to TSQL rather than a replacement for Extended Stored procedures

Well said.  Absolutely true.  I never had a need to write my own Extended Stored proc, nor have I done anything with the CLR integration.

That's probably not going to win me any prizes.

I had considered it to replicate the functionality of a couple of functions in Excel.  This was over a year ago, but I believe they were the FORECAST and LINEST functions.  Lacking the time to delve into how to go about creating CLR functions (and lacking VS Pro), I decided to build a solution using stored procedures.  My client wanted the values stored on the records of his table anyway, so they were calculated in a batch and the records updated accordingly.

I also considered CLR as a possibility to perform an interesting calculation to determine how many agents in our call center were required to keep wait times low.  Unfortunately, our migration to SQL Server 2005 has been delayed.  I ended up building that calcuation in Excel using the Poisson function.  This was about a year ago too.

I still haven't had time to learn how to implement CLR functions and look forward to one of your great workbenches should you put one together on the subject.

To date, the only use I have personally come up with is access to the mentioned RegEx  methods. I have seen demos of UDTs implemented as classes (a currency type for foreign exchange data), but have not implemented personally.

So far , i've never needed any "extra kick" to deal with the bussiness rules in SQL server. I recall that i did some dll for Interbase , a long time ago , just to emulate SQL Server ISNULL , to port some SQL code verbatim to a linux host , and one string conversion function  , that's all.

I've still to try doing some tinkering with the CLR , just for the hell of it, but , seriusly , we are talking about SQL Server. If we have to go all the way to do some programming in .NET to provide an answer , then we don't undestand the question.

Dabases are about storing and retrieving sets of data. To go up close and personal with data and do something ugly with the results in .NET is something better left to the programmers. I think all the hardcore DBAs have shudders thinking the big bad ugly programmers are going to do eldrich things to the way SQLServer works.

I can conceive that being capable of doing something like

   Select Sigma2(price) from CurrentStock

may appeal some people , but i have serious reservations about doing such things so near the core of the database engine.

CLR integration looks like a viable replacement to SQL Server 2000 jobs that utilize the xp_commandshell.  CLR integrations that execute an NT bat file can be integrated into a stored procedure and scheduled via the SQL Server scheduler.  Many of our database maintenance jobs are in NT bat files.  What we are still trying to figure out is the best way to secure the assemblies so that we can control what stored procedures utilize the CLR integrations.  

I have one useful example: We use a CLR-based function to parse datetime fields into TimeZone localized datetimes for report presentation. It is a simple CLR assembly that accesses the Windows API to do the time zone adjustment. It's unsafe, so we have to sign it, but it is handy to let Windows manage the locale data for us instead of manually keeping up a table or a complex stored procedure.

To me it looks more presentation stuff that real DB proper work , but if it works for you , then all is well.

Incidentally , i came across some stuff that would be well suited for CLR work. An estimate of the current network load during query execution. I started to wonder about perf counters and how in hell would i integrate that in the query result , and this topic came to mind. I'll taste the idea while drinking a beer tonigth :)

It is interesting that you mention regex as a good reason for implementing CLR solutions.  I agree that if you want regex, that is indeed a good one.  I am a software developer, and I mostly use a language that does not natively have a regex.  Regex statements are generally against our coding standards: they stack multiple data manipulations on a single code line, and they hide simple transformations behind an abstruse command.  I will go so far as to say most of the regex uses do not even work as intended (for example, enter the email address a@a into your favorite email editor).  In any case, I suspect that SQL experts naturally tend toward regex-type solutions, but there are many developers who do not, and I suspect that never the twain shall meet.

In over 10 years of working with previous version of SQL Server, I had cause to write only 1 extended stored procedure.  I am expecting the same time frame for the need to write a CLR solution.  I have installed a RegEx solution just as a test of the process.  However, the need to use even these is a rare event.  I say leave the CLR programming to the .NET programmers.  I tend to agree with MVV that if we find ourselves in need of using the CLR, than we either don't understand the problem or we are approaching the solution from the wrong direction.

The most frustrating limitation of TSQL, for me personally, is the inability to pass table names into a function, and use them in dynamic SQL.

How about addressing this post in the Forums:

http://www.simple-talk.com/community/forums/thread/53180.aspx

with a CLR solution?

It would be a great primer for me to utilizing CLR code in SQL server as I tend to stick with pure TSQL in SQL Server SPs and procedures, but am primarily a C# developer.

We use CLR stored procedures to allow customers to integrate directly with our application's Web Service Interface (WSI). We provide a signed assembly with full source and instructions for setting it up in their database so that they have minimal work to get up an running. Web services are by far the best method of integration as they are compatible with may different programing languages (everything from javascript to CLR stored procedures) and still filter through our business logic layer. The assembly can be used to call the web service methods using a secure HTTPS connection and convert the XML SOAP messages into TSQL record sets. Below is an example from our WSI user guide (more info at http://demo.oqanalyst.com/OQAHS/Files/WSI/OQ_Analyst_Raw_WSI_Overview_And_Examples_2008MAY08.pdf ):

Microsoft SQL 2005 or SQL Express CLR Stored Procedure
If you are using a Microsoft SQL 2005 or SQL Express database you can use a special Common Language Runtime (CLR) stored procedure to access the WSI directly from the database. We have created a wrapper class for all of the WSI methods that can be imported as a Stored Procedure into an existing database. When you call the stored procedure, the CLR code will convert the request into a SOAP message, call the method on the OQ Analyst server, parse the results into an SQL record set, and return the data to your query. This allows you to quickly integrate with the WSI using standard MS SQL techniques such as triggers, cursors and constraints. Below is an example of how to run a query once the assembly has been loaded into your database.

-- Create temporary values and tables
Declare @Message varchar(200)
CREATE TABLE #Clients (PersonID Int, MedRecordNum VarChar(20), FirstName VarChar(20), MiddleName VarChar(20), LastName VarChar(20), BirthDate SmallDateTime, Diagnosis VarChar(25), Gender VarChar(10), ClinicianPersonID Int, ClinicianFirstName VarChar(20), ClinicianLastName VarChar(20), Clinic VarChar(25), Instrument VarChar(20))

--Load clienINSERT #Clients
t records into temp table
EXEC WSI_GetAllClients 'admin', 'password', @Message OUTPUT

--Print message
Print @Message

--Display Results
Select * from #Clients

--Clear temp table
Drop Table #Clients

I come from an Oracle background and one thing I really missed when moving to SQL Server was how PL/SQL has Autonomous Transactions (http://www.oracle-base.com/articles/misc/AutonomousTransactions.php) and TSQL doesn't.  So I think one really nice use of CLR stored procedures is to have the ability to have Autonomous Transactions within your TSQL stored procedure processes when the volume of data is just too large to process in one transaction.

One simple example use of this is in error logging.  You can create a CLR proc that creates a new database connection and commits an error message to an error log table even when the stored procedure rolls back everything else. I know for that you can also cheat and use a table variable as it doesn't take part in the transaction, but the CLR lets you do this in a nice clean consistent manner.

I use a single CLR function, which oddly enough is in a utility assembly I wrote before we went to SQL Server 2005 but which works well when I used the method as a function in SQL Server 2005 as well (wow, actual code reuse!) It was written to do "smart casing" of personal and city names and street addresses. By smart casing I mean:

1) Capitalize the first character of every "word" and lowercase everything else, except for the following.

2) Handle name suffixes like II and III as well as street directions like NW and SE and rural routes like Route AA correctly.

3) Handle "Mc" prefixes correctly.

4) A few other cleanup type operations around rationalizing punctuation.

It was originally written to help with importing data from our IBM mainframe system (typical mainframe data in all caps) into Microsoft CRM. Since then I've used it in SQL Server 2005 for various mainframe-to-local-data mappings and cleanup.

I typically try and do as much in T-SQL as possible, but as a C#/VB.NET programmer I would still consider using CLR functions when applicable. But other than the above (where I already had the code laying around) I haven't found anything applicable (yet).

I would like to see CLR implementations of the new(ish) ansi sql aggregate/set functions that go with the new(ish) ansi sql boolean data type.  The functions are EVERY, ANY and ALL.  I am sure you could produce equivalent implementations based around sql server's bit data type.   The logic should be relatively straightforward, so we can see clearly what's involved in implementing the CLR side of things.  Thanks.

Hi Tony,

  I used CLR integration once in production (so far). It was for a search and replace function. I could not get T-SQL to recognize a non-printable character so I created a CLR procedure to do the job. It worked really well and solved this particular issue.

:{> Andy

1. CLR integration was a stupid idea.
2. If you cannot add together the results of MAX or MIN or whatever (aka aggregate), then that is not a reflection of T-SQL, it is a limitation of your original thinking; aka, creativity, and I suggest not bragging about your limitations so openly.

The only limitation I have found in T-SQL is the ability to directly minipulate the file system; but I have other built-in SQL Server capabilities for that. CLR was a fix for a non existing problem.

G'day Tony - I've used one CLR function to calculate Levenshtein distance (see http://www.merriampark.com/ld.htm). The implementation in T-SQL was far from efficient (at http://www.merriampark.com/ldtsql.htm), it was a no-brainer to implement it with the CLR.

Cheers, Thomas

Used it to make reporting services call a proc and have that proc generate a report and create a file for export. The user couldn't be arsed to use the save as function - so we empowered user lazyness with the latest technology and didnt' use the command shell function.

We're currently using it to implement some pretty hairy calculations that need to be applied against a potentially large set of data across a potentially large spread of time (think data warehouse). The data could be read and sent to the web server, processed in .NET, and the results (about as much data as was originally read) sent back to the database server, but the decision was to do it all on one box. (Relatively low volume work, or so they say--if wildly popular, we'll have scaling problems.) FYI, I'm a DBA/database developer, and this work was done by the application group.

There's talk of doing funky aggregations some day, but we'd be looking at Analysis Services first to solve that problem.

<second attempt to submit, hopefully this only appears once...>

G'day Tony - you mentioned that I'd won the $50 Amazon voucher. You can contact me at my new blog at TheRuntime.com.

Cheers!

Hi Tony,

I was contemplating of using a CLR stored proc for use in sql reporting.
Initially all the hairy calculations were put in the reports. After creating multiple reports I am sick of fixing calculations in multiple places.

I tried regular sql stored proc + UDF. Got to complex.
Tried assembly for use in sql reporting. also got to complex.

Would a CLR stored proc suit my needs?