Get started Bring yourself up to speed with our introductory content.

CLR assemblies in SQL Server 2005

If you're familiar with ADO.NET, you may wonder exactly how CLR database objects connect to the database. Find out in this chapter excerpt, along with how to code to create a SQL Server assembly.

SQL Server .NET data provider

If you're familiar with ADO.NET, you may wonder exactly how CLR database objects connect to the database. After all, ADO.NET makes its database connection using client-based .NET data providers such as the .NET Framework Data Provider for SQL Server, which connects using networked libraries. While that's great for a client application, going through the system's networking support for a database call isn't the most efficient mode for code that's running directly on the server. To address this issue, Microsoft created the new SQL Server .NET Data Provider. The SQL Server .NET Data Provider establishes an in-memory connection to the SQL Server database.


After the coding for the CLR object has been completed, you can use that code to create a SQL Server assembly. If you're using Visual Studio 2005, then you can simply select the Deploy option, which will take care of both creating the SQL Server assembly as well as creating the target database object.

If you're not using Visual Studio 2005 or you want to perform the deployment process manually, then you need to copy the .NET DLL to a common storage location of your choice. Then, using SQL Server Management Studio, you can execute a T-SQL CREATE ASSEMBLY statement that references the location of the .NET DLL, as you can see in the following listing:


The CREATE ASSEMBLY command takes a parameter that contains the path to the DLL that will be loaded into SQL Server. This can be a local path, but more often it will be a path to a networked file share. When the CREATE ASSEMBLY is executed, the DLL is copied into the master database.

If an assembly is updated or becomes deprecated, then you can remove the assembly using the DROP ASSEMBLY command as follows:


Because assemblies are stored in the database, when the source code for that assembly is modified and the assembly is recompiled, the assembly must first be dropped from the database using the DROP ASSEMBLY command and then reloaded using the CREATE ASSEMBLY command before the updates will be reflected in the SQL Server database objects.

You can use the sys.assemblies view to view the assemblies that have been added to SQL Server 2005 as shown here:

SELECT * FROM sys.assemblies

Since assemblies are created using external files, you may also want to view the files that were used to create those assemblies. You can do that using the sys.assembly_files view as shown here:

SELECT * FROM sys.assembly_files

The previous tip is from "Developing CLR database objects: 10 tips in 10 minutes," excerpted from Chapter 3 of the book "Microsoft SQL Server 2005: A Developer's Guide" written by Michael Otey and Denielle Otey, courtesy of McGraw-Hill Publishing.

Developing CLR database objects

 Home: Introduction
 Tip 1: CLR architecture
 Tip 2: CLR assemblies SQL Server 2005
 Tip 3: Creating CLR database objects
 Tip 4: CLR stored procedures
 Tip 5: User-Defined Functions
 Tip 6: CLR triggers
 Tip 7: User-Defined Types
 Tip 8: CLR aggregates
 Tip 9: Debugging CLR database objects
 Tip 10: .NET database object security

Dig Deeper on Microsoft SQL Server 2005

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.