SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


What's in your CLR?


What's in your CLR?

Author
Message
Steve Jones
Steve Jones
SSC Guru
SSC Guru (148K reputation)SSC Guru (148K reputation)SSC Guru (148K reputation)SSC Guru (148K reputation)SSC Guru (148K reputation)SSC Guru (148K reputation)SSC Guru (148K reputation)SSC Guru (148K reputation)

Group: Administrators
Points: 148434 Visits: 19444
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
My Blog: www.voiceofthedba.com
LerxtDBA
LerxtDBA
SSC Eights!
SSC Eights! (812 reputation)SSC Eights! (812 reputation)SSC Eights! (812 reputation)SSC Eights! (812 reputation)SSC Eights! (812 reputation)SSC Eights! (812 reputation)SSC Eights! (812 reputation)SSC Eights! (812 reputation)

Group: General Forum Members
Points: 812 Visits: 623
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.
timothyawiseman
timothyawiseman
SSCrazy
SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)

Group: General Forum Members
Points: 2402 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/
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (218K reputation)SSC Guru (218K reputation)SSC Guru (218K reputation)SSC Guru (218K reputation)SSC Guru (218K reputation)SSC Guru (218K reputation)SSC Guru (218K reputation)SSC Guru (218K reputation)

Group: General Forum Members
Points: 218239 Visits: 41995
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.

--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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Kyle Freeman
Kyle Freeman
SSC-Enthusiastic
SSC-Enthusiastic (142 reputation)SSC-Enthusiastic (142 reputation)SSC-Enthusiastic (142 reputation)SSC-Enthusiastic (142 reputation)SSC-Enthusiastic (142 reputation)SSC-Enthusiastic (142 reputation)SSC-Enthusiastic (142 reputation)SSC-Enthusiastic (142 reputation)

Group: General Forum Members
Points: 142 Visits: 332
Ive only used it to transpose a dataset, which I later found out could be done with unpivot.
michael bourgon
michael bourgon
SSC-Enthusiastic
SSC-Enthusiastic (171 reputation)SSC-Enthusiastic (171 reputation)SSC-Enthusiastic (171 reputation)SSC-Enthusiastic (171 reputation)SSC-Enthusiastic (171 reputation)SSC-Enthusiastic (171 reputation)SSC-Enthusiastic (171 reputation)SSC-Enthusiastic (171 reputation)

Group: General Forum Members
Points: 171 Visits: 623
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.



Wesley.Groleau
Wesley.Groleau
SSC Rookie
SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)

Group: General Forum Members
Points: 43 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!
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (218K reputation)SSC Guru (218K reputation)SSC Guru (218K reputation)SSC Guru (218K reputation)SSC Guru (218K reputation)SSC Guru (218K reputation)SSC Guru (218K reputation)SSC Guru (218K reputation)

Group: General Forum Members
Points: 218239 Visits: 41995
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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Wesley.Groleau
Wesley.Groleau
SSC Rookie
SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)

Group: General Forum Members
Points: 43 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).
KermitTheRock
KermitTheRock
SSC Journeyman
SSC Journeyman (75 reputation)SSC Journeyman (75 reputation)SSC Journeyman (75 reputation)SSC Journeyman (75 reputation)SSC Journeyman (75 reputation)SSC Journeyman (75 reputation)SSC Journeyman (75 reputation)SSC Journeyman (75 reputation)

Group: General Forum Members
Points: 75 Visits: 100
Question Requirements!
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search