Programming SQL Server 2005

Chapter 1. Introduction

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

New support for developing database objects such as stored procedures, triggers, and user-defined functions using .NET programming languages. SQL Native Client (SQLNCLI) combines and replaces the native OLE DB provider for SQL Server and the ODBC provider with a single standalone API. SQLXML 4.0 enhances SQLXML 3.0 with support for new SQL Server 2005 XML data capabilities and SQLNCLI.

XML support

New support for the xml data type used to natively store XML fragments and documents and for manipulating xml data type instances with XML Query Language (XQuery) and XML Data Manipulation Language (DML).

Native XML web services

New support lets SQL Server accept SOAP requests so that you can execute queries without a middle-tier application server such as Internet Information Server (IIS).

SQL Management Objects (SMO)

Extends and supersedes Distributed Management Objects (DMO) for configuring and managing all aspects of SQL Server instances.

SQL Server Integration Services (SSIS)

A new technology for building data integration solutions and workflow solutions. SSIS replaces Data Transformation Services (DTS) introduced in SQL Server 2000.

SQL Server Reporting Services (SSRS)

A server-based reporting technology that supports authoring, distributing, managing, and accessing reports. SSRS was introduced in SQL Server 2000 and has been significantly enhanced in SQL Server 2005.

SQL Server Notification Services

A new built-in technology for building applications that creates and sends messages to subscribers according to a schedule or in response to events.

SQL Server Service Broker

A new technology for building scalable, loosely coupled, distributed applications using message-based communications.

Replication Management Objects (RMO)

Extends and supersedes the DMO replication capabilities for configuring and managing all aspects of SQL Server replication.

SQL Server Agent

Automates administrative tasks by running jobs, monitoring SQL Server, and processing alerts. SQL Server 2005 introduces new SMO classes for creating and managing SQL Server Agent.

SQL Server Mobile Edition

Provides relational database functionality for mobile devices in a compact footprint with a programming model consistent with SQL Server 2005. Update to SQL Server 2000 Windows CE Edition 2.0.

Contents of This Book

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

• The new command-prompt utilities included with SQL Server 2005

Chapter 3, T-SQL Enhancements

This chapter describes the new SQL Server 2005 data types that handle XML and large binary data; enhancements to the T-SQL programming language; new DDL triggers; and new catalog views that replace information schema views in SQL Server 2005 as a mechanism to retrieve metadata about SQL Server objects.

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

SQL Server 2005 hosts the .NET Framework CLR in the Database Engine. This arrangement lets you create database objects such as stored procedures, functions, and triggers in programming languages supported by the CLR; you are no longer limited to creating these objects in T-SQL. This chapter introduces programming, testing, debugging, deploying, and securing CLR routines.

Chapter 5, Programming SQL Server CLR Routines

This chapter shows how to program the different types of CLR routines—scalar-valued functions, table-valued functions, stored procedures, user-defined aggregate functions, user-defined types, and both DML and DDL triggers.

Chapter 6, .NET Client-Side Programming

SQL Server 2005 introduces SQL Native Client (SQLNCLI). It replaces the OLE DB provider for SQL Server and the ODBC provider with a single standalone API that combines their functionality into a single DLL. SQL Server 2005 also introduces SQLXML 4.0, which provides client-side functionality for developing applications that access XML data from SQL Server, process that data, and return the data back to the server. SQLXML 4.0 enhances the functionality of SQLXML 3.0 with support for new XML and web services functionality. This chapter describes SQLNCLI and SQLXML 4.0 programming.

Chapter 7, XML Data

SQL Server 2005 introduces native support for XML data storage and processing. Most significantly, a new xml data type stores typed (having an XML schema) and untyped XML fragments and documents. You can manipulate xml data type instances using either XQuery or XML DML. SQL Server also lets you map relational data to XML data, making it easy to work with a mix of data types. This chapter discusses programmatically creating and manipulating xml data type instances and mapping XML data to relational data.

Chapter 8, Native XML Web Services

SQL Server 2005 supports native web services, so you can send SOAP messages directly to SQL Server 2005 to execute T-SQL statements, stored procedures, and scalar-valued user-defined functions (UDFs). This chapter shows how to create and manage HTTP endpoints, expose web service methods, work with SOAP request and response messages, work with SOAP sessions, and monitor SOAP requests for performance.

Chapter 9, SQL Server Management Objects (SMO)

SQL Server 2005 introduces SQL Management Objects (SMO)—a collection of namespaces used for programmatically managing all aspects of SQL Server 2005. SMO supersedes the database management functionality of SQL DMO used to manage SQL Server 2000. This chapter describes the SMO object model and shows how to create a simple SMO application.

