When working with SQL Server CLR assemblies, developers often encounter a frustrating limitation: you can't simply update the DLL and have SQL Server recognize the changes. The current workflow requires:

-- 1. Drop all dependent functions
DROP FUNCTION NormalizeString
DROP FUNCTION RemoveDiacritics
-- ...and all other functions

-- 2. Drop the assembly
DROP ASSEMBLY CLRFunctions

-- 3. Recreate the assembly
CREATE ASSEMBLY CLRFunctions FROM 'C:\path\to\updated.dll'

-- 4. Recreate all functions
CREATE FUNCTION NormalizeString...

Fortunately, SQL Server provides the ALTER ASSEMBLY command to update assemblies without complete recreation:

-- Update assembly with new version
ALTER ASSEMBLY CLRFunctions
FROM 'C:\path\to\updated.dll'
WITH PERMISSION_SET = SAFE; -- Or EXTERNAL_ACCESS/UNSAFE

When using ALTER ASSEMBLY, keep these points in mind:

• Version changes in strongly named assemblies may require additional steps
• Method signatures cannot change - only implementations can be updated
• New methods can be added but existing signatures must remain compatible
• Permission set changes may require dropping and recreating the assembly

For assemblies with dependencies, use the ADD FILE clause:

ALTER ASSEMBLY CLRFunctions
FROM 'C:\path\to\updated.dll'
ADD FILE FROM 'C:\path\to\dependency.dll'

Here's a complete workflow for updating a CLR function implementation:

-- 1. Check current assembly version
SELECT name, version, permission_set_desc 
FROM sys.assemblies 
WHERE name = 'CLRFunctions'

-- 2. Update the assembly
ALTER ASSEMBLY CLRFunctions
FROM 'C:\updated\CLRFunctions_v2.dll'

-- 3. Verify the update
SELECT name, version FROM sys.assemblies
WHERE name = 'CLRFunctions'

-- 4. Test the updated functions
SELECT dbo.NormalizeString('Test Input')

If you encounter errors during the ALTER operation:

-- Check for active connections using the assembly
SELECT session_id, login_time, host_name, program_name
FROM sys.dm_exec_sessions
WHERE session_id IN (
    SELECT session_id 
    FROM sys.dm_exec_requests
    WHERE sql_handle IN (
        SELECT sql_handle 
        FROM sys.dm_exec_query_stats
        CROSS APPLY sys.dm_exec_sql_text(sql_handle)
        WHERE text LIKE '%CLRFunctions%'
    )
)

-- Then terminate problematic sessions if necessary
KILL [session_id]

When working with SQL Server CLR integrations, developers often face a frustrating limitation: you cannot simply overwrite an existing assembly with an updated version. The server maintains strict dependencies between assemblies and their referenced objects, requiring a complete teardown and rebuild process.

-- Typical error when trying to drop an in-use assembly
DROP ASSEMBLY MyCLRAssembly
-- Error: DROP ASSEMBLY failed because 'MyCLRAssembly' is referenced by object 'MyFunction'

SQL Server enforces these dependencies to maintain database integrity. The dependency chain looks like this:

1. CLR functions/procedures depend on their parent assembly
2. The assembly depends on its physical DLL file at creation time
3. SQL Server caches the assembly contents after loading

While there's no direct UPDATE ASSEMBLY command, these approaches can streamline the process:

Method 1: Scripted Redeployment

Create a deployment script that handles the entire process:

-- Generate drop statements for all dependent objects
SELECT 'DROP FUNCTION ' + QUOTENAME(SCHEMA_NAME(schema_id)) + '.' + QUOTENAME(name)
FROM sys.objects 
WHERE type_desc LIKE 'CLR%' AND assembly_id = ASSEMBLYPROPERTY('MyCLRAssembly', 'id');

-- After drops, rebuild everything
DROP ASSEMBLY MyCLRAssembly;
CREATE ASSEMBLY MyCLRAssembly FROM 'C:\path\to\updated.dll';

-- Recreate all functions using their original definitions
CREATE FUNCTION dbo.MyFunction(...) 
AS EXTERNAL NAME MyCLRAssembly.[Namespace.Class].Method;

Method 2: ALTER ASSEMBLY with UNCHECKED DATA

For minor updates that don't change method signatures:

ALTER ASSEMBLY MyCLRAssembly 
FROM 'C:\path\to\updated.dll'
WITH PERMISSION_SET = SAFE, UNCHECKED DATA;

Important limitations:

• Cannot add new methods or change signatures
• Requires ALTER permission on the assembly
• UNCHECKED DATA may be needed for persistent data structures

For production systems, consider a versioned approach:

-- Create new version with distinct name
CREATE ASSEMBLY MyCLRAssembly_v2 FROM 'C:\path\to\v2.dll';

-- Update functions to point to new version
ALTER FUNCTION dbo.MyFunction(...)
AS EXTERNAL NAME MyCLRAssembly_v2.[Namespace.Class].Method;

-- When stable, drop old version
DROP ASSEMBLY MyCLRAssembly;

For frequent updates, automate the process using:

• SQLCMD variables in deployment scripts
• PowerShell scripts that extract existing function definitions
• SSDT projects with pre/post deployment scripts

-- Example PowerShell to extract function definitions
$query = @"
SELECT OBJECT_DEFINITION(object_id) 
FROM sys.objects 
WHERE type_desc LIKE 'CLR%' AND assembly_id = ASSEMBLYPROPERTY('MyCLRAssembly', 'id')
"@

Invoke-Sqlcmd -Query $query -ServerInstance ".\SQLInstance" | Out-File "functions.sql"