Section 3.4. Metadata

3.4. Metadata

SQL Server 2005 introduces catalog views an interface to server metadata which expose information about database objects including tables, views, columns, indexes, and stored procedures. Catalog views expose user-available catalog metadata and are the most efficient way to get, transform, and present catalog metadata. Catalog views can be queried using a SELECT statement in the same way as any other database view and return data as standard result sets. Catalog views are often joined in queries to retrieve complex metadata.

Like information schema viewsanother way to retrieve metadatacatalog views expose metadata independently of the underlying implementation of catalog tables. If the underlying catalog tables change, applications that rely on catalog views will not be affected. Unlike information schema views, catalog views expose metadata specific to SQL Server.

Catalog views do not contain data about replication, backup, database maintenance plans, or SQL Server Agent data.

The following query uses a catalog view to retrieve information about all the tables in the AdventureWorks database. One record is returned in the result set for each table.

    SELECT * FROM sys.tables

More information about specific catalog views appears throughout the book. For a complete list of catalog views and their organization, see Microsoft SQL Server 2005 Books Online.

Chapter 4. Introduction to Common Language Runtime (CLR) Integration

The .NET Framework Common Language Runtime (CLR) is an environment that executes compiled code written in programming languages such as C# and VB.NET. The code is compiled to a file called an assembly that contains the compiled code together with an assembly manifest. The manifest contains metadata about the assembly, including types, methods, and inheritance relationships. Code running within the CLR is called managed code.

The CLR provides services such as automatic garbage collection, security support, and runtime type checking. Because the compiled code is executed by the CLR rather than directly by the operating system, managed code applications are platform- and language-independent.

SQL Server 2005 hosts the CLR in the Database Engine. This is called CLR integration. CLR integration lets you create database objects such as functions, stored procedures, triggers, user-defined types (UDTs), and user-defined aggregate (UDA) functions in programming languages supported by the CLR. Managed code running in SQL Server-hosted CLR is referred to as a CLR routine.

Prior to SQL Server 2005, the main way that SQL Server was extended was using extended stored procedures which let you create external routines using programming languages such as C. Extended stored procedures are used like regular stored procedures, however can have performance problems such as memory leaks and can cause the server to become unreliable. CLR integration lets you extend SQL Server with the safety and reliability of T-SQL and with the flexibility of extended stored procedures.

Managed code uses code access security (CAS) to control what operations assemblies can perform. CAS secures the code running within SQL Server and prevents the code from adversely affecting the operating system or the database server.

Generally, you should use T-SQL when the code in the routines primarily performs data access. CLR routines are best for CPU-intensive calculations and for supporting complex logic that would otherwise be difficult to implement using T-SQL.

The components needed to develop CLR routines are installed with SQL Server 2005. Although SQL Server 2005 ships with the .NET Framework and command-line compilers for C# and VB.NET, as well as a Visual Studio .NET IDE that lets you build Analysis Services and Reporting Services projects, you need to install Visual Studio 2005 to create CLR routines in Visual Studio 2005.