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 programmingthe 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.