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.

1 Answer 1


The bits are copied into the database where you call CREATE ASSEMBLY or ALTER 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.

  • Thanks very much. That makes a lot of sense. I scripted the assembly and did see a long binary value like this: CREATE ASSEMBLY [AlphaShapes] AUTHORIZATION [dbo] FROM 0x4D5A900003000000 ... WITH PERMISSION_SET = SAFE It was about 19000 characters long, which sounds roughly right for a 10K dll.
    – andyb
    Jun 27, 2012 at 16:30
  • @andy: Yep. One other advantage of having the bits directly in script is that in a deployment scenario, you don't have to rely on the file system, which is great. Also (aside), you can use backticks (`) to delimit code both in answers and comments.
    – Jon Seigel
    Jun 27, 2012 at 16:34

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.