Programming SQL Server 2005

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

Microsoft SQL Server 2005 is the latest relational database server product from Microsoft, updating Microsoft SQL Server 2000. SQL Server 2005 adds new functionality and improves the performance, reliability, programmability, and usability of SQL Server 2000.

This book describes and shows how to program SQL Server 2005. Generally, the discussions cover the entire topic, because most aspects of programming SQL Server 2005 are new. Examples include using .NET Framework Common Language Runtime (CLR) assemblies to create SQL Server objects, such as stored procedures and triggers, and using SQL Server Management Objects (SMO) to programmatically create, modify, delete, and manage databases, tables, and other SQL Server objects. In the case of Transact-SQL (T-SQL) and ADO.NET 2.0, only enhancements are described and demonstrated. The SQL Server Analysis Services (SSAS) coverage provides a broad introduction to SSAS that should help you to understand what SSAS is, what its key parts are, and how to get started with SSAS programming—the topic is simply too large to do more than that here.

From a programming perspective, new SQL Server 2005 features include the following:

Tools and utilities: A new IDE called SQL Server Management Studio for managing SQL Server topologies, databases, and objects; and a collection of new tools for tuning, profiling, and developing SQL Server 2005 solutions.
Data types: New support for storing and working with native XML data and large binary data.
T-SQL enhancements: New support for Data Definition Language (DDL) triggers, event notifications, bulk operations, recursive queries, and distributed queries, and introduction of new operators.
Programmability enhancements

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

This book is organized into 20 chapters (plus this introduction, and an appendix), each of which focuses on a SQL Server 2005 programming topic. In some cases, more than one chapter is used to cover different aspects of a single topic. Each chapter contains code samples showing you how to program SQL Server 2005. Code samples are written in C# and compiled using the Visual Studio 2005 development environment. To give you an overview of this book's contents, the following list summarizes each chapter:

Chapter 2, Tools and Utilities

This chapter provides an overview of new and enhanced tools and command-line utilities in SQL Server 2005. This chapter describes:

SQL Server Management Studio, the new IDE for managing SQL Server 2005 objects
SQL Server Configuration Manager, used to manage SQL Server 2005 services
SQL Server Surface Area Configuration Manager, used to manage the available features, services, and remote connectivity of a SQL Server 2005 instance for security purposes
Database Engine Tuning Advisor, used to improve query processing without requiring a detailed understanding of the database structure or how query processing actually occurs
SQL Server Profiler, used to monitor query processing for auditing, debugging, and tuning purposes
SQL Server Business Intelligence Development Studio, used to develop SQL Server 2005 solutions—Analysis Services, Integration Services, and Reporting Services—with an IDE similar to that of Visual Studio 2005
Visual Studio .NET 2005, used for developing CLR routines and solutions for programmatically administering SQL Server 2005

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

This book is not a reference, although some reference material is included where it helps explain concepts. Detailed reference information is available in Microsoft SQL Server 2005 Books Online, which is installed by default with SQL Server 2005 and accessed by selecting Start → All Programs → Microsoft SQL Server 2005 → Documentation and Tutorials → SQL Server Books Online. This book does not cover SQL Server 2005 administration or migrating from SQL Server 2000 to SQL Server 2005.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

SQL Server 2005 introduces new and enhanced tools and command-line utilities. This chapter provides an overview of those tools and utilities. It focuses on new features and enhancements, and information most relevant to programming SQL Server and programmatically administering SQL Server. For detailed information about these tools and utilities, see Microsoft SQL Server 2005 Books Online.

SQL Server Management Studio is an integrated environment for accessing, configuring, managing, and administering SQL Server and for developing SQL Server objects. Management Studio works with all SQL Server components, including Report Services, Data Transformation Services, SQL Server Mobile, and Notification Services.

SQL Server Management Studio combines the features of Enterprise Manager, Query Analyzer, and Analysis Manager in SQL Server 2000 and adds new functionality.

Launch SQL Server Management Studio by selecting Start → All Programs → Microsoft SQL Server 2005 → SQL Server Management Studio from the taskbar. The Connect to Server dialog box opens, prompting you for server information and credentials. Fill in the required information and click the Connect button. Figure 2-1 shows SQL Server Management Studio.

SQL Server Management Studio displays two panes by default:

Object Explorer
Document (initially a single Summary Page)

These and other windows can be added using the View menu. The various windows are described in the following subsections.

Figure 2-1: SQL Server Management Studio

