SQLServerCentral / Editorials / SQLServerCentral.com / What's in your CLR? / Latest Posts

RE: What's in your CLR?

michael bourgon — Mon, 15 Nov 2010 10:30:50 GMT

[quote][b]Tao Klerks (12/11/2009)[/b][hr]Registry Access. Haven't figured out an efficient and sensible way to do it in SQL Server 2005 without using the CLR.[/quote]Powershell for a decent bit of it. Not necessarily actual registry hacking, but there's a lot you can change with PS.

RE: What's in your CLR?

Kyle Freeman — Thu, 09 Sep 2010 16:45:16 GMT

Ive only used it to transpose a dataset, which I later found out could be done with unpivot.

RE: What's in your CLR?

Jeff Moden — Fri, 18 Dec 2009 19:09:27 GMT

Sorry for my apparent absence, but I didn't want to put any kind of a slant on the possible responses.I have to admit a great amount of surprise at the responses. I've seen very little blanket "anything that can't be done in SQL", which usually equates to "don't know how" and I had lot's of pork chops ready for those types of folks :-P. Most of the places where folks have stated they use CLR's seem to be very well thought out, practical, and truly should be done somewhere besides the bowels of T-SQL (heh... although I may have to write an article on how to do very high speed medians in a set based fashion) including Regex, certain string manipulations, hierarchies, communications to Web Servers and other "devices", certain types of mailings, and file handling (my favorite).Personally, I'm like some of the others... there hasn't been much that I haven't been able to do in T-SQL, I frequently get close enough to or even beat CLR speeds to make it not worthwhile maintaining another code base, and the GUI folks normally take care of all the Web Server and "other" device requirements from the app.Anyway, thank you one and all for the time you spent on some really good answers. I also really appreciate those of you that have CLR's running and still warn that they can easily be overused or inappropriately used. I also appreciate those who don't have any active CLR's that say they can see some appropriate uses.What's probably more impressive is that this could have turned out to be an absolute flame war between CLR users and the hard core T-SQL folks. Instead, this thread is filled with some very thoughtful and useful ideas as to what folks are actually using CLR's for or not and why.Very well done, folks, and thank you all again.

RE: What's in your CLR?

timothyawiseman — Fri, 18 Dec 2009 17:15:41 GMT

[quote][b]Jack Corbett (12/11/2009)[/b][hr]The reason I don't use the CLR is simple. I haven't had a task where I found it to be necessary. I don't work on big, high transaction systems where milking the a few milliseconds or even seconds of performance is necessary so the time needed to optimize to that level is better used elsewhere. Before I get flamed for saying that, I do attempt to write the best-performing code I can, but I don't have to seek out faster solutions if the customer (end-user) is satisfied.[/quote]Anecdotally a couple of times I tested CLR vs pure T-sql solutions the pure T-sql solution was faster. I make some very limited use of it, but only in cases where generating the answer in T-sql is very awkward but for one reason or another I want the functionality at the SQL Server level instead of in the application.

RE: What's in your CLR?

LerxtDBA — Mon, 14 Dec 2009 07:17:29 GMT

Done a fair bit of CLR.1) TiffPageCount: We have a java-based tiff viewer to display scanned images in our main web site. For multi-page tiff files it requires a count of the pages as well as the location of the document. Our web team was struggling to get a count and I just made a little 10 line CLR that calls system.imaging to get it. Super easy to code but not the best use of a database server.2) HUGE XML export: We needed to generate a very large XML file for a client - over 200MB. None of the SQL Server tools could do it. VB.net via CLR to the rescue. Personally, I doubt the client ever manages to import the file - but I delivered it.3) US Standard Claim Forms EDI (X12 - 837): This is basically a full-featured app called from SQLCLR udf's. It manages queueing up records to be added to a file, generates the unencrypted file, encrypts the file using a public key stored in a table (gpg), and can send the encrypted file via FTP, email, or simple network storage. It reports on the entire process and is smart enough to tell users when a record doesn't meet the requirements to send. You can buy X12 exporters but they are VERY expensive and still require a lot of work to set up. All told - for maybe $25k worth of my time I built my employer an app that would have cost them at least $250k to buy. Nope, I didn't get a bonus for it. haha4) AP XML Feed Import: The Associated Press has an XML format that is too complex for Microsoft products to read. I wrote a VB.net CLR to parse it.CLR has it's place and I know I've stretched it a little farther than I should. Sometimes what's expedient just takes precedence over what "should" be done.

