January 20, 2015 at 9:36 am
I tend to agree with Solomon here. The .NET tools are widely known, though perhaps not by DBAs. This is advanced tooling, and rather than have MS build something else, I think the SQLCLR and .NET are fine.
January 20, 2015 at 10:01 am
It seems I disagree with both of you on all points then.
These are my most critical reasons for doing so:
The target audience are the database people, not the code people (yet only code- or cross-field people can currently create the assemblies).
DBA's have much fewer languages to deal with (nullifies the language argument).
DBA's think more in data and less in code and are more likely to have proper understanding how the code will be used (most programmers do not and would break things).
The C# language (likely other .NET languages too) is ill suited for database integration
This is the result of the type and memory system mismatches between them and SQL Server (it is not true integration, more like grafting).
What SQL lacks out of the box is well performing procedural code and grafting languages and run-times on top that are designed with other things in mind is just lazy.
The result is poor matching interfaces with loss of information in transition and much too complex code for what it does (it lacks problem domain focus).
It is somewhat usable, but that is about all the praise I can give this effort from Microsoft.
January 20, 2015 at 10:03 am
I'll show my ignorance here -
Can we invoke PowerShell directly from a SS object (procedure/UDF)? I know that PS can access SS objects such as from SS Agent but I wasn't sure if it was possible to actually invoke PS within a SS object process. Would this be a solution to "yet-another-Sql-dialect"?
Perhaps this is all a problem of security and of DB performance. If we allow execution of scripts/.NET assemblies/DLLs/etc. within the SS instance(s) then we are taking resources away from the DB server itself and impacting security constraints. I'm guessing that the correct paradigm is to move non-DB operations to separate servers and use some nice inter-process communications mechanism (NotificationServices?) to handle messaging.
I was coming from an environment where the DB server had a lot of business rules (and some presentation formatting) built within the objects. This environment tends to treat SQL as the driving engine and wants to have programming/events handled at the DB level.
In my understanding, in a better world, the application calls the business and presentation layers and underneath the DB objects. In my world it was somewhat upside-down.
January 20, 2015 at 4:11 pm
peter-757102 (1/20/2015)
The target audience are the database people, not the code people (yet only code- or cross-field people can currently create the assemblies).
I disagree. In order for this to be truly useful, the target audience has to be the cross-field folks. (more on this in a moment).
DBA's have much fewer languages to deal with (nullifies the language argument).
No, it does not nullify the "too many languages" concern. If this were not an issue, everyone would already know, at the very least, PowerShell, if not also MDX, BIML, etc.
DBA's think more in data and less in code and are more likely to have proper understanding how the code will be used (most programmers do not and would break things).
So this is part 2 of why the target audience is really the cross-skilled folks. DBA's do indeed think more in terms of data, and that is exactly why they often produce horribly bad code. As you have pointed out, the mind-set of the data world is very much different than the mind-set of the code/app-dev world. Yes, the data folks do know better how a particular function should work and will be used, but that implies nothing about their ability to either make it happen or do a good job of making it happen. So just as "most programmers do not and would break things", the same holds true of the database folks, just in the opposite direction.
The C# language (likely other .NET languages too) is ill suited for database integration
Ok. I don't really see a problem with this. We already have a language, T-SQL, that handles data. What is needed is something to do other stuff. I don't think we are looking for a replacement of T-SQL, like PL/pgSQL replaces SQL in PostgreSQL. And .NET handles complex calculations, accessing external resources, etc quite well, and likely better than any new hybrid language would do. Also, related to this and also reinforcing my earlier point regarding the re-usability of the skill of knowing C# / VB.NET: SSIS allows one to create a custom task using either VB.NET or C#.
SQL# — https://SQLsharp.com/ ( SQLCLR library ofover 340 Functions and Procedures)
Sql Quantum Lift — https://SqlQuantumLift.com/ ( company )
Sql Quantum Leap — https://SqlQuantumLeap.com/ ( blog )
Info sites — Collations • Module Signing • SQLCLR
January 20, 2015 at 4:15 pm
Personally, I think that SQL Server (set based RDBMS) is a fairly focused product. Yes, SQL in all its flavors is a language, but it have a specific purpose. It has lots of add-ons, but that's primarily what they are (add-ons that try and keep up with the other guy and ease integration with other functions)
It seems that SQL CLR is just a way to create more add-ons. Do you want RegEx? CLR! Do you want specialized encryption and hashing algorythms? CLR! Do you want to completely interface with the file system? CLR! Do you want to pump data into SQL Server? SSIS, BCP, CLR, etc Do you want to perform message queueing? Service Broker! By giving us SQL CLR it's opened the door to do a ton of things that we would have to wait for Microsoft to code.
Is it easy for a database developer to create SQL CLR procs correctly and efficiently...not really. It's a different animal. So is SSIS. (which I hate with a passion!) Will a web programmer code up a SQL CLR proc correctly and efficiently? Probably not, since there are SQL Server constraints and constructs that might not be readily known/understood.
It takes somebody willing to bridge a few worlds (DBA, SysAdmin, SQL developer, .net programmer). When that person does it though, it can produce a very nice solution to a problem.
Let's face it, when Microsoft added first class xml support to SQL Server it was welcomed. We could have done it all ourselves with SQL CLR though (we != me).
I think the title of Phil's editorial was definitely intended to create some conversation. I don't know if Microsoft "went wrong". I think it was mostly just a mismatch between marketing promises and expectations and reality.
Just my .02.
February 10, 2015 at 12:40 am
I believe that SQLCLR is a success. A lot of the hype was hogwash and always would be. It is a feature for use when T-SQL isn't the right tool. Which is rare. Or that the functionality is better not being a separate external system. Uncommon.
SQLCLR taking over the world was never on the cards.
Gaz
-- Stop your grinnin' and drop your linen...they're everywhere!!!
April 14, 2015 at 8:19 am
I'm late to this conversation, but I've appreciated the ideas from different quarters. Myself, I've been a slow adopter of CLR, but I've gotten to the point where (a) it fills several niches exceedingly well and (b) I must be at least moderately adept at it, b/c I recently gave a well-received talk on CLR at my local SQL Server group. 😀
Something for thought: Microsoft provides a particular bundled suite of tools with every commercial versions of SQL Server. This suite uses Visual Studio as its development environment, creates code that can be stored in the database engine but not edited there, and requires specialized development experience to use it successfully.
Yet it has experienced widespread adoption in the user community.
What is this suite? SSIS.
So there seems to be no intrinsic reason why tools or suites or packages included as ancillary components to the engine cannot or will not be adopted by the community. I suspect the real reason lies elsewhere, and I believe we DBAs were part of that reason.
How many rants have you read (or, more likely, started to read then bailed) asserting that .NET code has no business being used in an RDBMS? Maybe you've even written one of these rants! The SQL blogosphere was awash with this cant for years, and it left many of us mid-level DBAs scratching our heads with indecision, left to decide to return to tuning our stored procs.
There are certainly aspects of CLR I dislike, but it fills a few important niches for me, and I'm glad the tool exists.
Rich
April 14, 2015 at 3:50 pm
rmechaber (4/14/2015)
...So there seems to be no intrinsic reason why tools or suites or packages included as ancillary components to the engine cannot or will not be adopted by the community. I suspect the real reason lies elsewhere, and I believe we DBAs were part of that reason.
How many rants have you read (or, more likely, started to read then bailed) asserting that .NET code has no business being used in an RDBMS? Maybe you've even written one of these rants! The SQL blogosphere was awash with this cant for years, and it left many of us mid-level DBAs scratching our heads with indecision, left to decide to return to tuning our stored procs.
Hey Rich. Thanks for sharing those thoughts. I would agree that there is no intrinsic reason for lack of adoption. I think the slow adoption rate is related to a combination of the following:
Along these same lines, as I have mentioned before, a lot of people have claimed that SQLCLR presents problems for security and performance. But when pressed to give specific examples, I have only ever been given one: that a runaway Regular Expression can chew up CPU and/or memory. Ok. But that is hardly a reason to not use the entire feature simply due to a rather infrequent problem. However, the mere fact that some people wrote it in blogs meant that quite a few DBAs still won't enable a feature that could greatly help them, all because they read somewhere, years ago, that "CLR is bad" and that was somehow convincing, and there's certainly no time to waste re-evaluating that statement. It is far easier to just remember simple rules such as "no CLR" and "no Cursors" than to learn the nuances of when those things are entirely appropriate.
Take care,
Solomon..
SQL# — https://SQLsharp.com/ ( SQLCLR library ofover 340 Functions and Procedures)
Sql Quantum Lift — https://SqlQuantumLift.com/ ( company )
Sql Quantum Leap — https://SqlQuantumLeap.com/ ( blog )
Info sites — Collations • Module Signing • SQLCLR
December 12, 2018 at 7:12 am
I don't have a problem with Microsoft's technical implementation of CLR functions in SQL Server. It's just that when folks want execute C# or VB.NET from SQL Server queries, it makes me think they are approaching the overall database and application design in a wrong way. Like, perhaps the need to query using RegEx implies your tables are not normalized. Regarding 3rd party CLR functions for encryption / decryption, they suck in terms of performance and maintainability. Microsoft has already provided symmetric functions and also the ability to keep data encrypted from the backend all the way to the client, so why would you need that? As for using CLR functions to do stuff like copying files or create PDF document; that's downright silly. There are so many better options for doing that type of thing (jobs, PowerShell scripts, Service Broker, erc.). I guess some folks just love to code in C#.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
December 12, 2018 at 6:51 pm
Steve Jones - SSC Editor - Saturday, January 17, 2015 11:04 AMI actually think the CLR has worked well. MS used it internally to add things like HierarchyID to the native product (along with Spatial and other items). It gives them flexibility.I see more and more people adding specialized assemblies to their instances for specific things. I'm not quite sure why it hasn't caught on more, perhaps because it does require a degree of knowledge to write efficient assemblies, but I suspect it's more that it isn't really needed in many cases.
Heh... I suspect that it's because the people that don't know much about databases and T-SQL are doing "the work" from front-end code and those that do know a fair bit about databases and T-SQL either don't need to use it very often or don't know enough about .Net to actually write one.
There's also the "stigma" that some DBAs carry against CLR. In the early days when 2005 first came out, I "worked with" a couple of developers that got pretty stupid with SQL CLR. One of them didn't know how to do an "Upsert" in T-SQL and so wrote an SQL CLR to do it and another apparently couldn't be bothered to do any research into how to do a modulus in T-SQL and so wrote an SQL CLR to do it (he also took me to the boss because I refused to allow his SQL CLR to go to production but ran out of the room before I could explain how simple it was in T-SQL). Despite the fact that I was actually thrilled that MS created the likes of SQL CLR, all that put a serious sour taste in my mouth about using SQL CLR. There also the problem of frequently not being allowed to review the source code for SQL CLR. It I can't review it, it's not going in... period.
--Jeff Moden
Change is inevitable... Change for the better is not.
December 13, 2018 at 12:41 am
http://www.sqlservercentral.com/stairway/105855/ answered a lot of concerns for me.
The gap for me was the way that user defined aggregates worked. http://www.sqlservercentral.com/articles/SQLCLR/71942/
I had hoped to make a lot of stats functions available in the DB rather than having to do regular bulk extraction.
I find that SQLSharp does everything I would want to do https://sqlsharp.com
Viewing 11 posts - 16 through 25 (of 25 total)
You must be logged in to reply to this topic. Login to reply