I created a dll in .NET for some custom CLR spatial functions in SQL Server 2008 R2. I sent the dll to a colleague along with a script to deploy it (CREATE ASSEMBLY then create the external functions etc).
She asked whether she needed to keep the file in the same location after it had been registered using CREATE ASSEMBLY. I didn't know, so I experimented by registering an assembly and creating the functions from it in sql server. Then I renamed the dll and restarted sql server. I found that the functions still worked after this so it appears that sql server has loaded the dll in some way and it doesn't need to stay on the file system. What's puzzling, though, is that the table sys.assembly_files does store the original path of the dll. Why would it do that if it isn't relevant?
So I'm not sure my test for this was right. But does anyone know what CREATE ASSEMBLY does with a dll? Does it copy/import it somewhere? Or was my test flawed and sql server does require the original dll to stay in the same location?
Thanks in advance.
Best Answer
The bits are copied into the database where you call
CREATE ASSEMBLYorALTER ASSEMBLY.The path you select in Management Studio is only a convenience for it to read the file and dump out the bits into a
varbinary(MAX)value (hit the Script button in that dialog to see what I mean).This also means that the assembly is "carried" with the database if you do something like backup/restore on another server.