Sql-server – How to update a CLR Assembly without dropping assembly from SQL Server

sql serversql-server-2008-r2sqlclr

How can i update a CLR function (or procedure) assembly dll without having to drop and re-create the assembly in SQL Server (2008 R2)?

As it stands now if i update an assembly (e.g. to add a new function), SQL Server will not honor the updated dll until i drop the assembly:

DROP ASSEMBLY CLRFunctions

Msg 6590, Level 16, State 1, Line 1
DROP ASSEMBLY failed because 'CLRFunctions' is referenced by object 'NormalizeString'.

But before i can drop the assembly, i must first drop all functions that reference it:

DROP FUNCTION NormalizeString
DROP FUNCTION RemoveDiacritics
DROP FUNCTION RemoveCombiningDiacritics
DROP FUNCTION CombineLigatures
....
DROP FUNCTION PseudolocalizeArabic

And then i can drop the assembly:

DROP ASSEMBLY CLRFunctions

Now i have to "create" the assembly:

CREATE ASSEMBLY CLRFunctions FROM 'c:\foos\CLRFunctions.dll';

And now i have to hunt the declaration of all the UDF's that were registered before i deleted them.

i would rather update an assembly, and have SQL Server begin using it.


Update: i randomly tried DBCC FREEPROCCACHE to force a "recompile", but SQL Server still uses the old code.

Update: i deleted the assembly dll CLRFunctions.dll, and SQL Server is still able to run the code (without code that should be impossible).

Best Answer

I think you're looking for alter assembly. From BOL:

If the FROM clause is specified, ALTER ASSEMBLY updates the assembly with respect to the latest copies of the modules provided. Because there might be CLR functions, stored procedures, triggers, data types, and user-defined aggregate functions in the instance of SQL Server that are already defined against the assembly, the ALTER ASSEMBLY statement rebinds them to the latest implementation of the assembly. To accomplish this rebinding, the methods that map to CLR functions, stored procedures, and triggers must still exist in the modified assembly with the same signatures. The classes that implement CLR user-defined types and user-defined aggregate functions must still satisfy the requirements for being a user-defined type or aggregate.

One of the examples on the same page seems like it'd do the trick:

ALTER ASSEMBLY ComplexNumber 
FROM 'C:\Program Files\Microsoft SQL Server\90\Tools\Samples\1033\Engine\Programmability\CLR\UserDefinedDataType\CS\ComplexNumber\obj\Debug\ComplexNumber.dll'