Cover image for Programming SQL Server 2005

Book description

SQL Server 2005, Microsoft's next-generation data management and analysis solution, represents a huge leap forward. It comes with a myriad of changes that deliver increased security, scalability, and power--making it the complete data package. Used properly, SQL Server 2005 can help organizations of all sizes meet their data challenges head on.

Programming SQL Server 2005 from O'Reilly provides a practical look at this updated version of Microsoft's premier database product. It guides you through all the new features, explaining how they work and how to use them. The first half of the book examines the changes and new features of the SQL Server Engine itself. The second addresses the enhanced features and tools of the platform, including the new services blended into this popular version. Each chapter contains numerous code samples-written in C# and compiled using the Visual Studio 2005 development environment-that show you exactly how to program SQL Server 2005.

Programming SQL Server 2005 can help you:

  • Build, deploy, and manage enterprise applications that are more secure, scalable, and reliable

  • Maximize IT productivity by reducing the complexity of building, deploying, and managing database applications

  • Share data across multiple platforms, applications, and devices to make it easier to connect internal and external systems

Because the goal of Programming SQL Server 2005 is to introduce all facets of Programming SQL Server 2005, it's beneficial to programmers of all levels. The book can be used as a primer by developers with little experience with SQL Server, as a ramp up to the new programming models for SQL Server 2005 for more experienced programmers, or as background and primer to specific concepts.

Any IT professional who wants to learn about SQL Server 2005's comprehensive feature set, interoperability with existing systems, and automation of routine tasks will find the answers in this authoritative guide.

