Juval has been and gone – Ian Griffiths is on the way

It was great having Juval Lowy in Australia for our Industrial Strength WCF Masterclass. I’m still kicking myself that I couldn’t attend it. The reports I heard from attendees were wonderful.


Now there’s another wizard on the way. Ian Griffiths will be heading over to present our Industrial Strength WPF Masterclass. Each of these series of events are unique experiences to get to spend time with thought-leaders in the industry. Clearly, they’re not for everyone but if you feel that WPF is in your future and you want to have a very solid understanding of it, now’s the best chance to get it.

SQL CLR needs server-side compiles

THE PROBLEM


 


The current model in which a developer generates an assembly and then passes it to a DBA and says “trust me” is failing to allow for the typical dynamics involved. To the DBA, this assembly is like a black box and completely ignores the politics often involved in this situation.


 


Also, the current model ties databases directly to versions of the .NET Framework. While there is currently only a single supported version of this now, this won’t remain the case and will lead to a versioning mess in the future. DBA’s won’t want to hear about things like publisher policies, etc. or whatever will be required to deal with it.


 


Further, the current model requires a DBA to be comfortable with Visual Studio, even when implementing what would otherwise be simple managed code functions. This is harming SQL CLR adoption.If the managed code source was processed when scripting a database instead of the assembly binaries, much of these issues would be avoided.


 


PROPOSED SOLUTION


 


The simplest way would be to allow:


 


CREATE ASSEMBLY myassembly FROM CLRCOMPILE(‘C#’,’code for the assembly goes here’)


 


This would provide for readable database scripts (instead of the current hexadecimal gibberish that DBA’s are confronted with) and provide a high degree of .NET framework version independence and interoperability. For example, a database with framework version 4.1 could be scripted and recreated on a server with framework version 4.0 (numbers picked as examples only :-)). As long as the required classes were present, it wouldn’t matter what version of the framework was present.


 


An even better option would be to avoid the need for entire assemblies for simple situations. Even though an assembly is the unit the database engine has to work with, it doesn’t have to be the unit the developer or DBA works with necessarily.


 


I believe DBA’s would be much more likely to accept CLR code if they could execute a script like:


 


CREATE FUNCTION somefunction FROM CLRCOMPILE(‘VB’,’code for just the function goes here’)


 


This could be achieved by associating a “default assembly” with each database. When items like functions were specified on their own rather than with an entire assembly, they could be added to the “default assembly”. (This could be extended further in the future to support multiple assemblies like this).


 


The one remaining issue would be how to implement assembly references. I think that the default assembly could already have the references that are the default ones when you currently start a database project in Visual Studio. Other references required by the function (or procedure or type, etc.) could be passed like this:


 


CREATE FUNCTION somefunction AS CLRCOMPILE(‘C#’,’function code goes here’, ‘extra references required go here’)


 


alternately, something like:


 


ALTER ASSEMBLY default ADD REFERENCE ‘reference goes here’


 


Given the command line compilers are already present on the systems, these shouldn’t be onerous tasks but could drastically improve the usefulness (and future maintainability) of SQL CLR.


 


WHERE TO VOTE TO TRY TO MAKE IT HAPPEN


 


https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=265266