What can you do in a CLR ?

[Isaac]

Can you do just about 'anything' in the guts of a CLR written in, say, c#.net?

Let's say I wanted to create a scalar clr function written in c#.net. If it has to 'return' something scalar in SQL that's fine, it will return a 1 for success and a 0 for failure or whatever.

But in the guts of it, could I, say.....tell it to take a file and split it into 10 files? Or copy something to another network folder? Or pretty much any IO type of stuff?

 

[tvanstiphout]

I wrote a .NET assembly once, and used it from Access (indirectly): http://accessmvp.com/TomVanStiphout/Simil.htm
Assemblies have to be trusted, so if yours let's say formats the C-drive, or uses the machine for crypto mining, the chances are that the trust will not be granted by the installing person.
But yes, AFAIK there are no restrictions to what parts of the vast .NET world you can use.

 

[Isaac]

I wrote a .NET assembly once, and used it from Access (indirectly): http://accessmvp.com/TomVanStiphout/Simil.htm
Assemblies have to be trusted, so if yours let's say formats the C-drive, or uses the machine for crypto mining, the chances are that the trust will not be granted by the installing person.
But yes, AFAIK there are no restrictions to what parts of the vast .NET world you can use.

Thanks Tom!!

 

[sonic8]

But in the guts of it, could I, say.....tell it to take a file and split it into 10 files? Or copy something to another network folder? Or pretty much any IO type of stuff?

Technically there are only very few limitations. See CLR Integration Programming Model Restrictions for details.

However, I recommend to be very careful with access to external resources, like your example of splitting files and network IO. These depend on external infrastructure, which may be slow and unreliable, and thus might negatively affect your SQL Server performance.

Always keep in mind that SQL Server processing power, due to licensing cost, is the most expensive (in terms of money!) processing power you can get anywhere. For memory and IO intensive operations, it's usually more sensible to run these operations outside of SQL Server on much cheaper processing infrastructure.

 

[Galaxiom]

For just moving files or other interacting with the files system I would use the Powershell integration with SQL Server.

For experienced VBA developers, it is easy to get started with CLR using VB.NET

 

[Isaac]

Interesting. It would seem that perhaps broadly speaking, CLR is not the best choice for some of what I want to do (like I need to take a file with any # of records - maybe 50 million, and split by 7 million at a time into separate files)........But, right now I am sort of trying to persuade my team to start using CLR's in general. I may try doing it this way just to get our foot in the door so to speak, but with the understanding that powershell may have actually made more sense.

They want to kind of start using Python for just about everything, but I hesitate because I don't want to learn Python in place of 1) forgetting my ssis skills, and 2) not expanding my other sql server skills, like clr's etc........So I'm pushing back a little bit to try to build out my sql server skills before I agree to just throw up my hands and use Python for everything.

Honestly what annoys me about Python is that it doesn't feel like "real programming". My impression is, it's so late-generation, it's more like the only real programmers are the guys who create the various python libraries. The rest of us are just using one-liners here and there, doesn't feel like much of anything other than being an end user pushing buttons.

Very subjective I know, just kinda how I feel about it

 

[Galaxiom]

I don't want to learn Python in place of 1) forgetting my ssis skills, and 2) not expanding my other sql server skills, like clr's etc........

The process of implementing CLR in SQL Server is trivial. The crux of it is learning to code in a dot NET language.

The big advantage of CLR is speed because the code is compiled. Written well, it will outperform anything procedural (as opposed to set based tasks where the engine excels) that can be done in SQL. For example I implemented a Damerau-Levenshtein distance function in CLR and it out performed the same function done in SQL, completing in sixty percent of the time taken by the SQL.

Python is an interpreted language so its performance is inevitably abysmal.

We had someone employed as a Data Analyst and he used Python. He left when management wouldn't increase his salary and I had to take on support of what little he had done. I knew very little about Python but It was immediately obvious that he really didn't have a clue what he was doing and was importing complex modules to do trivial tasks that could be done in the Python base. I think this zombie cut and paste approach would be a quite common situation with Python particularly.

Really though, one should not be afraid of learning new coding languages. The main differences are between object oriented languages and procedural languages. Once you understand that, the rest is just syntax. In the end they are all about logic trees and loops.

 

[Isaac]

. I think this zombie cut and paste approach would be a quite common situation with Python particularly.

Yes, this is my point and kind of why I was trying away from it. It seems to be a language where the most common usage is one liners here and there with no programming knowledge.

And I would rather add.net programming to my resume then Python programming. But it looks like I may have to acquiesce to the demands..