Professional SQL Server™ 2005 Programming

Professional SQL Server™ 2005 Programming

by Robert Vieira
Released December 2006
Publisher(s): Wrox
ISBN: 9780764584343

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.

Start your free trial

Book description

Professional SQL Server 2005 Programming shows experienced developers how to master the substantially revamped feature set of the latest release of Microsoft SQL Server. The book begins with a concise overview of the new features of SQL Server that is of interest to experienced developers. This is especially important given the substantial changes to SQL Server with this release. From there, the book quickly moves on to the 'meat' of the title. Beginning-level material has been removed to provide more room for covering new features and more extensive code examples.

Table of contents

  1. Copyright
  2. Credits
  3. About the Author
  4. Dedication
  5. Acknowledgments
  6. Introduction
  7. Being Objective: Re-Examining Objects in SQL Server
    1. So, What Exactly Do We Have Here?
    2. An Overview of Database Objects
    3. SQL Server Data Types
    4. SQL Server Identifiers for Objects
    5. Summary
  8. Tool Time
    1. Books Online
    2. The SQL Server Configuration Manager
    3. The SQL Server Management Studio
    4. SQL Server Business Intelligence Development Studio
    5. SQL Server Integration Services (SSIS)
    6. Reporting Services
    7. Bulk Copy Program (BCP)
    8. SQL Server Profiler
    9. sqlcmd
    10. Summary
  9. Basic T-SQL
    1. The Basic SELECT Statement
    2. Adding Data with the INSERT Statement
    3. Changing What You've Got with the UPDATE Statement
    4. The DELETE Statement
    5. Exploring Alternative Syntax for Joins
    6. The UNION
    7. Summary
  10. Creating and Altering Tables
    1. Object Names in SQL Server
    2. The CREATE Statement
    3. The ALTER Statement
    4. The DROP Statement
    5. Using the GUI Tool
    6. Summary
  11. Reviewing Keys and Constraints
    1. Types of Constraints
    2. Constraint Naming
    3. Key Constraints
    4. CHECK Constraints
    5. DEFAULT Constraints
    6. Disabling Constraints
    7. Rules and Defaults: Cousins of Constraints
    8. Triggers for Data Integrity
    9. Choosing What to Use
    10. Summary
  12. Asking a Better Question: Advanced Queries
    1. What Is a Subquery?
    2. Building a Nested Subquery
    3. Correlated Subqueries
    4. Derived Tables
    5. The EXISTS Operator
    6. Mixing Data Types: CAST and CONVERT
    7. Using External Calls to Perform Complex Actions
    8. Performance Considerations
    9. Summary
  13. Daring to Design
    1. Normalization 201
    2. Relationships
    3. Diagramming
    4. Logical versus Physical Design
    5. Dealing with File-Based Information
    6. Subcategories
    7. Database Reuse
    8. De-Normalization
    9. Partitioning for Scalability
    10. The SQL Server Diagramming Tools
    11. Summary
  14. SQL Server — Storage and Index Structures
    1. SQL Server Storage
    2. Understanding Indexes
    3. Creating, Altering, and Dropping Indexes
    4. Choosing Wisely: Deciding What Index Goes Where and When
    5. Maintaining Your Indexes
    6. Summary
  15. Views
    1. Simple Views
    2. More Complex Views
    3. Editing Views with T-SQL
    4. Dropping Views
    5. Auditing: Displaying Existing Code
    6. Protecting Code: Encrypting Views
    7. About Schema Binding
    8. Making Your View Look Like a Table with VIEW_METADATA
    9. Indexed (Materialized) Views
    10. Partitioned Views
    11. Summary
  16. Scripts and Batches
    1. Script Basics
    2. Batches
    3. SQLCMD
    4. Dynamic SQL: Generating Your Code on the Fly with the EXEC Command
    5. Control-of-Flow Statements
    6. Summary
  17. Getting Procedural: Stored Procedures and User-Defined Functions
    1. Creating the Sproc: Basic Syntax
    2. Changing Stored Procedures with ALTER
    3. Dropping Sprocs
    4. Parameterization
    5. Confirming Success or Failure with Return Values
    6. Dealing with Errors
    7. What a Sproc Offers
    8. Extended Stored Procedures (XPs)
    9. A Brief Look at Recursion
    10. User-Defined Functions (UDFs)
    11. UDFs That Return a Table
    12. Debugging
    13. Summary
  18. Transactions and Locks
    1. Transactions
    2. How the SQL Server Log Works
    3. Locks and Concurrency
    4. Setting the Isolation Level
    5. Dealing with Deadlocks (a.k.a. "A 1205")
    6. Summary
  19. Triggers
    1. What Is a Trigger?
    2. Using Triggers for Data Integrity Rules
    3. Other Common Uses for Triggers
    4. Other Trigger Issues
    5. INSTEAD OF Triggers
    6. IF UPDATE() and COLUMNS_UPDATED
    7. Performance Considerations
    8. Dropping Triggers
    9. Debugging Triggers
    10. Summary
  20. Nothing But NET!
    1. Assemblies 101
    2. Compiling an Assembly
    3. Uploading Your Assembly to SQL Server
    4. Creating Your Assembly-Based Stored Procedure
    5. Creating Scalar User-Defined Functions from Assemblies
    6. Creating Table-Valued Functions
    7. Creating Aggregate Functions
    8. Creating Triggers from Assemblies
    9. Custom Data Types
    10. Summary
  21. SQL Cursors
    1. What Is a Cursor?
    2. The Lifespan of a Cursor
    3. Types of Cursors and Extended Declaration Syntax
    4. Navigating the Cursor: The FETCH Statement
    5. Altering Data within Your Cursor
    6. Summary
  22. XML Integration
    1. The XML Data Type
    2. Retrieving Relational Data in XML Format
    3. A Quick (Very Quick) Reminder of XML Indexes
    4. HTTP Endpoints
    5. Summary
  23. Reporting for Duty, Sir!
    1. Reporting Services 101
    2. Building Simple Report Models
    3. Report Server Projects
    4. Summary
  24. Buying in Bulk: the Bulk Copy Program (BCP) and Other Basic Bulk Operations
    1. BCP Utility
    2. Format Files
    3. BULK INSERT
    4. OPENROWSET (BULK)
    5. Summary
  25. Getting Integrated
    1. Understanding the Problem
    2. An Overview of Packages
    3. Building a Simple Package
    4. Executing Packages
    5. Summary
  26. Replication
    1. Replication Basics
    2. Replication Models
    3. Replication Topology
    4. Planning for Replication
    5. Setting Up Replication in Management Studio
    6. Replication Management Objects (RMO)
    7. Summary
  27. Looking at Things in Full: Full-Text Search
    1. Full-Text Search Architecture
    2. Setting Up Full-Text Indexes and Catalogs
    3. Full-Text Query Syntax
    4. Noise Words
    5. Summary
  28. Security
    1. Security Basics
    2. Security Options
    3. User Rights
    4. Server and Database Roles
    5. Application Roles
    6. More Advanced Security
    7. Certificates and Asymmetric Keys
    8. Summary
  29. Playing a Good Tune: Performance Tuning
    1. When to Tune
    2. Index Choices
    3. Client vs. Server-Side Processing
    4. Strategic De-Normalization
    5. Routine Maintenance
    6. Organizing Your Sprocs Well
    7. Uses for Temporary Tables
    8. Sometimes, It's the Little Things
    9. Hardware Considerations
    10. Troubleshooting
    11. Summary
  30. Administrator
    1. Scheduling Jobs
    2. Backup and Recovery
    3. Index Maintenance
    4. Archiving of Data
    5. Summary
  31. SMO: SQL Management Objects
    1. The History of SQL Server Management Object Models
    2. The SMO Object Model
    3. Walking through Some Examples
    4. Dropping a Database
    5. Backing Up a Database
    6. Scripting
    7. Pulling It All Together
    8. Summary
  32. System Functions
    1. Legacy System Functions (a.k.a. Global Variables)
    2. Aggregate Functions
    3. Cursor Functions
    4. Date and Time Functions
    5. Mathematical Functions
    6. Metadata Functions
    7. Rowset Functions
    8. Security Functions
    9. String Functions
    10. System Functions
    11. Text and Image Functions
  33. Connectivity
    1. Some General Concepts
    2. General Performance Considerations
    3. Connectivity Examples
  34. Getting Service
    1. Analysis Services
    2. Notification Services
    3. Service Broker
  35. Index

Product information

  • Title: Professional SQL Server™ 2005 Programming
  • Author(s): Robert Vieira
  • Release date: December 2006
  • Publisher(s): Wrox
  • ISBN: 9780764584343