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 Sunday, December 13, 2009 8:09 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Saturday, December 20, 2014 3:11 PM
Points: 31,368, Visits: 15,837
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.

http://msdn.microsoft.com/en-us/library/ms189566%28SQL.90%29.aspx







Follow me on Twitter: @way0utwest

Forum Etiquette: How to post data/code on a forum to get the best help
Post #833593
Posted Monday, December 14, 2009 7:17 AM
SSC-Addicted

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

Group: General Forum Members
Last Login: Yesterday @ 11:56 AM
Points: 442, Visits: 496
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. haha

4) 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.
Post #833801
Posted Friday, December 18, 2009 5:15 PM


Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Monday, November 24, 2014 4:29 PM
Points: 752, Visits: 920
Jack Corbett (12/11/2009)
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.



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.


---
Timothy A Wiseman
SQL Blog: http://timothyawiseman.wordpress.com/
Post #836729
Posted Friday, December 18, 2009 7:09 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 8:58 PM
Points: 35,792, Visits: 32,473
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 . 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.


--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #836740
Posted Thursday, September 9, 2010 4:45 PM
SSC-Enthusiastic

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

Group: General Forum Members
Last Login: Friday, December 12, 2014 12:25 PM
Points: 113, Visits: 299
Ive only used it to transpose a dataset, which I later found out could be done with unpivot.
Post #983510
Posted Monday, November 15, 2010 10:30 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, December 10, 2014 2:17 PM
Points: 30, Visits: 564
Tao Klerks (12/11/2009)
Registry Access. Haven't figured out an efficient and sensible way to do it in SQL Server 2005 without using the CLR.

Powershell for a decent bit of it. Not necessarily actual registry hacking, but there's a lot you can change with PS.



Post #1020920
Posted Saturday, July 27, 2013 10:05 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, August 13, 2013 10:52 AM
Points: 7, Visits: 13
Dave Ballantyne (12/11/2009) CLR routines are again (over)used by non-set based mindset developers.
But if the requirements are not "set-based" or "column-based" ....

For example, if the ROW (condition) then select col_A as @vrbl_X, col_B as @vrbl_Y

else, select L, M, N, O, P, Q, ...V and arrange them into several lines of a flat file according to X12 rules, inserting @vrbl_X, @vrbl_Y, and other globals at the appropriate places, and updating the globals conditionally as you go along.

Sure, a stored procedure CAN do it, but a script component or other CLR is easier. Especially when the DB is read-only and you can't stick a sproc into it!
Post #1478292
Posted Saturday, July 27, 2013 5:26 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 8:58 PM
Points: 35,792, Visits: 32,473
Wesley.Groleau (7/27/2013)


For example, if the ROW (condition) then select col_A as @vrbl_X, col_B as @vrbl_Y

else, select L, M, N, O, P, Q, ...V and arrange them into several lines of a flat file according to X12 rules, inserting @vrbl_X, @vrbl_Y, and other globals at the appropriate places, and updating the globals conditionally as you go along.

Sure, a stored procedure CAN do it, but a script component or other CLR is easier. Especially when the DB is read-only and you can't stick a sproc into it!


I guess my question would be, why do you think that couldn't be done in a set-based or column-based fashion? Remember that "Set-Based" doesn't mean "all in one query". And just because a database is "Read-Only" doesn't mean you can't have a stored proc that runs against it.

So far as "a script component or other CLR is easier" goes, that depends on a whole lot but mostly depends on the knowledge of the programmer both about T-SQL and the "other" language and the actual problem itself, whatever it is.. Are they smarter on the declarative or procedural side of the house? I agree that I've seen some folks that have worked absolute wonders using CLR (especially SQLCLR) as it was meant to be used. I've also seen some people do some absolutely stupid things with it just because they weren't as familiar with T-SQL as they were with typical front-end-style programming.

Of course, that's not just limited to T-SQL.


--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1478323
Posted Sunday, July 28, 2013 11:28 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, August 13, 2013 10:52 AM
Points: 7, Visits: 13
Jeff Moden (7/27/2013)
Wesley.Groleau (7/27/2013)
(snip) Sure, a stored procedure CAN do it, but a script component or other CLR is easier. (snip)
I guess my question would be, why do you think that couldn't be done in a set-based or column-based fashion?
If I thought that, would I have said what I didn't snip?

If you instead ask "why do you think that shouldn't," my answer would be "because I've done both"

If you haved a screen full of declares and ifs and loops, and are storing values as you go along to be used in later iterations, you are doing procedural programming and might as well use a language originally intended for that.

If you are able to avoid either by using nested subqueries (yes, I've done that too, four levels deep), then there's a high probability the next person who has to work on it won't understand it, and will just start over. Or break it.

I know others disagree, but for me, the priorities are
1. Meets the requirements
2. Easy to understand
3. Robust
4. Amenable to re-use
5. Easy to write
6. Efficient

(1) is obviously the first, but (2) makes 1/3/4/5 more likely. And an irrational commitment to (6) often screws up all the others, including (1).
Post #1478381
Posted Sunday, July 28, 2013 11:37 AM


Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, July 30, 2013 8:55 AM
Points: 21, Visits: 96
Question Requirements!

Post #1478382
« Prev Topic | Next Topic »

Add to briefcase «««12345»»

Permissions Expand / Collapse