Guru's Guide to SQL Server™ Stored Procedures, XML, and HTML, The

Guru's Guide to SQL Server™ Stored Procedures, XML, and HTML, The

by Ken Henderson
Released December 2001
Publisher(s): Addison-Wesley Professional
ISBN: 9780201700466

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

"This is a book that deserves a prominent place by anyone who aspires to be a real professional developer of SQL Server applications."

--from the Foreword by Ron Soukup

The message of this book is that building stored procedures in Transact-SQL is very much like building programs in any other language. It requires the same type of skill, planning, attention to detail, and overall grasp of technology that successful development in other languages requires. To master Transact-SQL, one must first master the fundamental concepts of software development, then build on this foundation by embracing and studying Transact-SQL as a programming language in its own right. This book teaches you how to do that and more.

More than just a catalog of coding tricks and syntax subtleties, The Guru's Guide to SQL Server(TM) Stored Procedures, XML, and HTML explores the philosophy of Transact-SQL programming. It teaches readers how to apply this philosophy in order to develop their own coding techniques and discover their own solutions to real-world programming problems. A follow-up to the widely acclaimed The Guru's Guide to Transact-SQL, this book teaches that stored procedure development does not occur in a vacuum--it involves a wide variety of skills, subjects, and technologies--and helps the reader become a better software engineer, not just a stored procedure expert.

Blending theoretical detail with practical application, this comprehensive reference begins with a foundational overview of SQL Server(TM) stored procedure programming. From there, the focus moves on to best practices and design considerations before progressing to advanced topics and a general philosophy of software craftsmanship. In all, this book provides the most complete coverage of SQL Server stored procedure programming available in one source.

Topics such as user-defined functions, views, triggers, extended procedures, error handling, OLE Automation, database design, and XML are covered in detail. The book spotlights undocumented language features and brings the first application of design patterns to the SQL language. The preview of .NET and a groundbreaking approach to adding arrays to Transact-SQL make for the most thorough and engaging read published to date on SQL Server programming.

The accompanying CD-ROM contains the book's source code. More than 700 SQL scripts, programming utilities, and extended procedures provide a veritable treasure trove of high-quality example code.

Theoretically sound, yet immensely practical, The Guru's Guide to SQL Server(TM) Stored Procedures, XML, and HTML provides developers with the tools they need to become expert stored procedure programmers and better software engineers.



0201700468B11262001

