Programming SQL Server 2005

Programming SQL Server 2005

by Bill Hamilton
Released February 2006
Publisher(s): O'Reilly Media, Inc.
ISBN: 9780596004798

Read it now on the O’Reilly learning platform with a 10-day free trial.

O’Reilly members get unlimited access to books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.

Buy on Amazon Buy on ebooks.com
Start your free trial

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.

Publisher resources

View/Submit Errata

Table of contents

  1. A Note Regarding Supplemental Files
  2. 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
  3. 1. Introduction
    1. Contents of This Book
    2. What’s Not in This Book
  4. 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.  
        1. SQL Server command-line tool (sqlcmd utility)
  5. 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
  6. 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
  7. 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
  8. 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
  9. 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
  10. 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
  11. 9. SQL Server Management Objects (SMO)
    1. SMO Object Model
    2. Creating an SMO Project in Visual Studio .NET
    3. A Simple SMO Application
  12. 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
  13. 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
  14. 12. SQL Server Management Objects (SMO) Utility Classes
    1. Scripting
    2. Backing Up and Restoring Data
    3. Transferring Data
    4. Tracing
    5. Database Mail
  15. 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
  16. 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
  17. 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
  18. 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
  19. 17. Service Broker
    1. Architecture
    2. Programming Service Broker
      1. Enumerating Service Broker Objects
      2. Implementing a Service Broker Service
  20. 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
  21. 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
  22. 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)
  23. 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
  24. 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
  25. Index
  26. About the Author
  27. Colophon
  28. Copyright

Product information

  • Title: Programming SQL Server 2005
  • Author(s): Bill Hamilton
  • Release date: February 2006
  • Publisher(s): O'Reilly Media, Inc.
  • ISBN: 9780596004798