Table of Contents

  1. Programming SQL Server 2005
    1. SPECIAL OFFER: Upgrade this ebook with O’Reilly
    2. A Note Regarding Supplemental Files
    3. Preface
      1. What You Need to Use This Book
      2. Conventions Used in This Book
      3. How to Contact Us
      4. Safari® Enabled
      5. Acknowledgments
    4. 1. Introduction
      1. Contents of This Book
      2. What’s Not in This Book
    5. 2. Tools and Utilities
      1. SQL Server Management Studio
        1. Registered Servers
        2. Object Explorer
          1. Database server instance
          2. Databases node
          3. Security node
          4. Server Objects node
          5. Replication node
          6. Management node
          7. Notification Services node
          8. SQL Server Agent node
        3. Template Explorer
        4. Solution Explorer
      2. SQL Server Configuration Manager
      3. SQL Server Surface Area Configuration
      4. Database Engine Tuning Advisor
      5. SQL Server Profiler
      6. SQL Server Business Intelligence Development Studio
      7. Visual Studio 2005
      8. New Command-Line Utilities
          1. SQL Server command-line tool (sqlcmd utility)
    6. 3. T-SQL Enhancements
      1. New Data Types
        1. The xml Data Type
        2. Large Value Data Types
      2. T-SQL Language Enhancements
        1. TOP
        2. TABLESAMPLE
        3. OUTPUT
        4. Common Table Expressions (CTEs)
        5. SOME and ANY
        6. ALL
        7. PIVOT and UNPIVOT
        8. APPLY
        9. EXECUTE AS
        10. New Ranking Functions
          1. ROW_NUMBER( )
          2. DENSE_RANK( )
          3. NTILE( )
        11. Error Handling
      3. Data Definition Language (DDL) Triggers
      4. Metadata
    7. 4. Introduction to Common Language Runtime (CLR) Integration
      1. CLR Integration Design Objectives
      2. Enabling CLR Integration
      3. Required .NET Namespaces
      4. Types of CLR Routines
      5. Hello World Example
        1. Command-Line Compiler
      6. DDL Support for CLR Integration
        1. CLR Routine Metadata
          1. sys.assemblies
          2. sys.assembly_files
          3. sys.assembly_modules
          4. sys.assembly_references
          5. sys.assembly_types
        2. Assembly Management
          1. CREATE ASSEMBLY
          2. ALTER ASSEMBLY
          3. DROP ASSEMBLY
        3. User-Defined Functions
          1. CREATE FUNCTION
          2. ALTER FUNCTION
          3. DROP FUNCTION
        4. Stored Procedures
          1. CREATE PROCEDURE
          2. ALTER PROCEDURE
          3. DROP PROCEDURE
          4. Metadata
        5. User-Defined Aggregate Functions
          1. CREATE AGGREGATE
          2. DROP AGGREGATE
        6. User-Defined Types
          1. CREATE TYPE
          2. DROP TYPE
          3. Metadata
        7. Triggers
          1. CREATE TRIGGER
          2. ALTER TRIGGER
          3. DROP TRIGGER
          4. Metadata
      7. ADO.NET In-Process Extensions Supporting CLR Programming
        1. SqlContext Object
        2. SqlPipe Object
        3. SqlTriggerContext Object
        4. SqlDataRecord Object
      8. Custom Attributes for CLR Routines
      9. SQL Server Data Types in the .NET Framework
      10. Testing and Debugging CLR Routines
    8. 5. Programming SQL Server CLR Routines
      1. Scalar-Valued Functions
      2. Table-Valued Functions
      3. Stored Procedures
      4. User-Defined Aggregate Functions
      5. User-Defined Types
      6. Triggers
        1. DML Triggers
        2. DDL Triggers
    9. 6. .NET Client-Side Programming
      1. SQL Native Client Programming
      2. SQLXML 4.0
        1. SQLXML Managed Classes
          1. SqlXmlCommand
          2. SqlXmlParameter
          3. SqlXmlAdapter
        2. Using SQLXML Managed Classes
          1. Executing a query
          2. Using parameters in a query
          3. Handling an exception
          4. Retrieving an XmlReader object
          5. Processing an XML result set on the client
          6. Filling a DataSet
          7. Applying an XSLT transformation to the XML result set
        3. Annotated Mapping Schemas
        4. XML Template Queries
        5. UpdateGrams
        6. DiffGrams
      3. Exception Message Box
    10. 7. XML Data
      1. xml Data Type
        1. Creating xml Data Type Columns and Variables
          1. Columns
          2. Variables
        2. Limitations
      2. Creating xml Data Type Instances
        1. Casting and Converting Strings
        2. Constant Assignment
        3. Bulk Loading Data with OPENROWSET
      3. XML Data Type Methods
        1. query( )
        2. value( )
        3. exist( )
        4. modify( )
        5. nodes( )
      4. Viewing XML Data as Relational Data
      5. Indexing XML Data
        1. Creating an XML Index
        2. Altering an XML Index
        3. Dropping an XML Index
        4. Viewing XML Indexes
      6. Managing XML Schema Collections
        1. Creating XML Schema Collections
        2. Modifying XML Schema Collections
        3. Removing XML Schema Collections
        4. Viewing XML Schema Collections
        5. Managing XML Schema Collection Permissions
          1. Granting permissions
          2. Revoking permissions
          3. Denying permissions
        6. Limitations of XML Schema Collections
      7. XQuery Support
        1. xml Data Type Functions
        2. xml Data Type Operators
        3. Using XQuery Extension Functions to Bind Relational Data Inside XML Data
          1. sql:column( ) function
          2. sql:variable( ) function
      8. XML Data Manipulation Language
        1. insert
        2. delete
        3. replace value of
        4. XML DML Limitations and Restrictions
      9. XML Results Using the FOR XML Clause
        1. Some FOR XML Examples
        2. FOR XML Support for SQL Server Data Types
        3. FOR XML Updates and Enhancements
      10. XML Catalog Views
    11. 8. Native XML Web Services
      1. Open Standards
      2. Creating a Web Service
      3. Creating the HTTP Endpoint and Exposing a Web Method Example
        1. SQL Batches
      4. SOAP Request and Response Messages
      5. Managing HTTP Endpoints
        1. Creating an HTTP Endpoint
          1. Protocol-specific arguments
          2. Language-specific arguments
        2. Altering an ENDPOINT
          1. Protocol-specific items
          2. Language-specific items
        3. Dropping an ENDPOINT
        4. Endpoint Authentication
        5. Managing Permissions on Endpoints
          1. Create permission
          2. Alter permission
          3. Control permission
          4. Connect permission
          5. Take Ownership permission
        6. Endpoint Metadata
    12. 9. SQL Server Management Objects (SMO)
      1. SMO Object Model
      2. Creating an SMO Project in Visual Studio .NET
      3. A Simple SMO Application
    13. 10. SQL Server Management Objects (SMO) Instance Classes, Part 1
      1. Programming SMO Instance Classes for Administering Data Storage Objects
        1. Connecting to and Disconnecting from SQL Server
        2. Navigating the Server Hierarchy
        3. Enumerating Database Properties
        4. Enumerating Database Objects
        5. Creating a Database Object
        6. Checking Database Table Integrity
        7. Transacting SMO Operations
        8. Capture Mode
        9. Event Notification
        10. Handling Exceptions
      2. SMO Instance Classes for Administering Data Storage Objects Reference
        1. SQL Server Instances
        2. Databases
        3. Tables
        4. Columns
        5. Views
        6. Indexes
        7. Foreign Keys
        8. Check Constraints
        9. Rules
        10. Stored Procedures
        11. Numbered Stored Procedures
        12. Extended Stored Procedures
        13. DML Triggers
        14. DDL Triggers
        15. User-Defined Objects
        16. Data Types
        17. System Data Types
        18. Schemas
        19. SQL Server Objects
    14. 11. SQL Server Management Objects (SMO) Instance Classes, Part 2
      1. Programming SMO Instance Classes for Administering Database Objects Not Used for Data Storage
        1. Registered Server and Server Groups
        2. Managing Logins
        3. Managing Users
        4. Managing Roles
        5. Managing Server Permissions
        6. Enumerating .NET Framework Assemblies
        7. Statistics
        8. Synonyms
        9. Messages
        10. Full-Text Search
      2. SMO Instance Classes for Administering Objects Not Used for Data Storage Reference
        1. Server Configuration
        2. Registered Servers
        3. Linked Servers
        4. Database Defaults
        5. Files and Filegroups
        6. Partition Functions
        7. Partition Schemes
        8. Logins
        9. Users
        10. Server Roles
        11. Database Roles
        12. Server Permissions
        13. Database Permissions
        14. Application Roles
        15. .NET Framework Assemblies
        16. Endpoints
        17. XML Schemas
        18. Languages
        19. Statistics
        20. Certificates
        21. Credentials
        22. Keys
        23. Synonyms
        24. System Messages
        25. User-Defined Messages
        26. Full-Text Search
        27. OLE DB Providers
    15. 12. SQL Server Management Objects (SMO) Utility Classes
      1. Scripting
      2. Backing Up and Restoring Data
      3. Transferring Data
      4. Tracing
      5. Database Mail
    16. 13. Programming Windows Management Instrumentation (WMI)
      1. Programming SMO WMI Classes
        1. Enumerating the WMI Installation
        2. Creating a Server Alias
        3. Starting and Stopping a Service
    17. 14. SQL Server Reporting Services (SSRS)
      1. Getting Started
      2. Integrating Reports into Applications
        1. URL Access
        2. Report Viewer Control
        3. Report Server Web Service
      3. Reporting Services Extensions
    18. 15. SQL Server Integration Services (SSIS)
      1. Architecture
        1. SSIS Objects
        2. Control-Flow Elements
        3. Data-Flow Components
        4. Connection Managers
        5. Events
        6. Package Configurations
        7. Log Providers
        8. Variables
      2. Tools
      3. Programming SSIS
        1. Control-Flow Programming
          1. Creating a package
          2. Saving a package
          3. Loading a package
          4. Adding a task to the package
          5. Adding a connection manager
          6. Running a package
          7. Validating a package
          8. Enumerating task properties
          9. Connecting tasks
          10. Using variables
          11. Configuring a package
          12. Handling events
          13. Logging
        2. Data-Flow Programming
    19. 16. SQL Server Agent
      1. Programming SQL Server Agent
        1. Creating a Job
        2. Running a Job
        3. Creating a Schedule
        4. Scheduling a Job
        5. Creating an Operator
        6. Creating an Alert
        7. Creating a Proxy Account
        8. Multiserver Environments
    20. 17. Service Broker
      1. Architecture
      2. Programming Service Broker
        1. Enumerating Service Broker Objects
        2. Implementing a Service Broker Service
    21. 18. Notification Services
      1. Architecture
      2. Creating a Notification Services Application
      3. Programming Notification Services
        1. Creating a Notification Services Application and Service
        2. Creating a Notification Services Instance and Application
        3. Creating a Delivery Channel
        4. Creating an Event Class
        5. Creating a Subscription Class and Subscription Event Rule
        6. Creating a Notification Class, Content Formatter, and Notification Class Protocol
        7. Creating an Event Provider
        8. Creating a Generator
        9. Creating a Distributor
        10. Creating a Vacuum Schedule
        11. Creating a Subscriber and Subscriber Device
        12. Creating a Subscription
        13. Enumerating a Notification Services Instance Database
        14. Enumerating a Notification Services Application Database
    22. 19. Replication
      1. Programming Replication
        1. Prerequisites
        2. Installing a Distributor
        3. Creating a Publisher
        4. Enabling a Database for Publication
        5. Creating a Publication
        6. Creating an Article
        7. Enumerating Items Available for Replication
        8. Filtering an Article
        9. Registering a Subscriber
        10. Creating a Subscription
        11. Generating the Initial Snapshot
        12. Synchronizing a Subscription to an Initial Snapshot
        13. Retrieving Agent History
        14. Specifying a Replication Schedule
        15. Validating Subscriber Data
        16. Monitoring Replication
        17. Business Logic Handlers
    23. 20. SQL Server Analysis Services (SSAS)
      1. Before You Begin
      2. SSAS Overview
      3. SSAS Database
      4. SSAS Languages
        1. Multidimensional Expressions (MDX)
        2. Data Mining Extensions (DMX)
        3. XML for Analysis (XMLA)
        4. Analysis Services Scripting Language (ASSL)
      5. Development
        1. SSAS Project
        2. SSAS Scripts Project
      6. Accessing Data by Using ADOMD.NET
        1. Querying an SSAS Database
        2. Retrieving Schema Information
      7. Administering SSAS Objects
        1. Analysis Management Objects (AMO)
        2. Decision Support Objects (DSO)
    24. 21. SQL Server Mobile Edition
      1. Environments
        1. Client
        2. Server
      2. Prerequisites
      3. Programming SQL Server Mobile
        1. Creating a Database
        2. Maintaining a Database
          1. Verifying and repairing
          2. Reclaiming space
          3. Modifying properties
          4. Backing up and restoring
        3. Creating, Altering, and Dropping Database Objects
        4. Reading and Updating Data
        5. Error Handling
    25. A. ADO.NET 2.0
      1. Data Provider Enumeration and Factories
      2. Data Provider Enhancements
        1. Asynchronous Processing
        2. Support for SQL Server Notifications
        3. Multiple Active Result Sets
        4. Bulk Copy
        5. Support for New SQL Server Large-Value Data Types
        6. Support for SQL Server User-Defined Types
        7. Support for Snapshot Isolation in Transactions
        8. Database Mirroring Support
        9. Server Enumeration
        10. Support for Retrieving Provider Statistics in SQL Server 2005
        11. Change Password Support
        12. Schema Discovery
      3. Disconnected Class Enhancements
        1. DataSet and DataTable Enhancements
        2. Batch Processing with the DataAdapter
    26. Index
    27. About the Author
    28. Colophon
    29. SPECIAL OFFER: Upgrade this ebook with O’Reilly