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


The SQLCLR Impact


The SQLCLR Impact

Author
Message
currentp
currentp
SSC Rookie
SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)

Group: General Forum Members
Points: 29 Visits: 90
My experience will echo a lot of the comments already mentioned. I use T-SQL whenever possible because, in most cases, T-SQL performs faster, it's easier to maintain and our clients’ IT departments are not as verse with the CLR so they cannot support it. With that said, there has been a few times where T-SQL fell short of our needs and CLR external objects were needed.

I needed a common interface for a collection of applications to easily extract data from a historian which has a proprietary backend database and merge that data with a MES system. The proprietary backend database is a glorified key/value system that is great for tending data over time, but cannot be used for aggregating data or linking multiple keys together to make correlations in the data, which a relational database does naturally. The vendor has built components to overcome these limitations, but I shy away from them because of lower performance, the additional cost and/or they do not really fit into our current architecture. I could have created a .Net DLL for the applications, but by using the SQLCLR, other applications (database applications and .Net applications) could leverage the assemblies. Plus, it was much easier to write a suite of CLR objects to expose the data in the proprietary database and then use T-SQL stored procedures on the exposed data.

Another reason for using the CLR was for reporting. I have written a number of external objects to help format data for SSRS report and to expose analysis services metadata via AMO (primarily role members and role permissions).

There's my 2 cents - Tom

Many of our clients have multiple SQL Servers and I try to install the CLRs on as few servers as possible; usually the servers used for reporting. Having few instances of the assemblies makes it easier to perform updates.
jmoneysteph
jmoneysteph
Grasshopper
Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)

Group: General Forum Members
Points: 19 Visits: 28
In my shop CLR functions have a limited use, primarily for complex financial computations. One crucial best practice when coding CLR objects should include mechanisms to avoid runaway CLR processes (calling sleep functions every x iterations). Especially true for looping procedures. A non-yielding CLR process in SQL Server can have disastrous results when it happens to be on the same scheduler as the Log Writer process. Let's just say I have first hand experience here Smile.
Gary Varga
Gary Varga
One Orange Chip
One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)

Group: General Forum Members
Points: 27430 Visits: 6550
jmoneysteph (2/21/2014)
In my shop CLR functions have a limited use, primarily for complex financial computations. One crucial best practice when coding CLR objects should include mechanisms to avoid runaway CLR processes (calling sleep functions every x iterations). Especially true for looping procedures. A non-yielding CLR process in SQL Server can have disastrous results when it happens to be on the same scheduler as the Log Writer process. Let's just say I have first hand experience here Smile.


I, for one, would like to see bad practice/good practice examples of this. Any change of either a quick posting or an article?

Gaz

-- Stop your grinnin' and drop your linen...they're everywhere!!!
GoofyGuy
GoofyGuy
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1197 Visits: 971
From an application developer's perspective, I tend to think of using SQLCLR as a violation of the three-tier model. The data tier really should be abstracted from the rest of the architecture. Some advantages of this abstraction are performance, scalability, and vendor-neutrality (MySQL? Anyone? Anyone?).

Additionally, I'm not now, nor even have been, a proponent of using the .Net framework, in *any* application tier. HTML, JavaScript, Ajax, and CSS have been my preferred technologies for the presentation and business logic tiers since .Net's initial release.
currentp
currentp
SSC Rookie
SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)

Group: General Forum Members
Points: 29 Visits: 90
Gary Varga,

In my experience I use the following as a best practice:

•Validate all input parameters and throw the appropriate exception when the parameter is not correct

•Use error handling and re-throw exceptions to the caller. Do not try and log exception in the CLR - have the T-SQL (or caller) do that.

•Write multiple smaller precise CLR methods verses one big one that does everything. This way you could take advantage of both worlds (T-SQL for set operations and CLR for everything else). I have also found that performance degrades on larger CLR objects. Using debuggers, it appears that SQL Server tries to determine how to perform a ROLLBACK on all CLR calls. The larger the CLR object, the longer it takes before the code is actually executed.

•When writing aggregations, build the result as the aggregation moves through the group verses storing all group members than performing the action. This will improve performance and reduce the required cache on large groups.

I'm sure there are more, but those are my big ones.

Tom
EricEyster
EricEyster
Right there with Babe
Right there with Babe (758 reputation)Right there with Babe (758 reputation)Right there with Babe (758 reputation)Right there with Babe (758 reputation)Right there with Babe (758 reputation)Right there with Babe (758 reputation)Right there with Babe (758 reputation)Right there with Babe (758 reputation)

Group: General Forum Members
Points: 758 Visits: 520
Does the CLR handle the where clause properly or does it always return all sub-values?
HighPlainsDBA
HighPlainsDBA
SSC-Addicted
SSC-Addicted (476 reputation)SSC-Addicted (476 reputation)SSC-Addicted (476 reputation)SSC-Addicted (476 reputation)SSC-Addicted (476 reputation)SSC-Addicted (476 reputation)SSC-Addicted (476 reputation)SSC-Addicted (476 reputation)