Chapter 10, SQL Server Management Objects (SMO) Instance Classes, Part 1

SMO contains a class hierarchy that matches the SQL Server database hierarchy. This chapter describes the SMO classes used to administer database objects that store and access data, such as tables, indexes, triggers, and stored procedures. It also shows how to use the SMO classes programmatically to administer these objects. In addition, it shows how to subscribe to SMO events and handle exceptions.

Chapter 11, SQL Server Management Objects (SMO) Instance Classes, Part 2

SMO contains classes for administering database objects that do not store or access data, such as data and log files, logins, users, roles, and .NET Framework assemblies. This chapter describes how to use these classes programmatically.

Chapter 12, SQL Server Management Objects (SMO) Utility Classes

SMO utility classes are used to perform tasks that are independent of a SQL Server instance. These classes include scripting, backup and restore, transfer, mail, and tracing classes. This chapter shows how to use these classes programmatically.

Chapter 13, Programming Windows Management Instrumentation (WMI)

WMI can be used to manage SQL Server services, network settings, and server alias settings. This chapter describes the classes that you use to access WMI and shows you how to program these classes.

Chapter 14, SQL Server Reporting Services (SSRS)

SSRS provides a reporting environment that runs on top of IIS. You can build reports from any data source—for example, relational, multidimensional, or XML—that can be accessed using a .NET managed data provider, OLE DB provider, or ODBC provider. Reports can be accessed through a parameterized URL or by using the report viewer control in either a Windows Forms or Web Forms application. This chapter shows you how to build, configure, access, and incorporate reports into your applications.

Chapter 15, SQL Server Integration Services (SSIS)

SSIS is a platform for building data-integration and workflow solutions in which you can merge data from different data sources, populate data warehouses, standardize data, and perform administrative operations such as backing up, loading, and copying data. This chapter provides an overview of SSIS and demonstrates SSIS managed-code programming.

Chapter 16, SQL Server Agent

SQL Server Agent automates administrative tasks by running jobs, monitoring SQL Server, and processing alerts. SMO contains classes used to manage all aspects of SQL Server Agent. This chapter describes SQL Server Agent and the SMO class hierarchy for SQL Server Agent and shows how to programmatically create and manage SQL Server Agent objects.

Chapter 17, Service Broker

Service Broker is a technology that helps you build scalable, loosely coupled database applications. Service Broker provides a message-based communications platform that is used to integrate independent applications and components. SMO contains classes used to manage all aspects of Service Broker. This chapter describes Service Broker and the SMO class hierarchy for Service Broker and shows how to programmatically create and use a Service Broker service.

Chapter 18, Notification Services

Notification Services is a programming framework for creating applications that generate and send messages to subscribers on a variety of devices, either according to a schedule or in response to events. Notification Services is integrated into SQL Server 2005 rather than being distributed as a download component, as was the case with SQL Server 2000. SMO contains classes used to administer all aspects of Notification Services. This chapter describes Notification Services and the SMO class hierarchy for Notification Services and shows how to programmatically create and manage Notification Services objects.

Chapter 19, Replication

Replication copies and distributes data and database objects between databases and provides a mechanism to keep the data synchronized. SQL Server 2005 introduces SQL Server Replication Management Objects (RMO), a collection of namespaces used to program all aspects of SQL Server 2005 replication. RMO replaces the replication management capabilities of SQL DMO, used to manage replication in SQL Server 2000. This chapter describes the RMO object model and shows you how to program replication using RMO classes.

Chapter 20, SQL Server Analysis Services (SSAS)

SSAS provides online analytical processing (OLAP) and data-mining functionality using a combination of client- and server-side components. This chapter describes SSAS, shows how to programmatically query data and metadata, and explains how to programmatically administer SSAS instances and objects.

Chapter 21, SQL Server Mobile Edition

SQL Server Mobile Edition lets you run relational database applications on mobile devices by providing relational database functionality in a compact footprint with a programming model consistent with SQL Server 2005. SQL Server Mobile is an update to SQL Server Windows CE. This chapter describes SQL Server Mobile, shows how to create and manage databases and database objects, and explains how to read, update, and synchronize data programmatically.

Appendix, ADO.NET 2.0

ADO.NET 2.0 is a collection of classes that lets .NET applications consistently access data stored in a wide variety of data sources. ADO.NET is used to retrieve, manipulate, and update data stored in supported data sources including SQL Server, Oracle, and data sources exposed through OLE DB. ADO.NET 2.0 is an update to ADO.NET introduced with Visual Studio .NET 2002. This appendix describes new functionality, support, and features of ADO.NET 2.0.

What’s Not in This Book

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.