Table of contents

  1. Copyright
  2. Foreword
  3. Preface
  4. Introduction
  5. The Basics
    1. Stored Procedure Primer
      1. What Is a Stored Procedure?
      2. Stored Procedure Advantages
      3. Creating a Stored Procedure
      4. Altering Stored Procedures
      5. Executing Stored Procedures
      6. Extended Stored Procedures
      7. Environmental Issues
      8. Parameters
      9. Flow Control Language
      10. Errors
      11. Nesting
      12. Recursion
      13. Summary
    2. Suggested Conventions
      1. Source Formatting
      2. Coding Conventions
      3. Summary
    3. Common Design Patterns
      1. The Law of Parsimony
      2. Idioms
      3. Design Patterns
      4. Summary
    4. Source Code Management
      1. The Benefits of Source Code Management
      2. The dt Procedures
      3. Best Practices
      4. Version Control from Query Analyzer
      5. Automating Script Generation with Version Control
      6. Summary
    5. Database Design
      1. General Approach
      2. Modeling Tools
      3. The Sample Project
      4. The Five Processes
      5. The Five Phases Examined
      6. Modeling Business Processes
      7. Entity-Relationship Modeling
      8. Relational Data Modeling
      9. Summary
    6. Data Volumes
      1. Approaches to Generating Data
      2. Speed
      3. Summary
  6. Objects
    1. Error Handling
      1. Error Reporting
      2. Handling Errors
      3. Summary
    2. Triggers
      1. Determining What Has Changed
      2. Managing Sequential Values
      3. Trigger Restrictions
      4. INSTEAD OF Triggers
      5. Triggers and Auditing
      6. Transactions
      7. Execution
      8. Calling Stored Procedures
      9. Nested Triggers
      10. Disabling Triggers
      11. Best Practices
      12. Summary
    3. Views
      1. Meta-data
      2. Restrictions
      3. ANSI SQL Schema Views
      4. Updatable Views
      5. The WITH CHECK OPTION Clause
      6. Derived Tables
      7. Parameterized Views
      8. Dynamic Views
      9. Partitioned Views
      10. Indexed Views
      11. Designing Modular Indexed Views
      12. Summary
    4. User-Defined Functions
      1. Scalar Functions
      2. Table-Valued Functions
      3. Inline Functions
      4. Limitations
      5. Meta-data
      6. Creating Your Own System Functions
      7. UDF Cookbook
      8. Summary
  7. HTML, XML, and NET
    1. HTML
      1. Origins
      2. Producing HTML from Transact-SQL
      3. Producing HTML from sp_makewebtask
      4. Summary
    2. Introduction to XML
      1. Wooden Nickels
      2. XML: An Overview
      3. HTML: Simplicity Comes at a Price
      4. XML: A Brief History
      5. XML versus HTML: An Example
      6. Document Type Definitions
      7. XML Schemas
      8. Extensible Stylesheet Language Transformation (XSLT)
      9. Document Object Model
      10. Further Reading
      11. Tools
      12. Summary
    3. XML and SQL Server: HTTP Queries
      1. Accessing SQL Server over HTTP
      2. URL Queries
      3. Template Queries
      4. Summary
    4. XML and SQL Server: Retrieving Data
      1. SELECT…FOR XML
      2. RAW Mode
      3. AUTO Mode
      4. ELEMENTS
      5. EXPLICIT Mode
      6. Mapping Schemas
      7. Summary
    5. XML and SQL Server: OPENXML
      1. The Flags Parameter
      2. Edge Table Format
      3. Inserting Data with OPENXML()
      4. Web Release 1
      5. Limitations
      6. Summary
    6. .NET and the Coming Revolution
      1. .NET: The Future of Applications Development
      2. What Is .NET?
      3. On Microsoft Bashing
      4. Microsoft Bigotry?
      5. Summary
  8. Advanced Topics
    1. Performance Considerations
      1. Indexing
      2. Statistics
      3. Query Optimization
      4. Summary
    2. Debugging and Profiling
      1. Debugging
      2. Profiling
      3. Stress Testing
      4. Summary
    3. Automation
      1. A Brief Overview of COM
      2. SQL Server and COM Automation
      3. Summary
    4. Extended Stored Procedures
      1. Open Data Services
      2. A Simple Example
      3. A Better Example
      4. Making Extended Procedures Easier to Use
      5. Debugging Extended Procedures
      6. Isolating Extended Procedures
      7. xp_setpriority
      8. Summary
    5. Administrative Stored Procedures
      1. sp_readtextfile
      2. sp_diff
      3. sp_generate_script
      4. Sp_start_trace
      5. sp_stop_trace
      6. sp_list_trace
      7. sp_proc_runner
      8. sp_create_backup_job
      9. sp_diffdb
      10. Summary
    6. Undocumented Transact-SQL
      1. What Defines Undocumented?
      2. Undocumented Procedures
      3. Creating INFORMATION_SCHEMA Views
      4. Creating System Functions
      5. Undocumented DBCC Commands
      6. Undocumented Trace Flags
      7. Summary
    7. Arrays
      1. xp_array.dll
      2. Array System Functions
      3. The Pièce de Résistance
      4. Multidimensional Arrays
      5. Summary
  9. Essays on Software Engineering
    1. Creating a Workable Environment
      1. Get Rid of Distractions
      2. Close the Door
      3. Internal Distractions
      4. Form Over Function
      5. Silence Is Golden; Communication Divine
      6. Conclusion
      7. Epilogue
    2. Evolutionary Development
      1. Kaizen
      2. The Benefits of Small Changes
      3. Software
      4. Software Entropy
      5. Refactoring
      6. Selling Management (or Yourself) on Refactoring
      7. When Not to Refactor
      8. Databases
      9. Can You Refactor Instead of Design?
      10. Code Extinction
      11. Extreme Programming
      12. Conclusion
      13. Epilogue
    3. The Gestalt of Testing
      1. Where to Begin
      2. The Futility of Testing
      3. Types of Tests
      4. When to Test
      5. Testing Can Save Time
      6. Testing in the Extreme
      7. Other Types of Testing
      8. Conclusion
      9. Epilogue
    4. References

Product information

  • Title: Guru's Guide to SQL Server™ Stored Procedures, XML, and HTML, The
  • Author(s): Ken Henderson
  • Release date: December 2001
  • Publisher(s): Addison-Wesley Professional
  • ISBN: 9780201700466