RE: What's in your CLR?

Steve Jones - SSC Editor — Sun, 13 Dec 2009 20:09:26 GMT

For anyone else confused, the SQL CLR is a programming platform inside SQL Server that allows you to write your own assemblies in any .NET language to call from t-sQL and work with data.[url]http://msdn.microsoft.com/en-us/library/ms189566%28SQL.90%29.aspx[/url]

RE: What's in your CLR?

michael.davies-650746 — Sun, 13 Dec 2009 18:33:06 GMT

I read this every day even though I am not a DBA. It nearly always makes sense to me. However in this case I am at a loss as to what a CLR is. I'm sure it is obvious and I will slap my head when I eventually find out, but if you could get contributors just define the jargon once in the article (since it is used 8 or 9 times) that would make it just that little bit more relevant to people like me.P.S. it is common language runtime for others like myself who don't already know it. (I am not slapping my head because it isn't obvious to me - never heard of it)thanks,

RE: What's in your CLR?

Gift Peddie — Fri, 11 Dec 2009 18:33:23 GMT

I have not used CLR but if I am working with XML I think CLR will be better solution to working with XML because .NET makes working with XML very easy. I also think CLR makes some distributed transaction operations with Oracle redundant, if you are running Oracle 10g and 11g.[url]http://msdn.microsoft.com/en-us/library/ms403279.aspx[/url]

RE: What's in your CLR?

JacekO — Fri, 11 Dec 2009 13:37:44 GMT

1. To send MSMQ messages (originally it was a SQL2000 extended SP written in C)2. SQL Server could't do it natively.3. Yes4. Accessing resources not available directly to SQL Server. The problem with those - in most cases you have to access namespaces not 'secure' form SQL Server point of view.

RE: What's in your CLR?

RBarryYoung — Fri, 11 Dec 2009 12:03:53 GMT

-- An XOR Aggregate function-- Several text parsers-- some CLR functions for pipeline summation-- CLR proc to send message/records from SQL Server Service Broker to Berkley databasesI thinks theres a couple more ...

RE: What's in your CLR?

Solomon Rutzky — Fri, 11 Dec 2009 11:29:50 GMT

I think that the CLR is great for doing things not natively available in T-SQL, such as RegEx, File System, and Internet related functions (just to name a few). Also, sometimes it nice just to be able to encapsulate some algorithms that maybe can be done in straight T-SQL but T-SQL is more for set-based operations than doing some computations or string manipulation.I have been working on a CLR project for 3 years now and it has gotten a much better response than one would expect given some of the negativity towards using CLR at all. The project -- SQL# (SQLsharp) -- is just a library of some common and some less common functions. Some were just interesting to produce even if there is little practical application and some have certainly made my life easier. Unfortunately with over 150 functions I am not sure which ones are used more than others by those who download it, but personally I have used the File System functions to export data, GZip files, copy files between servers, etc. There are plenty of back-end operations that are not set-based and are called by SQL Agent where being able to accomplish more within T-SQL makes us DB folks less reliant upon application developers who might not have time to help us with certain projects. Some will argue that some of these operations should still be done by business logic outside of the DB, but we all have jobs to do and we have to use what means are available to us. For those who are most comfortable with T-SQL, doing many of these functions simply allows people to get their job done in a language that is familiar, can be easily maintained, and doesn't require bringing on additional people (which can mean getting the job done now instead taking an additional 2 months).Also, creating a User-Defined Aggregate to get a Median has helped.Lastly, the most unexpected positive response has been the desire to access Twitter via SQL Server. Originally I incorporated that functionality because it seemed interesting (even if I was unsure as to how useful it was) but the number of downloads I am getting has increased dramatically since adding the Twitter functions.So, I think that CLR functionality has been very helpful for many people, it is just that they are not being very vocal about it.

RE: What's in your CLR?

Peter E. Kierstead — Fri, 11 Dec 2009 11:19:20 GMT

Custom aggregators...bit-wise aggregation; OR, AND, XOR...string aggregation; deduped concatenation, longest string, shortest string, longest left/right most match,...intelligent date/time aggregationXML aggregation...

RE: What's in your CLR?

Bill Wunder — Fri, 11 Dec 2009 10:58:59 GMT

I use CLR to create a node type that models the SMO object model hierarchy. My application has been in use since before the vendor supplied assembly with the hierarchyid, actually since before the SQL 2005 RTM... I probably could have made the hierarchyid type work but with the one I coded I also get a tree representation. The advantages of the node as a CLR type are that I get a single compact, index-able numeric value that is a pkey and can also used to index based only on mid level nodes of the tree - if it helps my queries - yet never have to parse a string, recurse a parent-child hierarchy or mess with constructing/deconstructing a bundle of character columns into the node when I move the hierarchy between the database and the application tree views. I also get the ordering and DML operation advantages of the hiererchyid. My storage efficiency is not as good as the hierarchyid, in part because when I started the project I was stuck with the 8000 byte requirement that has since been improved considerably, but it kept my eye off the storage ball early on and have just not had a reason to revisit yet. I have also used the CLR for proxied IPC communications between database instances to mange SQLTrace rather than having to resort to the command-line, OLEDB or IS. Makes it easy to tell if something actually worked on the other end or if not, what the error was when trying to manage many instances with out rolling a bunch of cookie cutter stored procedures out to every target and then trying to keep them in sync. In this usage only have to enable the CLR on the instance that the DBAs use to manage the environment. Thinking about developers using the CLR too much will cost me the other two hours of sleep so I try not to go there...

RE: What's in your CLR?

j.a.c — Fri, 11 Dec 2009 10:43:10 GMT

We use it to send syslog messages instead of database mail.

RE: What's in your CLR?

Miles Neale — Fri, 11 Dec 2009 10:18:22 GMT

1. have used CLR once for a basic function I wanted on the database side. 2.Why I did it, probably more like a test drive than anything else.3. It was fine.4. Have been looking at it as a customization for running reports based on certain conditions that an update or insert may have caused, may use it for customized logging against an asycn web service, in one area I may use it to cause a copy of an image I am storing to be archived on Sharepoint. These are the type of things I see as advantageous but have not had time to research them to see how far I can get with CLR.

RE: What's in your CLR?

r5d4 — Fri, 11 Dec 2009 08:45:04 GMT

I've successfully used VB.NET CLR functions to replace TSQL Scalar functions.2 instances where it works well are >1) Conversion of String IP Address data to numeric form (and back)2) Extraction of Search Terms from Search Engine Http Referrer Urls.I found the CLR string manipulation efforts for the latter outperformed the TSQL function 10 fold.r

RE: What's in your CLR?

Joshua M Perry — Fri, 11 Dec 2009 08:43:49 GMT

I used it to replace xp_getfiledetails. This allowed several developers relying on the stored procedure to just do a find and replace on the stored procedure name in their code to be able to move from 2000 to 2005.[code="other"]using System.Data.SqlClient;using System.Data.SqlTypes;using Microsoft.SqlServer.Server;using System.IO;namespace Enterprise.SqlServer.Server{ public partial class GetFileDetails { [Microsoft.SqlServer.Server.SqlProcedure] public static void csp_getfiledetails(string filePath) { try { FileInfo fileProperties = new FileInfo(filePath); SqlMetaData colAlternateName = new SqlMetaData("Alternate Name", SqlDbType.NVarChar, 4000); SqlMetaData colSize = new SqlMetaData("Size", SqlDbType.BigInt); SqlMetaData colCreationDate = new SqlMetaData("Creation Date", SqlDbType.NChar, 8); SqlMetaData colCreationTime = new SqlMetaData("Creation Time", SqlDbType.NChar, 6); SqlMetaData colLastWrittenDate = new SqlMetaData("Last Written Date", SqlDbType.NChar, 8); SqlMetaData colLastWrittenTime = new SqlMetaData("Last Written Time", SqlDbType.NChar, 6); SqlMetaData colLastAccessedDate = new SqlMetaData("Last Accessed Date", SqlDbType.NChar, 8); SqlMetaData colLastAccessedTime = new SqlMetaData("Last Accessed Time", SqlDbType.NChar, 6); SqlMetaData colAttributes = new SqlMetaData("Attributes", SqlDbType.Int); SqlDataRecord record = new SqlDataRecord(new SqlMetaData[] { colAlternateName, colSize, colCreationDate, colCreationTime, colLastWrittenDate, colLastWrittenTime, colLastAccessedDate, colLastAccessedTime, colAttributes}); record.SetInt64(1, fileProperties.Length); record.SetString(2, fileProperties.CreationTime.ToString("yyyyMMdd")); record.SetString(3, fileProperties.CreationTime.ToString("HHmmss")); record.SetString(4, fileProperties.LastWriteTime.ToString("yyyyMMdd")); record.SetString(5, fileProperties.LastWriteTime.ToString("HHmmss")); record.SetString(6, fileProperties.LastAccessTime.ToString("yyyyMMdd")); record.SetString(7, fileProperties.LastAccessTime.ToString("HHmmss")); char[] splitter = { ',' }; string[] attributes = fileProperties.Attributes.ToString().Split(splitter); int attributesInt = 0; foreach (string attributesString in attributes) { FileAttributes fileAttributes = (FileAttributes)Enum.Parse(typeof(FileAttributes), attributesString); attributesint += (int)fileAttributes; } record.SetInt32(8, attributesInt); record.SetInt32(8, (int)fileProperties.Attributes); SqlContext.Pipe.Send(record); } catch (Exception myexception) { throw (myexception); } } };}[/code]

RE: What's in your CLR?

alastors — Fri, 11 Dec 2009 08:36:43 GMT

1. What's in YOUR CLR?A UDF that sends a UDP Packet to a phone (displayed by my J2me App), called by a normal SQL insert Trigger to take advantage of set based logic.2. If you have written CLR's, why did you write them (or it)?AFAIK you can't send UDP packets from T-SQLit was more performant as a CLR_UDF + SQL_Trigger than polling, plus I dont like polling.3. Looking back at it, was it an appropriate thing to do?Absolutly, received good comments about the performance from the client, made me look good to the boss4. what would you consider a CLR to be appropriate for?The problem about repacing multipe spaces with just one space that was mailed recently in here, didn't like the solucion using set based logic, because it reads the string more than one time.

RE: What's in your CLR?

DannyS — Fri, 11 Dec 2009 08:20:09 GMT

Newb here, but saw CLR poll.I've used CLR a bit for a database backed website. Used lots of simple function usually for text processing for example,a function to determine if user input is a 5 digit number (zip code) or a valid email address (one line of regex). these functions are used to prevent sql injection.Also, custom output of results. We produce files in a number of formats and have found CLR convienient in meeting clients format requirements.Also Comm functions FTP, Email and file operations DoesFileExist() DeleteFile() CompressFile()

RE: What's in your CLR?

Jason Miller-476791 — Fri, 11 Dec 2009 08:00:52 GMT

We have used 1 CLR total. It simply looks up user data in our AD for some custom permission stuff.That being said, I'm moving to a new home/project/environment. It may very well require the use of CLR. This is an analytical environment that currently sits in MySQL, but there is a desire to investigate SQL2008 for performance and scalability... SQL2005 wasn't an option because time didn't resolve finely enough. It is the largest db I've ever encountered.This is going to be very interesting.

RE: What's in your CLR?

GSquared — Fri, 11 Dec 2009 07:39:09 GMT

I've got one CLR procedure in use, which checks drive space available on a network share before running backups. Pretty much the same functionality as xp_fixeddrives, but works on network UNC paths.In a prior job, there was a whole set of procedures that accessed an external DLL for mailing address validation and mail list sorting. They were written using the OLE automation extended procs, but would have been much more efficient if converted to CLR. If I still worked there, I'd have converted them by now.I've got another that I'm working on where I would normally use SSIS, but the manager here doesn't like SSIS. It will access web services, so I'll do that part in CLR.

RE: What's in your CLR?

kramaswamy — Fri, 11 Dec 2009 07:35:23 GMT

The stuff that I've used CLRs for are things that, as far as I know, can't be done using the native SQL environment.In particular, at times I require code which sends a HTTP web request and receives a response. This task needs to be executed on a periodic basis - once per hour to be exact.In order to accomplish this task, I've found only three solutions. One is to have the web request source executing the task on the periodic basis, and updating the database. This is not an option, since the web request is from a third-party, and I can't easily have that third party modify their application to communicate with mine. Another option is to have a Windows scheduled task which executes the web request and then updates the database. The final option was to have a database job which would execute on a periodic basis. This one proved easiest to manage, and all it required was for me to write a .NET CLR which would execute a HTTPRequest and return back the HTTPResponse string.The other instance that I've used CLRs is when I needed to move files between servers. I couldn't find a way to do this through the native SQL interface, so I wrote a CLR which would handle moving the files between servers. I have found it possible to do it in native SQL using the xp_cmdshell extended stored procedure, but the limitations for that are that it becomes *very* tricky to handle mapping network drives and giving permissions to the users such that the files can be copied between servers.

RE: What's in your CLR?

StephenRay — Fri, 11 Dec 2009 07:30:38 GMT

I've written one clr. It was a work around for being able to call an application on a backend AS400 without having access to Microsoft's OLEDB provider for DB2. That provider is only available with the Enterprise Version of SQL Server and since SQL Server is secondary in our environment, Enterprise was not an option. My clr function uses ODBC to connect to the 400 and execute a single program. The biggest pain of the whole thing was getting the proper permissions set for the stored procedure to actually run. Thank goodness for all the blogs/documentation/help on here and Microsoft to try and solve the errors I received on every step of getting it to work!

RE: What's in your CLR?

Ian Massi — Fri, 11 Dec 2009 07:22:13 GMT

1. Have not written CLR functions yet. This is because I haven't encountered a situation where T-SQL couldn't cut it so I haven't looked into them thus far.2. N/A3. N/A4. If I had some kind of really intricate string manipulation or needed something in the .NET that I couldn't figure out how to do in T-SQL I'd look into it. So far I haven't come across anything like that.

RE: What's in your CLR?

ross.cecil — Fri, 11 Dec 2009 07:19:10 GMT

I have used SQL CLR code in a specific case after a lot of testing of alternatives coded in VB6/ADO, VB.NET/ADO.NET, and TSQL. The objective in each case was to return a 1000 (or so) row recordset where the contents of each record were determined by a metadata table that specified the table, view, utf, or sproc and the column of the data. The metadata table is dynamic and can change frequently.As each solution was developed it was optimized for performance within its own technology. All solutions returned the same recordset. The CLR coded solution was [u]roughly[/u] 1000 times faster than the worst case (VB6/ADO) and 100 times faster than TSQL. Each solution was tested over 100 times on each of three databases and the results combined in order to determine the results.I put a lot of work into the development, optimization, and testing of the solutions as it was very important to providing good performance to my customers. The CLR solution is used tens of thousands of times a day by hundreds of users.Like any tool, SLQ CLR solutions can provide the best solution in the appropriate situation. They can be misused as can any tool.Ross

RE: What's in your CLR?

Mark-101232 — Fri, 11 Dec 2009 06:49:14 GMT

Just some string manipulation functions, such as Levenshtein and InitCap. All of which could be coded in TSQL but are easy to do in C# (InitCap is a one-liner).

RE: What's in your CLR?

Lowell — Fri, 11 Dec 2009 06:43:50 GMT

converting an rtf string to raw text. AFAIK, you can't uses a System.Windows.Forms.RichTextBox in a CLR, but you can call a web service that uses the System.Windows.Forms.RichTextBox;two steps to do the same process, CLR>>WebService, but it gets the job done.

RE: What's in your CLR?

jay-h — Fri, 11 Dec 2009 06:14:51 GMT

I'm posting as someone who in previous life wrote a lot of C++Obviously any set based operation would be better in SQL.Other types of business logic, or a complex custom conversion function would probably be better in CLR (Some SQL string functions are quite slow, and could be better managed directly in memory by a procedural language with pointers).And there is nothing in sql that is comparable to the heirarchical inheritance structure of C++ (or even C#, which is more limited).

RE: What's in your CLR?

Andy Leonard — Fri, 11 Dec 2009 06:09:49 GMT

I've used SqlCLR once in Production. I ETL'd some data from a DB2 database on iSeries into SQL Server 2005. The data included product descriptions, and for some reason the trademark symbol (™) refused to display as a printable character. I tried every T-SQL trick I could think of to search and replace that character, and for whatever reason, I could not figure out how to find the unprintable character in the column.I wrote a search and replace function that accepted a database name, schema.table name, column name, search string, and replace string. It worked very well.:{> Andy

RE: What's in your CLR?

Jack Corbett — Fri, 11 Dec 2009 06:06:34 GMT

I have not yet used the CLR. As Grant mentioned I would bet more people on 2008 are if you include the Spatial and Hierarchy data types which are CLR types.The reason I don't use the CLR is simple. I haven't had a task where I found it to be necessary. I don't work on big, high transaction systems where milking the a few milliseconds or even seconds of performance is necessary so the time needed to optimize to that level is better used elsewhere. Before I get flamed for saying that, I do attempt to write the best-performing code I can, but I don't have to seek out faster solutions if the customer (end-user) is satisfied.

RE: What's in your CLR?

Tao Klerks — Fri, 11 Dec 2009 06:04:23 GMT

Registry Access. Haven't figured out an efficient and sensible way to do it in SQL Server 2005 without using the CLR.

RE: What's in your CLR?

Grant Fritchey — Fri, 11 Dec 2009 05:51:14 GMT

I have yet to find a need for writing a CLR package of any kind, so I'm not using CLR directly.However, I do use CLR quite a lot. We have more and more apps taking advantage of the spatial data type.This is a CLR data type and the functionality it provides is through CLR. I know that Adam Machanic has found some places where CLR is doing amazing stuff, but you'd need him to detail it where he can.

RE: What's in your CLR?

Todd Townley — Fri, 11 Dec 2009 04:18:56 GMT

A developer once "proved" to me how much faster CLR was. He developed a simple TSQL function and a CLR function that did equivalent things. His test script showed the CLR function to be roughly 20 times faster than the TSQL function.On closer examination of what was wrapped around the functions in the test scripts, there were further efficiencies to be gained. For example, the CLR function was only being called "when needed", but the TSQL function was called for every record.Once the maximum efficiencies were included, the TSQL sample ran slightly faster than the CLR sample.We do some specialty stuff which requires developing 20,000-30,000 lines of TSQL code in our test environment, moving that to a customer's test environment, then ultimately moving that to the customer's live environment and running it one time (data conversion). Therefore the code needs to be very portable.Collecting the full set of stored procedures into one text file (we have a script that does this) has proven to be a very effective method for portability. (Server names can also be changes by a simple search/replace across the text file.) CLR adds another level that would also have to be ported.In short, our developers can use CLR if desired and if effective for what they need to do. Because they also deliver the solution, they also have to work with the portability aspects. To date, only two of over 100 projects has used CLR.

RE: What's in your CLR?

shane.davies — Fri, 11 Dec 2009 03:14:31 GMT

We are looking at the Logi reporting tool. We want to report on data in an old 3GL system, so we use a CLR to get the data into a SQLServer view via a web service. So far it is the only way I have found of using a WS in SQL Server.

RE: What's in your CLR?

Dave Ballantyne — Fri, 11 Dec 2009 02:04:25 GMT

The only CLR routine that I have written , that wasnt "hello world", is to call and mail a SSRS report.[url]http://sqlblogcasts.com/blogs/sqlandthelike/archive/2009/08/27/direct-emailing-of-ssrs-reports-via-sqlclr.aspx[/url]Just like cursors and udfs , from what i see anecdotally, CLR routines are again (over)used by non-set based mindset developers. They do have a place, interestingly in the latest [url=http://ask.sqlservercentral.com/questions/2295/phil-factor-speed-phreak-challenge-3]Phil Factor Speed Phreak Challenge[/url] a CLR routine is presently top of the leader board. I suspect though, if and when we get a "ROLLING_BALANCE()" function the need will be even less.

What's in your CLR?

Jeff Moden — Thu, 10 Dec 2009 20:37:55 GMT

Comments posted to this topic are about the item [B]What's in your CLR?[/B]