Wednesday, July 28, 2004 5:13 AM nielsb


Earlier today I mentioned briefly ALTER ASSEMBLY. AA is IMHO both cool and somewhat dangerous. The coolness is in the fact that it allows you to redeploy an assembly without having to drop the SQL objects bound to that assembly beforehand, if you haven't changed signature of your existing methods. So you may have a method in your assembly looking like so:

1public static int Adder(int x, int y) {

2 return x + y;

Your have also deployed the assembly to SQL Server, using CREATE ASSEMBLY and created a SQL Function against the Adder method through CREATE FUNCTION. You now add a new method to your assembly, and you want to re-deploy the assembly. In Beta 1 you had to drop the existing function, drop the assembly and then CREATE ASSEMBLY again, plus ofcourse CREATE FUNCTION etc. In Beta 2 you can instead call ALTER ASSEMBLY:


2FROM 'path_to_assembly'

this without having to drop existing functions etc.  After ALTER ASSEMBLY you just CREATE FUNCTION/PROCEDURE/TRIGGER against the newly added methods. So what about the existing methods, what happens to them?

Well here is the dangerous part, it turns out that SQL Server re-binds existing UDF's/sp's/triggers against the methods in the new assembly, which can cause side effects if the developer doesn't know what he/she is doing. In other words, the developer may have changed the original Adder method above to something like:

1public static int Adder(int x, int y) {

2 return x + y + 100;

In this scenario, SQL Server would happily go along and re-bind the method. This may have been intended - in which case it is goodness - but it might have been un-intended and that's where the danger comes in.

Anyway, there are more things to discuss about ALTER ASSEMBLY, and I'll do that later today or tomorrow.


No Comments