The Registered Servers window lists servers that you have previously registered, optionally organized into server groups—a hierarchical structure used to help manage registered servers. A registered server preserves connection information and lets you easily determine whether the servers are running, access Object Explorer and Query Editor for the servers, and provide user-friendly names together with detailed descriptions for the servers.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

SQL Server Management Studio is an integrated environment for accessing, configuring, managing, and administering SQL Server and for developing SQL Server objects. Management Studio works with all SQL Server components, including Report Services, Data Transformation Services, SQL Server Mobile, and Notification Services.

SQL Server Management Studio combines the features of Enterprise Manager, Query Analyzer, and Analysis Manager in SQL Server 2000 and adds new functionality.

Launch SQL Server Management Studio by selecting Start → All Programs → Microsoft SQL Server 2005 → SQL Server Management Studio from the taskbar. The Connect to Server dialog box opens, prompting you for server information and credentials. Fill in the required information and click the Connect button. Figure 2-1 shows SQL Server Management Studio.

SQL Server Management Studio displays two panes by default:

Object Explorer
Document (initially a single Summary Page)

These and other windows can be added using the View menu. The various windows are described in the following subsections.

Figure 2-1: SQL Server Management Studio

The Registered Servers window lists servers that you have previously registered, optionally organized into server groups—a hierarchical structure used to help manage registered servers. A registered server preserves connection information and lets you easily determine whether the servers are running, access Object Explorer and Query Editor for the servers, and provide user-friendly names together with detailed descriptions for the servers.

The toolbar below the main menu lets you switch between the five types of registered servers that you can manage (listed in order from left to right):

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

SQL Server 2005 runs as a collection of services—application types that run in the system background. SQL Server Configuration Manager is a Microsoft Management Console (MMC) snap-in that lets you start, stop, pause, resume, restart, and configure services, including:

SQL Server
SQL Server Agent
SQL Server Analysis Services
SQL Server Browser
SQL Server Full-Text Search
SQL Server Integration Services
SQL Server Reporting Services

Start SQL Server Configuration Manager by selecting Start → All Programs → Microsoft SQL Server 2005 → Configuration Tools → SQL Server Configuration Manager from the taskbar. Figure 2-17 shows SQL Server Configuration Manager.

Figure 2-17: SQL Server Configuration Manager

In addition to managing services, SQL Server Management Studio lets you manage server and client network protocols—you can enable or disable protocols and force protocol encryption—and manage server aliases.

SQL Server Configuration Manager combines the functionality of the following: Server Network Utility, Client Network Utility, and Service Manager from SQL Server 2000.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

SQL Server Surface Area Configuration lets you enable, disable, start, and stop features, services, and remote connectivity of SQL Server 2005 installations—this helps to secure both local and remote systems.

Start SQL Server Surface Area Configuration Manager by selecting Start → All Programs → Microsoft SQL Server → Configuration Tools → SQL Server Surface Area Configuration from the taskbar.

The sac.exe command-line utility imports Microsoft SQL Server 2005 surface settings so that you can apply them to other SQL Server instances. The utility is in the C:\Program Files\Microsoft SQL Server\90\Shared directory (assuming that you installed SQL Server to the default directory).

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

Database Engine Tuning Advisor (DTA) helps you improve query processing without requiring you to understand the database structure or how SQL Server processes queries. DTA helps you select and create an optimal set of indexes, indexed views, and partitions. It analyzes a workload—a set of T-SQL statements that runs against the database—against the implementation of one or more databases and recommends changes to the database that reduce the estimated workload cost of the query optimizer. These modifications include adding, deleting, or modifying clustered indexes, nonclustered indexes, indexed views, and partitions.

You launch Database Engine Tuning Advisor in one of three ways:

Select Start → All Programs → Microsoft SQL Server 2005 → Performance Tools → Database Engine Tuning Advisor from the taskbar.
From the SQL Server Management Studio menu, select Tools → Database Engine Tuning Advisor.
Run the command-line tool dta.exe.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

SQL Trace captures database-engine events in real time to a trace file. Traces are based on event class instances that you choose to monitor. Trace information is used to monitor and assess performance, audit activity, and debug SQL statements and stored procedures. Traces are usually managed and accessed through SQL Server Profiler, a graphical user interface to SQL Trace.

SQL Server Profiler lets you create and manage traces, create trace templates, and replay trace results. With SQL Server Profiler, you can monitor how queries are resolved and capture SQL Server events from the Database Engine or Analysis Services to a trace file for analysis. You can later replay the trace events to help diagnose problems.

You launch SQL Server Profiler in one of three ways:

