Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase «««12345»»»

What's in your CLR? Expand / Collapse
Author
Message
Posted Friday, December 11, 2009 8:45 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Thursday, July 17, 2014 9:42 AM
Points: 267, Visits: 685
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
Post #832989
Posted Friday, December 11, 2009 10:18 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 11:53 AM
Points: 2,264, Visits: 1,312
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.


Not all gray hairs are Dinosaurs!
Post #833073
Posted Friday, December 11, 2009 10:43 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Thursday, July 10, 2014 11:20 AM
Points: 571, Visits: 3,504
We use it to send syslog messages instead of database mail.
Post #833087
Posted Friday, December 11, 2009 10:58 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, August 2, 2011 8:13 AM
Points: 19, Visits: 146
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...


Bill Wunder
Post #833096
Posted Friday, December 11, 2009 11:19 AM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, June 20, 2014 2:22 PM
Points: 190, Visits: 436
Custom aggregators...

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




PeteK
I have CDO. It's like OCD but all the letters are in alphabetical order... as they should be.
Post #833107
Posted Friday, December 11, 2009 11:29 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Wednesday, July 9, 2014 3:30 PM
Points: 352, Visits: 1,868
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.





SQL# - http://www.SQLsharp.com/
Post #833117
Posted Friday, December 11, 2009 12:03 PM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Thursday, June 5, 2014 10:54 AM
Points: 9,902, Visits: 9,480
-- 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 databases

I thinks theres a couple more ...


-- RBarryYoung, (302)375-0451 blog: MovingSQL.com, Twitter: @RBarryYoung
Proactive Performance Solutions, Inc.
"Performance is our middle name."
Post #833133
Posted Friday, December 11, 2009 1:37 PM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Tuesday, March 29, 2011 2:59 PM
Points: 473, Visits: 606
1. To send MSMQ messages (originally it was a SQL2000 extended SP written in C)
2. SQL Server could't do it natively.
3. Yes
4. 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.


---------------------------------------------
Nothing is impossible.
It is just a matter of time and money.
Post #833180
Posted Friday, December 11, 2009 6:33 PM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 12:12 AM
Points: 3,433, Visits: 14,422
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.

http://msdn.microsoft.com/en-us/library/ms403279.aspx


Kind regards,
Gift Peddie
Post #833264
Posted Sunday, December 13, 2009 6:33 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, December 14, 2009 5:40 PM
Points: 1, Visits: 9
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,
Post #833573
« Prev Topic | Next Topic »

Add to briefcase «««12345»»»

Permissions Expand / Collapse