Hi Tim. I hope this somehow notifies you as I am not sure how else to get in contact with you. I had posed a question regarding SEDE to the Contact Us folks and they referred me to you.

@Tim-stone: is this the way to notify someone?

@TimStone : maybe I used the wrong userid?

hey

I actually lost network connectivity right as you invited me, sorry about that.

Anyway, what can I help you with?

thanks for responding. I haven't used chat much so forgive me if I miss some etiquette or common practice.

high level is that I proposed an addition to SEDE that I am not sure how to best handle.

details:

I am the creator of a SQL CLR library of functions to extend what can be done in T-SQL. SQLsharp.com

There are two versions: one for Free and the Full version is paid-for.

I had proposed a) including the Free version in SEDE, and b) including SEDE-specific functions (in the Free version, obviously).

This came up when someone on SO had asked about reformatting some mark-down in the data into actual HTML for their report.

It would be trivial to do in C#. but awkward in T-SQL.

The free stuff is basic string formatting, Regular Expressions, some math stuff, etc..

I didnt want to just throw something into the GitHub project without talking this out first

as there are 2 ways of going about this..

The idea that I was told (and why I needed to contact you) was that whatever is done for the hosted SEDE needs to also work for the open source downloads

Ah, okay. The Contact Us people were a bit off, then. This isn't actually a question for me, but for @nickcraver since it would need his blessing first.

and that is fine...my goal is to allow users of SEDE to get more out of that data without doing super complicated T-SQL.

ok

I can contact him as well.

He's a bit busy playing dad to a newborn right now though, so I'm not sure if we'll get a quick response.

should I at least explain the two methods so you understand what I am talking about?

I know the feel...have 3 myself :-)

youngest is 4 days from turning 2...

havent slept much in the past 3 years

Sure, go ahead. He can read everything here once he has a chance anyway.

quick info to not waste your time:

oh ok..

A) SQL# could be added via the installer SQL script that can be downloaded from the site and added to the GitHub repository in the folder with the other SQL scripts....

only change to make would be to fill in the Database name..that's it..

then the script just gets run along with the other sQL scripts as naturally done as part of any install or update...

These are 100% stand-alone functions and procedures so there are NO dependencies between any SEDE code and this code.

Right

The only dependencies would/could be in the queries that people write if they reference the functions.

Method B)

Since SQL CLR Assemblies are backed up with the database, depending on how the monthly updates are distributed to folks, if an actual SQL backups (instead of CSV export), then it would naturally already be there with nothing more for anyone to do :)

and since the library is freely available, nothing is stopping anyone from downloading and installing a newer version of SQL# than what comes with SEDE and again, nothing would break...those folks would just have some additional newer stuff.

But I think lots of folks would appreciate being able to do RegEx, etc.

And all of this works while the Assemblies are set to SAFE..

Nothing EXTERNAL_ACCESS or UNSAFE..

You're talking about the data dumps? Those are actually XML files, so the first option would be preferable in that case.

So as far as I can tell, not messy on technical, legal, or logistical fronts.

k. That helps.

So yes, I or someone else could just add the single SQL script, already edited with whatever DB name you currently have in the other scripts, or none at all if the current DB is assumed

I mention the SAFE permission_set since some folks might have concerns about doing EXTERNAL_ACCESS on the hosted SEDE. But I can't think of any reason those functions would be needed in that context anyway.

I seem to recall seeing some questions about how to do things like call other procs or other more complicated queries that there were work around for (temp stored procs, etc) that are made much easier via SQL#.

And again, I was planning on including SEDE-specific functions for free.

Yeah, that all makes sense. It's mostly just a question of how Nick feels about it, since he has stewardship over the project. I can certainly see the value in what you're proposing from the general perspective of making Data Explorer more powerful out of the box, but I also share his concerns about performance et al on the actual hosted instance.

The mark-down formatting idea was a result of this question and my answer there (which makes less sense once the OP informed us of what he was trying to do): stackoverflow.com/questions/25048739/…

It would not pose a performance issue on the hosted (or even private) systems.

And depending on what work-arounds are being done to handle CURSOR like queries, it could actually lead to performance improvements

So, that is the overall idea. I am always available to answer questions regarding implementation, concerns regarding performance, etc.

SQL# even includes UTC to Server Local Time conversion functions. I assume the data is stored in UTC so this would make it easier to people running it locally to get data in their timezone that is Daylight Savings-aware :).

Since the TIMEZONEOFFSET datatype in T-SQL is not Daylight Savings-aware.

Sorry, I am just now reading the post you linked above regarding the RegEx / CLR question for SEDE.

Ok, I have read the conversation in the comments there. Regarding the concern about performance, all I can say is that it is premature to assume that there would be issues. Performance is something that can be profiled and tested for.

There are certainly operations for which studies have been done and CLR approaches (such as with splitting delimited strings) are more efficient than T-SQL approaches.

Well, in that specific case of allowing user-defined regexes to be executed, it's certainly possible to create a very performance-detrimental statement. With more purpose-built functions that's obviously less of an issue, since they have better defined performance characteristics. But I'll let Nick defend his position, I don't want to put words in his mouth. :P

Understood. I will just say that as MartinSmith pointed out in those comments, it is just as easy to write horribly inefficient T-SQL code. And Nick then mentioned that GC doesn't run on the same schedule as native .Net apps, but I have only ever heard of one problem in that regards where GC didn't run when the server was experiencing memory pressure, but that was in 2008/R2 or 2012 SP1 and there was a Cumulative Update that fixed it that I assume was included in SP2.

And while not exactly scientific proof, I will add that I have had many thousands of downloads over the years (RegEx is usually high on the list of what people are looking for) and have yet to receive a single complaint about performance being degraded.

Lastly, and just to have it documented here, the linked post on Meta was from back in 2010 and SQL Server 2012 included two performance enhancements for CLR functions that might tip the scale for people who have not looked into it since 2005 / 2008 days.

Yeah, I think the concern is more over people doing irresponsible things either intentionally or unintentionally, not so much that there's an inherent performance issue.

But we're allowing people to run arbitrary SQL code, so of course that's always a risk regardless.

I understand. I can just say a) there is no inherent reason to assume performance problems, b) as you just sad, ad hoc T-SQL is just as risky when people can do a LIKE '%...' WHERE clause on a table that is CROSS JOINed to itself ;-) , and c) if it does become a problem then it can be turned off.

Keep in mind also that streaming TVFs (and I tried to make most of the SQL# TVFs to be streamed) are much less tempdb intensive as they release their results as each row is created instead of filling up a table variable.

I realize that people cannot write TVFs in SEDE, but that forces them to do table variable and temp tables that increases contention on tempdb whereas the SQL# stuff might be a way to avoid that usage and reduce that contention..

Granted, for pattern matching that can be done via CHARINDEX / PATINDEX, those will be more efficient than a CLR RegEx (in most cases). But for any sufficiently complicated pattern, a single RegEx will be more efficient than multiple queries or case statements within case statements within CROSS APPLY operators to only maybe possibly get the flexibility of an intermediate-level RegEx. I am not trying to be pushy here, just trying to put everything in its proper context :).

As far as perceived security risk goes, there really are none, especially in the context of assemblies with a PERMISSION_SET of SAFE. I wrote an article that walks the issue through, step-by-step, across various scenarios: sqlservercentral.com/articles/SQLCLR/109905 (free registration required).