Group: General Forum Members
Points: 476 Visits: 799
I've been pleasantly surprised by what I've seen for the most part over the years. Even at my last employer (20K+ employees, IT centric shop) there wasn't much abuse of the CLR. Most engineers were using it sensibly for high volume string manipulation and other function oriented work where T-SQL isn't so strong.
thisisfutile
thisisfutile
Say Hey Kid
Say Hey Kid (713 reputation)Say Hey Kid (713 reputation)Say Hey Kid (713 reputation)Say Hey Kid (713 reputation)Say Hey Kid (713 reputation)Say Hey Kid (713 reputation)Say Hey Kid (713 reputation)Say Hey Kid (713 reputation)

Group: General Forum Members
Points: 713 Visits: 1005
I had to Bing search it, so no, I don't use it. :-P
Peter Ryan-300561
Peter Ryan-300561
Forum Newbie
Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)

Group: General Forum Members
Points: 5 Visits: 113
Hi,

I too added two functions for regular expressions. One to test and return a 0 or 1. And one to return the matching string. I tried to minimize the amount of work in the CLR and only use the functions when I really need them.

http://www.sqllion.com/2010/12/pattern-matching-regex-in-t-sql/

and

http://blogs.msdn.com/b/sqlclr/archive/2005/06/29/regex.aspx

I have added these to all my servers in a separate database so that I can call the same function from each of the servers.

Thanks,
Peter
SumOfDavid
SumOfDavid
SSC Veteran
SSC Veteran (239 reputation)SSC Veteran (239 reputation)SSC Veteran (239 reputation)SSC Veteran (239 reputation)SSC Veteran (239 reputation)SSC Veteran (239 reputation)SSC Veteran (239 reputation)SSC Veteran (239 reputation)

Group: General Forum Members
Points: 239 Visits: 699
I use CLR extensively, and while it may be possible to kludge some convoluted solutions together to accomplish the same thing, if coded correctly, and used appropriately, it makes code immensely simpler and allows for solutions to be deployed quicker. I have created a couple of very specialized CLR's for specific encryption requirements (the requirements dictated the usage of a specific encryption methodology, the key length, the seed requirements, # of hash iterations, etc).

But my biggest time saver has been using the SQL# libraries from www.sqlsharp.com . ** disclaimer: I am in no way affiliated with SQLSharp, and paid full price for my license **

I have been using the library for years now, and Solomon Rutzky (the creator) is immensely helpful when it comes to adding functions, procs, features, and/or fixing issues. Here's some sample examples of what I use it for:

Development
* Reqular Expressions (try validating (or even semi-validating) email formats without it)
* String splitting (yes, there are fast Tally solutions out there, but they're still slower than CLR (small and large strings))
* String concatenation (yes, there are other ways to accomplish this, but the CLR function is simple and fast)
* Validation of data type conversions (Mostly used before the SQL 2012 TRY_CONVERT function existed)
* Creating ranges of Dates/Times (very handy for some scheduling applications)
* Creating ranges of numbers (always useful, especially if you want the numbers to be stepped (e.g. 5,10,15,etc)
* Date manipulation and formatting (you can do all of this manually, but...why?)

DBA
* File/Directory manipulation (yes, you can enable xp_cmdshell, but the results are cleaner and easier to work with in CLR, and so are the error messages)
* Internet access (the ability for me to easily call a CLR proc that goes and retrieves OR posts!! something across the internet is to me worth the price right there!)
* XML/XSL transformations (I actually requested this functionality, and Solomon graciously added it to SQL#) (For me, this comes in handy when I want to send a formatted html email message that is created based upon data in the db. It's basically a way to do mail merges. I can retrieve the necessary data elements (some scalar, some set based) and then apply an XSLT to generate a HTML email subject. This is a PITA to do manually)

ETL
* FTP (yes, you can do all of this via xp_cmdshell, but it's easier through SQL#)
* Internet access (see above) (some examples are retrieving a file from a website over port 80 through the firewall)
* Dynamic bulk importing and exporting of data (SSIS, BULK INSERT, bcp all have their pros and cons, there ARE ways to get the best of both worlds!)


The list goes on. Obviously, I'm a fan of SQL# and highly recommend it! I look at it this way: If Microsoft announced that there were 230+ new functions within SQL Server, the blogosphere would go crazy. To me, having this library is like having the complete tool box that everybody else either wishes they had, or doesn't even know they need because they've been hammering nails with a screwdriver so long, they just think that's *the way* to do it.

Now, I'm not advocating install SQL# on every database and let your developers go crazy with it, but it seems short sighted to not at least examine it, test it out, profile it, and release only what is necessary in your environment. Once you do, you'll wonder why you didn't sooner.

I've done a few presentations at our local user groups and SQL Saturdays and I have to say that I see people's eyes light up with ideas on how they can use this library and the time that it could save them.



David
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