Select Start → All Programs → Microsoft SQL Server 2005 → Performance Tools → SQL Server Profiler from the taskbar.
From SQL Server Management Studio, select Tools → SQL Server Profiler.
Run the command-line tool profiler90.exe.

SQL Server Management Objects (SMO) provides classes that can be used to create and manage traces for SQL Server or Analysis Server. They are discussed in detail in Chapter 12.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

SQL Server Business Intelligence Development Studio is a development IDE—similar to Visual Studio 2005—for developing Analysis Services, Integration Services, and Reporting Services projects. Business Intelligence (BI) Development Studio lets you develop these projects independently of the server and organize groups of projects into solutions. BI Development Studio lets you deploy projects to testing, staging, and production servers.

BI Development Studio functionality is added to Visual Studio 2005 on computers where Visual Studio 2005 is installed.

Launch BI Development Studio by selecting Start → All Programs → Microsoft SQL Server → SQL Server Business Intelligence Development Studio from the taskbar. BI Development Studio has five main windows:

Designer: Designs and creates objects in the project; provides a code view and design view of each object appropriate to the object type.
Solution Explorer: Manages projects in the solution.
Properties: Views and modifies properties of an object.
Toolbox: Contains controls available for an object. Controls are often available only in the design view.
Output: Displays output, debugging, error, and other information during compilation and execution of the solution.

Figure 2-18 shows the BI Development Studio with a sample Analysis Services project loaded.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

SQL Server 2005 hosts the .NET Common Language Runtime (CLR) in the Database Engine. This lets you create database objects such as functions, stored procedures, triggers, user-defined data types, and user-defined aggregate functions in programming languages supported by the CLR. Visual Studio 2005 supports CLR integration into SQL Server with a new project type named SQL Server Project. Once the compiled assembly for a SQL Server project is registered with SQL Server, the database objects in the assembly can be used the same way as if they had been created using T-SQL. Chapters 4 and 5 discuss SQL Server 2005 CLR integration and programming database objects using Visual Studio 2005.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

SQL Server 2005 introduces the new command-line utilities described in Table 2-2.

Table 2-2: New SQL Server command-line utilities
Utility	Description
sqlcmd	Executes T-SQL statements, system procedures, and script files from the command prompt. The `sqlcmd` utility is described further later in this section.
sqlwb	Launches SQL Server Management Studio from the command prompt.
profiler90	Launches SQL Server Profiler from the command prompt.
dta	Launches Database Engine Tuning Advisor from the command prompt.
dtexec	Used to configure and execute an SSIS package loaded from a database, the SSIS package store, or the file system.
dtutil	Used to manage and verify existence of an SSIS package stored in the SQL Server msdb database, the SSIS package store, or the file system.
tablediff	Used to compare the data in two tables for nonconvergence.

Section 2.8.1.1: SQL Server command-line tool (sqlcmd utility)

The SQL Server command-line tool lets you execute T-SQL statements, system procedures, and script files from the command prompt. The

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

SQL Server 2005 extends the T-SQL language in several significant ways:

New XML and large object data types
New and enhanced language capabilities, including support for recursive queries and improved error handling
Data Definition Language (DDL) triggers
Catalog views to access metadata

This chapter discusses these enhancements and changes.

SQL Server 2005 introduces the xml data type and large value data types. The xml data type supports storing XML documents and fragments in the database. Large value data types—varchar(max), nvarchar(max), and varbinary(max)—extend the storage capacity of varchar, nvarchar, and varbinary data types up to 2³¹ bytes of data.

The built-in xml data type stores XML documents and fragments natively as a column, variable, parameter, or function return type. A schema can be associated with an xml data type to validate each instance of the type—the XML instance is then typed. An XML instance without a schema is untyped. xml data types can be manipulated using XQuery and XML DML. Columns of xml data type can be indexed.

Chapter 7 provides an in-depth look at support for XML data in SQL Server 2005.

SQL Server 2000 has varchar, nvarchar, and varbinary variable-length data types:

varchar: Variable-length non-Unicode data with a maximum length of 8,000 bytes
nvarchar: Variable-length Unicode data with a maximum length of 4,000 bytes

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

SQL Server 2005 introduces the xml data type and large value data types. The xml data type supports storing XML documents and fragments in the database. Large value data types—varchar(max), nvarchar(max), and varbinary(max)—extend the storage capacity of varchar, nvarchar, and varbinary data types up to 2³¹ bytes of data.

The built-in xml data type stores XML documents and fragments natively as a column, variable, parameter, or function return type. A schema can be associated with an xml data type to validate each instance of the type—the XML instance is then typed. An XML instance without a schema is untyped. xml data types can be manipulated using XQuery and XML DML. Columns of xml data type can be indexed.

