MySQLCLRFunctions

Free SQLCLR functions tested on SQL Server 2019

These are simple functions I've written in order to avoid spending $500 for a two year license (https://sqlsharp.com/full/) of SQL#. For one, the product hasn't been updated in 2 years, and that's not very cool for a pay product.

FYI, warning, buyer beware on my stuff: I use UNSAFE assemblies, because half the reason to use SQLCLR is to get to functions that are not in SQL Server already, which is where the safe stuff is.

Another bug I have with SQL# is that their names are overly precious. They will name a function SQL#.string_Is_BEGINNING_WITH whereas I call it StartsWith, which is not coincidentally the same as the C# method. I try to align my names with C# when the functionality aligns. Less to remember.

I also have less arguments on functions than SQL#. SQL Server does not support optional arguments on functions, and so these are annoying to have to fill out. Also, I forget what they are supposed to be, and I don't recall their documentation telling me if using DEFAULT will work. Rather than every possible argument under the sun, I do it the old fashion way: I make a new function. For example, hypothetically, I would do this:

For reduced maintenance, I pass everything as NVARCHAR(MAX) as well as return NVARCHAR(MAX). Speed is not my main problem. The main problem is that SQL Server functions are severely lacking and new functions are added every third decade. STRING_AGG is great, but a SQLCLR function can go back to at least 2012.

I do suspect that there may be memory allocation issues with this design, and so I may come up with a generative way to make VARCHAR(8000) or NVARCHAR(4000) clones. Due to the way SQL Server pre-allocates memory, it may be even better to support smaller sizes. Not really sure.

If it's possible to think of a logical algorithm that can easily be described and understood, and there's no confusion about what to expect in the output, then it's worth being a function.

For instance, I have a function called AnyOneOfTheseIsInThose, or something like that. It is for when I to know if there is a non-empty intersection of a list of values that I don't want to stuff into tables. It answers the simple question: Are any of these items in this other list?

To pass in lists, I use delimited strings, but I usually have an argument for the separating string. SPLIT_STRING from Microsoft, and most home-grown SQLCLR splitters seem to think all splitting is based on single characters. I support the string for the cases that come up.

Structure or class organization, namespaces in other words:

I have a bunch somewhere, and I need to add more.