Thursday, July 29, 2004 11:11 AM nielsb


In an earlier post I discussed ALTER ASSEMBLY to an extent, and Angel and I had a discussion regarding a post he wrote about deployment of UDT's via Visual Studio.

In my post I wrote you didn't have to drop SQL objects created against methods in the original assembly if you use ALTER ASSEMBLY and follow certain rules as per the BOL (SQL Server 2005 Books On Line). However the BOL is not entirely correct and in our book in Chapter 2 we probably could have covered it a bit more. The reason we didn't was because ALTER ASSEMBLY wasn't fully implemented at the time of writing. So let's look at ALTER ASSEMBLY and various types of SQL objects created against methods in a CLR assembly.

CLR methods catalogued as Stored Procedures: As long as the method signature doesn't change ALTER ASSEMBLY works fine, and it will re-bind against the latest implementation. One caveat though: At the moment it seems to be a problem with CLR methods with ref or out params. I.e. methods catalogued as procs with out params.

CLR methods catalogued as Triggers: Works as stated and during ALTER ASSEMBLY it'll re-bind against the latest implementation.

CLR methods catalogued as UDF's: Now it starts to get interesting. Unless you have a direct or indirect schema bound reference for a persisted table column, index or check constraint against the UDF, it works exactly like a procedure or trigger. However if, for example, the UDF is part of a column definition where the column is created as PERSISTED, you'll get an error when executing ALTER ASSEMBLY. The error says there exists a dependency against the function, and you need to run ALTER ASSEMBLY with the UNCHECKED DATA option (more about UNCHECKED DATA later):


2FROM 'assembly_path'

Notice that you'll get the error regardless if there exists any data etc, or if you have changed the CLR implementation of the method in question.

CLR classes catalogued as UDT's: This depends upon the Format of the UDT. Remember from Angels posts (here and here), how a UDT can either be Format.Native (where SQL Server is reponsible for the storage of the data) or it can be Format.UserDefined (where the developer is responsible for defining how the data should be stored).

Format.Native: Unless you do any changes to the type, and you do not have any functions using the type against persisted columns, ALTER ASSEMBLY works. In other words; you can have tables with columns of that particular type and ALTER ASSEMBLY works OK, if you do  not change the type. However if you do changes to the type, ALTER ASSEMBLY will fail.

If you don't have any tables with columns of that particular type and no functions using the type against persisted columns you can change the type as you wish and ALTER ASSEMBLY will work.

If you have functions using the type against persisted columns, you need to use UNCHECKED DATA for ALTER ASSEMBLY to work.

Format.UserDefined: If you have tables with columns of the type, regardless if you do any changes you need to run ALTER ASSEMBLY with the UNCHECKED DATA option. If you do changes, it will still work if you use UNCHECKED DATA!

For functions using the type it works the same way as with Format.Native.

Finally UNCHECKED DATA: During ALTER ASSEMBLY, SQL Server does extensive per-row validation of the data. This validation can be costly timewise so by using UNCHECKED DATA the user can tell SQL to avoid this costly validation. The danger is that data integrity can be lost and therefore SQL gives you a warning when you run ALTER ASSMBLY ... WITH UNCHECKED data.

I hope this makes some sort of sense. If you want code examples that shows some of these issues, please let me know. Comments are always welcome!!


No Comments