Microsoft SQL Server 2005 Developer’s Guide- P5:This book is the successor to the SQL Server 2000 Developer’s Guide, which was extremely successful thanks to all of the supportive SQL Server developers who bought that edition of the book. Our first thanks go to all of the people who encouraged us to write another book about Microsoft’s incredible new relational database server: SQL Server 2005. | Chapter 3 Developing CLR Database Objects 79 CLR Architecture The .NET Framework CLR is very tightly integrated with the SQL Server 2005 database engine. In fact the SQL Server database engine hosts the CLR. This tight level of integration gives SQL Server 2005 several distinct advantages over the .NET integration that s provided by DB2 and Oracle. You can see an overview of the SQL Server 2005 database engine and CLR integration in Figure 3-1. As you can see in Figure 3-1 the CLR is hosted within the SQL Server database engine. A SQL Server database uses a special API or hosting layer to communicate with the CLR and interface the CLR with the Windows operating system. Hosting the CLR within the SQL Server database gives the SQL Server database engine the ability to control several important aspects of the CLR including Memory management Threading Garbage collection The DB2 and Oracle implementation both use the CLR as an external process which means that the CLR and the database engine both compete for system resources. SQL Server 2005 s in-process hosting of the CLR provides several important advantages over the external implementation used by Oracle or DB2. First in-process hosting enables SQL Server to control the execution of the CLR putting Figure 3-1 The SQL Server CLR database architecture 80 Microsoft SQL Server 2005 Developer s Guide essential functions such as memory management garbage collection and threading under the control of the SQL Server database engine. In an external implementation the CLR will manage these things independently. The database engine has a better view of the system requirements as a whole and can manage memory and threads better than the CLR can do on its own. In the end hosting the CLR in-process will provide better performance and scalability. Enabling CLR Support By default the CLR support in the SQL Server database engine is turned off. This ensures that update installations of SQL Server do not unintentionally introduce new
