February 20, 2014 at 8:30 pm
Comments posted to this topic are about the item The SQLCLR Impact
February 21, 2014 at 1:56 am
We have found no need to impliment clr, For us, SQL is about returning SETS and although it takes a mind flip to stop thinking procedurally and start with set based solutions only, it usually results in a substantial increase in performance. Materialised views are far more beneficial.
February 21, 2014 at 2:14 am
We have a data warehouse and reporting system running on SQL Server 2008R2 and we have not touched CLR yet. All our needs have been met via the 'traditional' T-SQL route with reporting implemented in SSRS.
Dave Morris :alien:
"Measure twice, saw once"
February 21, 2014 at 2:47 am
In my experience it is a very occasionally useful part of the toolkit. I can think of two times it has been used in anger over the 8 odd years it has been available to me, and I cannot even remember what they were - perhaps something to do with regular expressions on one occasion.
The problem for me is that the use cases I can think of can generally be accomplished some other way, and the skills needed to make such functionality working are thus not really worth maintaining. Meaning, it leaves a mess that is perhaps slightly difficult for others to come to grips with when they come across it. Perhaps this is testament to how much functionality is actually covered by SQL Server OOTB.
February 21, 2014 at 3:18 am
We're another "never used it" shop - there's always been another way so we haven't needed clr.
February 21, 2014 at 4:04 am
We use function made in VB.NET for write file from T-SQL directly to disk, usualy XML. 🙂
Regards
February 21, 2014 at 4:39 am
I have used CLR assemblies occasionally, sometimes to use regular expressions and sometimes for very specific ends. For example I've written an assembly for parsing compacted integers from AS400. I think for these very specific ends it's a very useful extension to Sqlserver. I agree with a previous poster set-based approach to data is preferred. Especially programmers should not misuse CLR assemblies to wire in procedural code as they sometimes also do in stored procedures. A very good apply of CLR's in my opinion is to bypass the use of xp_cmdshell as it can be dressed up with additional security and limits the extent of the code to the functions of the CLR only. I prefer file operations to be done by CLR's also because you have much more control over file formats, encoding etc. In my opinion CLR's have a very limited place but are potentially a very powerful addition to Sqlserver.
February 21, 2014 at 4:45 am
Only used in a prototype. A number of different options were considered. SQLCLR was not selected in the end. I am sure that there are some valid uses, however, there always seems to be another way. Some DBAs seem to think that opening up an instance like this would be opening the door to .NET developers. I would probably agree (says the .NET developer) 😉
Gaz
-- Stop your grinnin' and drop your linen...they're everywhere!!!
February 21, 2014 at 5:18 am
We've used it in a few cases, for specialized aggregates or to split an object into rows, but we don't access tables from within any CLR code I've seen. In most cases we've used it to significantly improve performance, in a few to make code more readable and maintainable. In the latter case, with one specific aggregate, we've had mixed performance results - one line of CLR code vs a 40 element expression, for a small (<100000 say) data set no difference in performance, but for large ones SQL sometimes generates a slower plan using the CLR - so its treating native aggregates differently from CLR ones in some circumstances.
February 21, 2014 at 5:45 am
We have several assemblies that we built for performing some needed string manipulation like regular expressions, with enhanced lookups on the replacement expressions. We have also built several Aggregate functions that perform complex attribute weighting based on a configuration parameter. Together those two assemblies have saved us hundreds of hours and also kept our code more concise and maintainable. We have not needed to author any CLR stored procedures -- most of this kind of logic gets written in our app server layers.
Cheers
R. Oz.
February 21, 2014 at 5:52 am
We highly recommend that developers seek other alternatives first. Many of the uses cases for CLR have been replaced by new functionality in the database engine, such as compression, encryption, etc. So far, we have not promoted any CLR objects to production.
February 21, 2014 at 6:12 am
I remember using it once. There was compression done outside of the database, and the data was stored compressed. But for some reason, the database also needed to be able to read that data. I think it may have been for reporting. It was a few years ago, and I don't remember the specifics.
Thanks,
MKE Data Guy
February 21, 2014 at 6:51 am
I worked on a project for Wells Fargo a few years ago. We had a database developer who used CLR procedures for 60 percent of his stored procs. We found the performance hit to be minimal and the flexibility to be superior. In my current work I have used CLR procedures several times when I needed to recursively look at or process .data
February 21, 2014 at 6:54 am
We process electronic transaction (ISO 8583). Since about a couple of years ago, we've had the need to format part of the response message in XML or a similar tagged format. We have found that a CLR assembly is way more efficient for this specific task.
February 21, 2014 at 7:02 am
We run an ERP with a vendor supplied database, and they provided us with several thousand CLR based functions, most of which act as "computed columns". Their toolkit also provides for creating custom functions. They work reasonably well within the vendor UI, but we avoid using them in native T-SQL queries, as they do come with a significant performance hit.
Viewing 15 posts - 1 through 15 (of 51 total)
You must be logged in to reply to this topic. Login to reply