Which situation we go for CLR?

Question

Which situation we go for CLR?

SqlUser-529296

SSCarpal Tunnel

Points: 4072
More actions
June 10, 2008 at 4:08 am

#192179

Hi,
Can anybody tell me,in which situation we will go for CLR integration?
---

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

alonzo SSC-Addicted Points: 443 More actions · Answer 1

Consider CLR when a procedure requires significant data access and computation. You would then separate the procedural code into a CLR portion calling the T-SQL procedure that performs data access, or vice-versa.

mjhillman Newbie Points: 3 More actions · Answer 2

I like to use CLR SPs when I have some special code I don't want the whole world to see. Could probably be reversed engineered but at least they will have to work at it.

Jack Corbett SSC Guru Points: 184393 More actions · Answer 3

mike (6/11/2008)
I like to use CLR SPs when I have some special code I don't want the whole world to see. Could probably be reversed engineered but at least they will have to work at it.

I never really thought about using the CLR for obfuscation, but there is a case for it if you are willing to take a performance hit. I have never used the CLR because I have not found a situation that I could not solve in T-Sql. Most discussions I have read on it go back to string manipulation and regular expressions. There could be a case made for complex calculations as well.

Jack Corbett
Consultant - Straight Path Solutions
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

SqlUser-529296 SSCarpal Tunnel Points: 4072 More actions · Answer 4

SqlUser-529296

SSCarpal Tunnel

Points: 4072

June 12, 2008 at 12:05 am

#827646

Thanks you guys for your valuable information. 🙂

Jeff Moden SSC Guru Points: 1003867 More actions · Answer 5

Heh... I'd have to say just about never... with the exception of RegEx, there's not much that can't be done using some form of set based programming in SQL Server that will also beat a CLR for performance. Heck, even the very procedural task of doing a running total can be done in SQL Server 2000 and it will blow the doors off an equivalent CLR.

SQL Server is an imagination limited tool... unlimited imagination, unlimited tool. Limited imagination, use a 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.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 6

What MS is not making particuarly clear is that CLR is intended to, in a couple of versions, replace extended stored procs.

Books Online
Extended Stored Procedures
Important:
This feature will be removed in a future version of Microsoft SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use this feature. Use CLR Integration instead.

Essentially, you use CLR for things that T-SQL cannot do, if you find it necessary to do such things within the DB.

Where I work, we're mainly using it to query web services straight from a database. We've got a couple apps (CRM) that only accept data loads via their web service and only provide data via their web service, so we have a CLR proc that runs at night that syncs the CRM data with a couple other systems. Sharepoint's another example of this.

It's also interesting to note that a lot of the newer features of SQL 2008 (spacial data types, hierarchy, etc) are implemented using CLR.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Marios Philippopoulos SSC Guru Points: 57190 More actions · Answer 7

CLR is useful in such cases as these (benefit in parentheses):

- string manipulation, regular expressions etc. (performance)

- OS-level operations, previously possible only using xp_cmdshell (more granular security, functionality with increased specialization, functionality with increased ease of use)

- complex mathematical calculations (performance)

- As Gail mentioned, replacing extended stored procedures (future SQL-Server compatibility)

- web services

Just as with any other tool, SQLCLR needs to be used judiciously and tested rigorously for performance against conventional T-SQL solutions. It's not a one-size fits all and there are no absolutes.

__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]

jezemine SSCrazy Eights Points: 8059 More actions · Answer 8

Another reason people haven't mentioned is code reuse.

In our shop we have a library of helper functions that is used by all our managed components. We also wanted to allow stored procs to have access to these helpers, so we expose the library in SQL Server as CLR UDFs and have only one code base to support.

---------------------------------------
elsasoft.org

Jeff Moden SSC Guru Points: 1003867 More actions · Answer 9

Marios Philippopoulos (6/14/2008)
CLR is useful in such cases as these (benefit in parentheses):
- string manipulation, regular expressions etc. (performance)
- OS-level operations, previously possible only using xp_cmdshell (more granular security, functionality with increased specialization, functionality with increased ease of use)
- complex mathematical calculations (performance)
- As Gail mentioned, replacing extended stored procedures (future SQL-Server compatibility)
- web services
Just as with any other tool, SQLCLR needs to be used judiciously and tested rigorously for performance against conventional T-SQL solutions. It's not a one-size fits all and there are no absolutes.

Good list... the one about OS-level operations isn't exactly correct, though. Many operations were available through undocumented stored procedures. Please don't get religious about that... even documented features can go away at a moment's notice. 😉

--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.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Marios Philippopoulos SSC Guru Points: 57190 More actions · Answer 10

Jeff Moden (6/14/2008)
Marios Philippopoulos (6/14/2008)
CLR is useful in such cases as these (benefit in parentheses):
- string manipulation, regular expressions etc. (performance)
- OS-level operations, previously possible only using xp_cmdshell (more granular security, functionality with increased specialization, functionality with increased ease of use)
- complex mathematical calculations (performance)
- As Gail mentioned, replacing extended stored procedures (future SQL-Server compatibility)
- web services
Just as with any other tool, SQLCLR needs to be used judiciously and tested rigorously for performance against conventional T-SQL solutions. It's not a one-size fits all and there are no absolutes.
Good list... the one about OS-level operations isn't exactly correct, though. Many operations were available through undocumented stored procedures. Please don't get religious about that... even documented features can go away at a moment's notice. 😉

Thanks Jeff, valid point.

__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]