Chapter 7 provides an in-depth look at support for XML data in SQL Server 2005.

SQL Server 2000 has varchar, nvarchar, and varbinary variable-length data types:

varchar: Variable-length non-Unicode data with a maximum length of 8,000 bytes
nvarchar: Variable-length Unicode data with a maximum length of 4,000 bytes
varbinary: Variable-length binary data with a maximum length of 8,000 bytes

SQL Server 2005 introduces large value data types—varchar(max), nvarchar(max), and varbinary(max). You use the max specifier to extend the storage capability of varchar, nvarchar, and varbinary data types to 2³¹ bytes in the varchar and varbinary data types and to 2

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

SQL Server 2005 includes significant enhancements to the T-SQL language:

The enhanced TOP clause supports using an expression to specify the number of rows or percent of rows returned in the result set.
The new TABLESAMPLE clause returns a random sample of rows from the result set.
The new OUTPUT clause returns a result set containing the rows affected by an INSERT, UPDATE, or DELETE statement.
Common table expressions (CTEs) let you create a temporary named result set from a query, which simplifies tasks such as recursive queries.
New SOME, ANY, and ALL operators compare the values in a column with a scalar value.
The new PIVOT operator rotates a table, turning unique values in column rows into multiple columns in the result set, while the new UNPIVOT operator turns multiple columns in a result set into rows.
The new APPLY operator invokes a table-valued function for each row in a result set.
The new EXECUTE AS clause defines the user execution context of T-SQL statements.
The new ROW_NUMBER( ), DENSE_RANK( ), and NTILE( ) ranking functions are added to the RANK function that exists in SQL Server 2000.
New support for structured exception handling using TRY...CATCH blocks.

This section details these enhancements.

The TOP clause limits the number of rows returned in a result set. SQL Server 2005 enhances the TOP clause to allow an expression to be used as the argument to the TOP clause instead of just a constant as was the case in SQL Server 2000. The TOP clause can be used in SELECT, INSERT, UPDATE, and DELETE statements.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

Data Manipulation Language (DML) triggers are fired on actions that cause a change to the data in a table or a view—INSERT, UPDATE, or DELETE. SQL Server 2005 introduces Data Definition Language (DDL) triggers that fire in response to DDL statements that change the database schema or database server. These statements include CREATE, ALTER, DROP, GRANT, DENY, and REVOKE. DDL triggers are typically used for auditing and logging.

The syntax for a DDL trigger is:

    CREATE TRIGGER trigger_name
    ON { ALL SERVER | DATABASE }
    [ WITH <ddl_trigger_option> [ ...,n ] ]
    { FOR | AFTER } { event_type | event_group } [ ,...n ]
    AS { sql_statement [ ...n ] | EXTERNAL NAME < method specifier > }
    [ ; ]

    <ddl_trigger_option> ::=
        [ ENCRYPTION ]
        [ EXECUTE AS Clause ]

    <method_specifier> ::=
        assembly_name.class_name.method_name

where:

trigger_name: The name of the trigger.
ON { ALL SERVER | DATABASE }: Defines the scope of the DDL trigger.

Triggers scoped as ALL SERVER fire whenever event_type or event_group happens anywhere in the current server. You must have at least CONTROL SERVER permission on the server to create a DDL trigger with server scope.

Triggers scoped as DATABASE fire whenever event_type or event_group occurs in the current database.
<ddl_trigger_option>: The WITH ENCRYPTION clause encrypts the CREATE TRIGGER statement body and prevents it from being accessed through catalog views or from being published as part of SQL Server replication.

The EXECUTE AS clause specifies the security context under which the trigger is executed.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

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 views—another way to retrieve metadata—catalog 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.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

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.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

Microsoft identifies the design objectives of SQL Server 2005 CLR integration as follows:

Reliability: CLR routines cannot perform operations that compromise the integrity of the Database Engine process, nor are they allowed to overwrite Database Engine memory buffers and internal data structures.
Scalability: SQL Server and the CLR have different models for threading, scheduling, and memory management. The design goal is to ensure scalability when user code calls APIs for threading, synchronization primitives, and memory.
Security: User code running in the database must follow SQL Server authentication and authorization rules for accessing database objects. Additionally, administrators must be able to control access to operating system resources from code running within the database.
Performance: User code running in the database must perform at least as well as equivalent implementations through native Database Engine functionality or T-SQL.

The CLR provides the following services to achieve these design objectives:

