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
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (149K reputation)SSC Guru (149K reputation)SSC Guru (149K reputation)SSC Guru (149K reputation)SSC Guru (149K reputation)SSC Guru (149K reputation)SSC Guru (149K reputation)SSC Guru (149K reputation)

Group: General Forum Members
Points: 149821 Visits: 41732
Comments posted to this topic are about the item What's in your CLR?

--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
Dave Ballantyne
Dave Ballantyne
SSCarpal Tunnel
SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)

Group: General Forum Members
Points: 4826 Visits: 8370
The only CLR routine that I have written , that wasnt "hello world", is to call and mail a SSRS report.

http://sqlblogcasts.com/blogs/sqlandthelike/archive/2009/08/27/direct-emailing-of-ssrs-reports-via-sqlclr.aspx

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 Phil Factor Speed Phreak Challenge 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.



Clear Sky SQL
My Blog
shane.davies
shane.davies
Valued Member
Valued Member (60 reputation)Valued Member (60 reputation)Valued Member (60 reputation)Valued Member (60 reputation)Valued Member (60 reputation)Valued Member (60 reputation)Valued Member (60 reputation)Valued Member (60 reputation)

Group: General Forum Members
Points: 60 Visits: 97
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.
Todd Townley
Todd Townley
Old Hand
Old Hand (377 reputation)Old Hand (377 reputation)Old Hand (377 reputation)Old Hand (377 reputation)Old Hand (377 reputation)Old Hand (377 reputation)Old Hand (377 reputation)Old Hand (377 reputation)

Group: General Forum Members
Points: 377 Visits: 479
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.
Grant Fritchey
Grant Fritchey
SSC Guru
SSC Guru (71K reputation)SSC Guru (71K reputation)SSC Guru (71K reputation)SSC Guru (71K reputation)SSC Guru (71K reputation)SSC Guru (71K reputation)SSC Guru (71K reputation)SSC Guru (71K reputation)

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

----------------------------------------------------
The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood...
Theodore Roosevelt

The Scary DBA
Author of: SQL Server Query Performance Tuning and SQL Server Execution Plans
Product Evangelist for Red Gate Software
Tao Klerks
Tao Klerks
SSCommitted
SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)

Group: General Forum Members
Points: 1775 Visits: 1249
Registry Access. Haven't figured out an efficient and sensible way to do it in SQL Server 2005 without using the CLR.

http://poorsql.com for T-SQL formatting: free as in speech, free as in beer, free to run in SSMS or on your version control server - free however you want it.
Jack Corbett
  Jack Corbett
SSC-Dedicated
SSC-Dedicated (30K reputation)SSC-Dedicated (30K reputation)SSC-Dedicated (30K reputation)SSC-Dedicated (30K reputation)SSC-Dedicated (30K reputation)SSC-Dedicated (30K reputation)SSC-Dedicated (30K reputation)SSC-Dedicated (30K reputation)

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



Jack Corbett

Applications Developer

Don't let the good be the enemy of the best. -- Paul Fleming
At best you can say that one job may be more secure than another, but total job security is an illusion. -- Rod at work

Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
How to Post Performance Problems
Crosstabs and Pivots or How to turn rows into columns Part 1
Crosstabs and Pivots or How to turn rows into columns Part 2
Andy Leonard
Andy Leonard
SSCommitted
SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)

Group: General Forum Members
Points: 1736 Visits: 1101
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

Andy Leonard
Data Philosopher, Enterprise Data & Analytics
jay-h
jay-h
SSCrazy
SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)

Group: General Forum Members
Points: 2951 Visits: 2351
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).

...

-- FORTRAN manual for Xerox Computers --
Lowell
Lowell
SSC-Forever
SSC-Forever (46K reputation)SSC-Forever (46K reputation)SSC-Forever (46K reputation)SSC-Forever (46K reputation)SSC-Forever (46K reputation)SSC-Forever (46K reputation)SSC-Forever (46K reputation)SSC-Forever (46K reputation)

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

Lowell

--
help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

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