In Advanced Transact-SQL for SQL Server 2000, Itzik Ben-Gan and Dr. Tom Moreau explore the powerful capabilities of Transact-SQL. Ben-Gan and Moreau offer solutions to common problems encountered using all versions of SQL Server with a focus on the latest version, SQL Server 2000. The authors offer practical solutions to the everyday problems programmers face and provide significant content on the advanced areas in T-SQL such as joins,subqueries, stored procedures, triggers, user-defined functions (UDFs), indexed views, cascading actions, federated views, hierarchical structures, cursors and more.

• Provides in-depth coverage of T-SQL programming, including the full range of joins, subqueries, derived tables, special datatypes, stored procedures, triggers, user-defined functions (UDFs), indexed views, partitioned views, and cursors
  
• Illustrates by example how to exploit T- SQL to the max, with complete solutions to complex problems such as manipulating complex numbers, maintaining hierarchical structures, dealing with special cascade scenarios, implementing recursion, and more
  
• Offers elegant solutions that provide a step-by-step guide to building the best-performing code
  
• Teaches you how to optimize your queries by providing annotated analyses of execution plans and I/O statistics
  
• Covers all versions of Microsoft SQL Server, focusing on SQL Server 2000
  
• Tested on the final release of SQL Server 2000

Contents

• Foreword.....xxv
  
• About the Authors.....xxvii
  
• Preface.....xxix
  
• Introduction.....xxxix
  
• Chapter 1: Joins in T-SQL.....1
  
• Chapter 2: Subqueries and Derived Tables.....43
  
• Chapter 3: Populating Tables.....81
  
• Chapter 4: Other Data Manipulation Issues.....111
  
• Chapter 5: Summarizing Data.....161
  
• Chapter 6: Special Datatypes and Properties.....197
  
• Chapter 7: Writing Code in T-SQL.....233
  
• Chapter 8: Views.....261
  
• Chapter 9: Stored Procedures.....303
  
• Chapter 10: Triggers.....341
  
• Chapter 11: User-Defined Functions.....375
  
• Chapter 12: Working with Temporary Tables.....435
  
• Chapter 13: Horizontally Partitioned Views.....459
  
• Chapter 14: Implementing Cascading Operations.....503
  
• Chapter 15: Working with Cursors.....553
  
• Chapter 16: Expanding Hierarchies.....579
  
• Chapter 17: Tips & Tricks.....629
  
• Chapter 18: SQL Puzzle Solutions.....671
  
• Appendix A: DML Basics.....735
  
• Appendix B: Checking for ANSI.....747
  
• Appendix C: Analyzing Query Performance.....749
  
• Appendix D: Ownership Chains.....773
  
• Appendix E: Pubs and Northwind Database Schemas.....777
  
• Appendix F: Dynamically Creating Triggers for Cascade Actions.....781
  
• Appendix G: References.....795
  
• Index.....799