Type-safe verification: After assemblies are loaded into the CLR but before they are compiled, the code is verified to ensure access to memory structures only in well-defined ways—code that passes this verification is type-safe.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

CLR integration is turned off by default in SQL Server 2005. Use the sp_configure system stored procedure to enable CLR integration, as shown here:

    sp_configure 'clr enabled', 1
    GO
    RECONFIGURE
    GO

The clr enabled server configuration option specifies whether .NET assemblies can be run by SQL Server (0 = do not allow; 1 = allow). The change takes effect immediately after sp_configure and reconfigure are executed—the server does not need to be restarted.

You need ALTER SETTINGS permissions at the server level to enable CLR integration.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

The components needed to create simple CLR routines are installed with SQL Server 2005 in the .NET Framework assembly named System.Data.dll—part of the base class library of the .NET Framework and located in both the Global Assembly Cache (GAC) and in the <windir>\Microsoft.NET\Framework\<version> directory. The key namespaces in this assembly are described in Table 4-2.

Table 4-2: .NET namespaces for CLR routines
System.Data.dll namespaces	Description
`System.Data`	Classes that comprise the ADO.NET architecture
`System.Data.Sql`	Classes that support SQL Server 2005-specific functionality
`Microsoft.SqlServer.Server`	Classes that support .NET CLR functionality inside SQL Server CLR routines—user-defined functions (UDFs), stored procedures, UDA functions, UDTs, and triggers
`System.Data.SqlTypes`	Classes that support native SQL Server data types
`System.Data.SqlClient`	.NET Frameworks data provider for SQL Server

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

SQL Server 2005 CLR integration lets you build database objects using .NET languages. Once a .NET Framework assembly is registered with SQL Server, you can create CLR routines that can be used anywhere a T-SQL equivalent routine can be used. Table 4-3 describes the available CLR routines.

Table 4-3: Types of CLR routines
Database object	.NET Framework assembly type	Description
Scalar-valued function	Public static method	A UDF that returns a single value.
Table-valued function	Public static method	A UDF that returns a table as the result set.
Stored procedure	Public static method	A routine that returns tabular result sets and messages to the client, invokes DDL and DML statements, and returns output parameters.
User-defined aggregate function	Class or structure	A UDA function that operates on values in a set of rows and returns a scalar.
User-defined type	Class or structure	Complex data types complete with methods that extend the scalar type system in SQL Server.
Trigger (DML and DDL)

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

This section shows how to create, configure, and use a SQL Server CLR routine by way of a sample CLR stored procedure that returns the text message "Hello world." This example is followed by an example that shows how to create the same .NET Framework assembly using a command-line compiler.

Follow these steps in Visual Studio 2005 to create the .NET Framework assembly containing the CLR stored procedure:

Select File → New → Project.
Select SQL Server Project in the New Project dialog box, shown in Figure 4-1, name it HelloWorld, specify the location, and click OK.

Figure 4-1: New Project dialog box
Because the stored procedure will not be accessing any data, click Cancel in the Add Database Reference dialog box, shown in Figure 4-2.

Figure 4-2: Add Database Reference dialog box
In Solution Explorer, right-click the HelloWorld project and select Add → Stored Procedure from the context menu, as shown in Figure 4-3.

Figure 4-3: Add Stored Procedure menu item
In the Add New Item dialog box, shown in Figure 4-4, select the Stored Procedure template. Enter the name HelloWorldStoredProcedure.cs and click Add.
Add the following line of code to the HelloWorldStoredProcedure( ) method in HelloWorldStoredProcedure.cs:
```
    SqlContext.Pipe.Send("Hello world.\n");
```
The complete code should now be as follows:

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

SQL Server introduces new T-SQL statements to create and manage .NET assemblies and UDTs, and enhances other T-SQL statements to create and manage functions, stored procedures, triggers, and UDA functions created from CLR assemblies. These statements are described in Table 4-4.

Table 4-4: New and changed T-SQL statements to support CLR integration
Scope	DDL statement	New T-SQL statement	Description
.NET Framework assembly	`CREATE ASSEMBLY`	Yes	Loads assembly into SQL Server.
	`ALTER ASSEMBLY`	Yes	Changes a loaded assembly.
	`DROP ASSEMBLY`	Yes	Unloads an assembly from SQL Server.
User-defined aggregate function	`CREATE AGGREGATE`	Yes	Creates a UDA function in a SQL Server database from a UDA function implemented as a class in a .NET Framework assembly. The assembly containing the class must first be registered in SQL Server with the

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy PDF of this chapter|

Table of Contents

Section 2.8.1.1: SQL Server command-line tool (sqlcmd utility)