February 21, 2014 at 7:14 am
We use CLRs (have for some times) with custom C# code. Previously, it ran on all of our customer-facing SQL servers (non-SSRS report servers), now only one server uses it to support one customer.
We do custom projects periodically that will use CLRs.
Argue for your limitations, and sure enough they're yours (Richard Bach, Illusions)
February 21, 2014 at 7:19 am
I've only used it once, and to the best of my knowledge, my company has only used it on two projects. Both were just before the advent of LINQ to SQL and EF. In the project I worked on, we used stored procedures to do all the data access and a bit of aggregation. As the project wore on, the requirements essentially transformed into a full rules engine. My client was not familiar with C# and wanted to be able to manipulate the rules from within SQL Server directly. I eventually switched to a SQLCLR SP to iterate the and apply the rules once my original dynamic SQL SP grew to several hundred, unreadable lines. SQLCLR really cleaned things up and sped the process up from 1.5 hours to 1 hour.
I recently did a refresh of that project, which included a move from SQL 2005 to SQL 2008. I was able to get rid of the remaining dynamic SQL and switched to a streaming TVF rather than the SP. The rules now apply in fifteen minutes.
I'm sure other approaches are better, and were we given the time to build a more robust front-end, we could probably have moved a lot of the data access to the application layer. Nevertheless, the SQLCLR really helped in terms of cutting down processing time.
February 21, 2014 at 7:21 am
At some point, I think we were using or evaluating a 3rd party column encryption solution that leveraged CLR functions. However, what ultimately got deployed to production was something I implemented using views and native symmetric key functions.
I can see the argument for leveraging CLR functions in niche case usage scenarios where custom pattern matching or aggregation is required, but I've actually never had a real use for it.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
February 21, 2014 at 7:28 am
my old shop used it for two reasons; regular expressions for doing ETL on staging tables(no SSIS in that shop...everything is TSQL), and a custom implementation of AES encryption; the encryption was used in multiple applications as a class inside them, and we needed it on the server at times as well. That class did not produce the same results as standard AES encryption, so we pushed it into an CLR so that we did not have to switch and modify legacy apps.
now test wise, i've done a zillion things with CLR: reports to disk in various text formats, including html, read web pages, results form web services, zip and unzip files , PGP encryption, and so many examples i'd have to look at my projects folder.
Lowell
February 21, 2014 at 7:29 am
I used it to build aggregate functions that should be in SQL in opinion.
Example: the ability to "Sum" a char/varchar/etc field.
February 21, 2014 at 7:52 am
drew.king1 (2/21/2014)
I used it to build aggregate functions that should be in SQL in opinion.Example: the ability to "Sum" a char/varchar/etc field.
Please, explain what it means to summarize a character column.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
February 21, 2014 at 8:01 am
I have 5 clients using the same piece of software from a 3rd party vendor. This vendor has rolled out CLR procedures to each of the clients. In this particular case, the CLR starts to leak memory and eventually impacts SQL Server. The vendor is working on a fix, but in this case the implementation could be better.
In other cases, CLR works marvelously.
In any case, the overall footprint of CLR (from my perspective) is a very small footprint.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
February 21, 2014 at 8:02 am
sum as in addition
t1:
animal Type
============
dog Pet
cat Pet
cow Livestock
SELECT Type, Concat(animal) as animals
from t1
group by type
Type Animals
===============
Livestock cow
Pet dog, cat
February 21, 2014 at 8:06 am
Never found a good use for it. Always return sets and process externally if required.
February 21, 2014 at 8:32 am
We have a small IT department. There used to be 4 of us, but we're down to 2. One of those who left was using SQLCLR, but no one else has since he left. I'm not even sure where with it, or if he just was researching it and didn't get too far into it. And at least for me, I can't ask him. When he left he was very ill. He passed away a few months after that. It's very sad, as he was a close friend of mine.
Well, at least if he did do something with SQLCLR, I don't see any impact. It would have involved some software he was working on which we're no longer using.
Rod
February 21, 2014 at 8:40 am
I haven't touched the CLR.
February 21, 2014 at 8:46 am
The only use at my previous shop was for string aggregates, where it's orders of magnitude faster than TSQL. I second the thought that string aggregates should be native SQL.
February 21, 2014 at 8:51 am
We use CLR quite a bit for functions that are not well suited to TSQL. If it works well in TSQL, then do it there, but some things are obviously better in CLR.
Examples of how we use it are:
Regular Expressions
Import flat file image (blob) to database
Export image to flat file
File manipulation (copy, delete, move etc...)
Sending e-mail on Express version of SQL
Web Service calls
Ping across network
Starting & stopping services locally or on remote computers
February 21, 2014 at 8:56 am
Eric M Russell (2/21/2014)
drew.king1 (2/21/2014)
I used it to build aggregate functions that should be in SQL in opinion.Example: the ability to "Sum" a char/varchar/etc field.
Please, explain what it means to summarize a character column.
declare @animal table (
animal_name varchar(30) not null,
animal_type varchar(30) not null,
primary key (animal_name) );
insert into @animal ( animal_name, animal_type )
values ('dog','Pet'),
('cat','Pet'),
('cow','Livestock');
select a.animal_type,
(
select animal_name + ', '
from @animal as b
where b.animal_type = a.animal_type
order by animal_name
for xml path('')
) animal_names
from @animal as a
group by a.animal_type;
animal_type animal_names
Livestock cow,
Pet cat, dog,
I'd call that concatenating or serializing and do that on occasion for stored procedurs that return an application facing resultset. I typically use a correlated sub-query with the "for xml" clause. Did you find that using a CLR function scales better across a large recordset?
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
February 21, 2014 at 9:11 am
We use CLR and it is, in fact, integral to a number of different pieces of functionality within our software. Probably the most interesting way we use CLR is as a backbone for an extensible search provider plugin architecture. We essentially allow our users to join search results to a custom table valued function or stored procedure that generates a table. This allow us to keep our plugin architecture really generic and has the added benefit of allowing our users to write a single query that merges search results from many different search providers.
Viewing 15 posts - 16 through 30 (of 51 total)
You must be logged in to reply to this topic. Login to reply