CLR Stored procedure without visual studio?

Question

CLR Stored procedure without visual studio?

Viewing 5 posts - 16 through 19 (of 19 total)

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

Jonathan Kehayias One Orange Chip Points: 26778 More actions · Answer 1

Regular Expression matches and replacements, as we've discussed previously, but I have also found good utility in CLR Aggregations under specific scenarios, but the 8KB limitation in SQL 2005, kind of limits their use. The good thing is that this has been changed in SQL Server 2008, and large aggregates are now possible. An example use of this would be a Fourier Fast Transformation, which someone on another forum challenged whether or not it could be done in SQLCLR. There are other types of aggregates that could have specific utility in certain applications/datasets for statistical analysis, data mining, and/or data group segmentational analysis.

SQLCLR can also be used to eliminate the need for xp_cmdshell calls to things like bcp to close the security gap for exporting/importing flat file data with the bcp tool. Anything that you would have previously used an XP for, can be rewritten in SQLCLR to lock down the XP calls, and make for a more secure environment. Using explicit impersonation, and Windows Authentication can even further lock down certain tasks to Windows OS level rights as well as internal SQL Server rights.

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 2

Cool... thanks for the feedback...

Agreed, RegEx Replace is one of the places where a CLR can beat T-SQL. So far as imports with BCP, go... not needed if you know how to use Bulk Insert so no SV there. Agreed that you would probably need xp_CmdShell for exports... but those are normally on ETL systems with nothing public facing, so still no SV there. FFT sounds very interesting, but why would you do something like that on data in a database? I'd think that you'd store the result of an FFT in a database which would only require a sampling app... not a CLR... unless the CLR is used to "bridge the gap" between the source and the database.

What kind of aggregations are you talking about?

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

RBarryYoung SSC Guru Points: 143329 More actions · Answer 3

To this I might also add:

Interaction with Web/Internet resources or other http(s) communication

Codebase preservation: using that the same code is used in different tiers

Code reuse: reusing production code that is already proven/tested

Specialized UDT's: Like the Spatial datatypes on 2008

High computation functions/subroutines

The last one needs some explanation: The key here is not so much the amount of computation required, as it is the ratio of computation required to the amount of data and the number of columns and records required. Additionally, the compution should be the kind that benefits greatly from procedural optimization (SQL has set and access optimization, but not procedural).

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

RBarryYoung SSC Guru Points: 143329 More actions · Answer 4

SV?

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

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

Sorry... "Security Violation"... forgot to do a final replace on "SV" before I posted.

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