0

Is there any database technology comparable to the SQL CLR?

Specifically, one that allows me to code high-performance routines in a common programming language like C# with shared memory (e.g. static variables) that can be called directly from within an SQL statement?

Triynko
  • 18,766
  • 21
  • 107
  • 173
  • Postgres supports many different languages for stored functions like Perl, Python or JavaScript - and you can also code them in C if you want. Not sure what you mean with "shared memory" though. –  Aug 29 '14 at 21:20
  • You mean a technology in a different database server, right? – John Saunders Aug 29 '14 at 21:33
  • Yes, similar technology in a different database server. The idea is to be able to leverage a large framework like .NET or Java and write functions that can be used directly in SQL. With "shared memory" I basically mean static variables. If I call an SQL CLR method, it can access static variables like any normal .NET application, so I could increment a counter for example or look something up in a runtime cache when a function is called. – Triynko Aug 29 '14 at 22:00
  • I was considering porting my application to a different database server, but I just can't find any other server that has anything even remotely close to the capabilities and performance of the SQL CLR. – Triynko Aug 29 '14 at 22:02

1 Answers1

0

Not really.

More specifics about what you are trying to achieve would help.

There may be a flaw in your design / approach.

If you need to code high-performance, functional routines, they should probably not be embedded in your database. You should be implementing a middle-tier and placing your routines there instead.

user3561406
  • 53
  • 8
  • There are no design flaws, the CLR is just simpler and faster to do things than TSQL, as well as doing things that TSQL can't do, or would involve reinventing the wheel in TSQL. – Triynko Aug 29 '14 at 21:19
  • For example, I have a check-constraints that call a Boolean CLR routine, which ensures the field matches a regular expression as it is committed to the database, by any means at all, either through an SQL update or modifications using SQL Server Management Studio. I have automated deployment mechanism that emits low-level MSIL code for such functions that simply attempt to cast the string to a specific data class such as "EmailAddress" or "Username", which all inherit from a RegexConstrainedStringClass. – Triynko Aug 29 '14 at 21:26
  • In essense, a single .NET/CLR class enforces data type rules not only in the application that consumes the data, but within the database itself as well. It's functionally impossible for bad data to enter the database or the system, since such type-checking check constraints, when active and trusted, prevent bad modification even through something like SSMS. Furthermore, some types like EmailAddress can be broken down for sorting, for example based on only that preceding the "@" sign, which may be doable in TSQL, but others are much better handled through .NET code. – Triynko Aug 29 '14 at 21:28
  • Furthermore, the RegexConstrainedString class is immutable and actually caches the instances, so it actually performs a static cache lookup while trying to create an instance of the type, which is even faster. I can run, for example, a regex that makes sure that some text thousands of characters long contains only ASCII characters or omits a specific reserved character, and run that on 2 million database rows in a matter of a couple seconds... AS A CHECK CONSTRAINT. Such a thing would be impossible with TSQL, if not astronomically slower. – Triynko Aug 29 '14 at 21:32
  • If a rule changes, I just redeploy the assembly, and SQL Server automatically says "check constraints are no longer trusted". I then reactive them, it checks all the data in just a few seconds, and I'm back in business. I've just never seen anything like it. It's one of those things that I just can't believe no other database has. The ability to leverage a large framework like .NET (or at least a large part of it), and use it directly within TSQL is just incredible, IMO. – Triynko Aug 29 '14 at 21:34
  • They are OK examples, but the fact nevertheless remains that you could do all of this in a middle-tier, and if you'd adopted a design that placed that logic in a middle-tier, you'd avoid a significant amount of development pain over the long-run, IMO. I'm not saying there is no place for SQL CLR - for niche scenarios where small elements of business logic are very tightly bound to data itself, e.g. data-manipulation routines, it can certainly be tidy enough. Where a system is being designed from the ground up I'd be reluctant to lean heavily on it. Most business logic belongs in the mid-tier. – user3561406 Aug 29 '14 at 23:33
  • Noting for clarity that mid-tier does NOT mean T-SQL. I am not advocating trying to write functional routines in T-SQL for one moment. – user3561406 Aug 29 '14 at 23:37
  • Well, it's not business logic at all. Data type constraints are an integral part of the data and should be enforced right in the database. Prior to the SQL CLR, that was not really possible, aside from rudimentary checks like numeric size. If a value is supposed to be an integer between 1 and 100, that's fairly easy to create a TSQL check constraint for it, but to limit a string to exclude certain characters... it's simply hands-down faster to implement the check as a CLR routine. – Triynko Aug 30 '14 at 04:46
  • Field types like string, int32, int64 are just too generic. By enforcing stronger data types right in the database, I was actually able to eliminate tons of unnecessary checks in the middle-tier, especially null value checks. It was really an interesting side-effect that null values were rendered unnecessary altogether. So there is a class for each type of data like "Username", and instead of being a string that needs checked, it is cast directly to that class, and is guaranteed to be valid within the system, when it first enters the system through a webform or something. – Triynko Aug 30 '14 at 04:49
  • The really nice thing is that these classes are written once in C#, and enforce the data types in the application as well as the database, without having to replicate any code. The database and the application literally use the same assembly (dll). – Triynko Aug 30 '14 at 04:51
  • Another scenario is storing unstructured JSON data in a field, such as a save-state for a lesson and page in online educational software. To run a report on who completed a page in less than 5 minutes, for example, I would have to extract a variable from within that JSON page-state string, and it's orders of magnitude faster to use an SQL CLR function to extract the member for comparison, rather than attempt to stream all the data to some middle tier for processing. A query like "select UserID from PageStates where SQLCLRParseJSON(FieldName,'object.membername') = 'checkvalue';" is fastest. – Triynko Aug 30 '14 at 05:10