Transact-SQL Cookbook

Transact-SQL Cookbook

by Ales Spetic, Jonathan Gennick
Released March 2002
Publisher(s): O'Reilly Media, Inc.
ISBN: 9781565927568

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

This unique cookbook contains a wealth of solutions to problems that SQL programmers face all the time. The recipes inside range from how to perform simple tasks, like importing external data, to ways of handling issues that are more complicated, like set algebra. Authors Ales Spetic and Jonathan Gennick, two authorities with extensive database and SQL programming experience, include a discussion with each recipe to explain the logic and concepts underlying the solution.SQL (Structured Query Language) is the closest thing to a standard query language that currently exists, and Transact-SQL -- a full-featured programming language that dramatically extends the power of SQL -- is the procedural language of choice for both Microsoft SQL Server and Sybase SQL Server systems. The Transact-SQL Cookbook is designed so you can use the recipes directly, as a source of ideas, or as a way to learn a little more about SQL and what you can do with it. Topics covered include:

  • Audit logging. In addition to recipes for implementing an audit log, this chapter also includes recipes for: improving performance where large log tables are involved; supporting multiple-languages; and simulating server push.
  • Hierarchies. Recipes show you how to manipulate hierarchical data using Transact-SQL.
  • Importing data. This chapter introduces concepts like normalization and recipes useful for working with imported data tables.
  • Sets. Recipes demonstrate different operations, such as how to find common elements, summarize the data in a set, and find the element in a set that represents an extreme.
  • Statistics. This chapter?s recipes show you how to effectively use SQL for common statistical operations from means and standard deviations to weighted moving averages.
  • Temporal data. Recipes demonstrate how to construct queries against time-based data.
  • Data Structures. This chapter shows how to manipulate data structures like stacks, queues, matrices, and arrays.
With an abundance of recipes to help you get your job done more efficiently, the Transact-SQL Cookbook is sure to become an essential part of your library.

Publisher resources

View/Submit Errata

Table of contents

  1. Dedication
  2. A Note Regarding Supplemental Files
  3. Preface
    1. Why We Wrote This Book
    2. Audience for This Book
    3. Which Platform and Version?
    4. Structure of This Book
    5. Conventions Used in This Book
    6. About the Code
    7. Comments and Questions
    8. Acknowledgments
  4. 1. Pivot Tables
    1. 1.1. Using a Pivot Table
  5. 2. Sets
    1. 2.1. Introduction
    2. 2.2. The Students Example
    3. 2.3. Implementing Set Difference
    4. 2.4. Comparing Two Sets for Equality
    5. 2.5. Implementing Partial Intersection
    6. 2.6. Implementing Full Intersection
    7. 2.7. Classifying Subsets
    8. 2.8. Summarizing Classes of Sets
    9. 2.9. Aggregating Aggregates
    10. 2.10. Summarizing Aggregated Classes
    11. 2.11. Including Nonaggregated Columns
    12. 2.12. Finding the Top N Values in a Set
    13. 2.13. Reporting the Size of a Set’s Complement
    14. 2.14. Finding the Complement of a Set
    15. 2.15. Finding the Complement of a Missing Set
    16. 2.16. Finding Complements of Sets with Different Universes
    17. 2.17. Comparing a Set with Its Universe
    18. 2.18. Dynamic Classification System
  6. 3. Data Structures
    1. 3.1. Types of Data Structures
    2. 3.2. Working Example
    3. 3.3. Finding Regions
    4. 3.4. Reporting Region Boundaries
    5. 3.5. Limiting Region Size
    6. 3.6. Ranking Regions by Size
    7. 3.7. Working with Sequences
    8. 3.8. Working with Runs
    9. 3.9. Cumulative Aggregates in Lists
    10. 3.10. Implementing a Stack
    11. 3.11. Implementing Queues
    12. 3.12. Implementing Priority Queues
    13. 3.13. Comparing Two Rows in an Array
    14. 3.14. Printing Matrices and Arrays
    15. 3.15. Transposing a Matrix
    16. 3.16. Calculating a Matrix Trace
    17. 3.17. Comparing Two Matrices for Size
    18. 3.18. Adding and Subtracting Matrices
    19. 3.19. Multiplying Matrices
  7. 4. Hierarchies in SQL
    1. 4.1. Types of Hierarchies
    2. 4.2. Creating a Permission Hierarchy
    3. 4.3. Changing Individual Permissions
    4. 4.4. Adding New Individual Permissions
    5. 4.5. Centralizing Authorization Logic
    6. 4.6. Implementing General Hierarchies
    7. 4.7. Traversing Hierarchies Recursively
    8. 4.8. Manipulating Hierarchies Recursively
    9. 4.9. Aggregating Hierarchies
    10. 4.10. Preparing Multilevel Operations
    11. 4.11. Aggregating Hierarchies Revised
  8. 5. Temporal Data
    1. 5.1. Introduction
    2. 5.2. The Schedules Example
    3. 5.3. Enforcing Granularity Rules
    4. 5.4. Storing Out-of-Range Temporal Values
    5. 5.5. Deriving the First and Last Dates of the Month
    6. 5.6. Printing Calendars
    7. 5.7. Calculating Durations
    8. 5.8. Reporting Durations
    9. 5.9. Querying Periods
    10. 5.10. Querying Periods and Respecting Granularity
    11. 5.11. Finding Available Periods
    12. 5.12. Finding Common Available Periods
    13. 5.13. Excluding Recurrent Events
    14. 5.14. Excluding Nonrecurring Events
    15. 5.15. Finding Continuous Periods
    16. 5.16. Using Calendar Information with Periods
    17. 5.17. Using Calendar Information with Durations
  9. 6. Audit Logging
    1. 6.1. Audit Logs
    2. 6.2. The Warehouse Example
    3. 6.3. Row-Level Logging
    4. 6.4. Reporting Log Events
    5. 6.5. Generating Current Snapshots
    6. 6.6. Generating Time-Restricted Snapshots
    7. 6.7. Undoing Table Changes
    8. 6.8. Minimizing Audit-Log Space Consumption
    9. 6.9. Online Account Balancing
    10. 6.10. Activity-Level Logging
    11. 6.11. Partitioning Large Log Tables
    12. 6.12. Server Push
  10. 7. Importing and Transforming Data
    1. 7.1. Considerations When Importing Data
    2. 7.2. Working Examples
    3. 7.3. Importing External Data
    4. 7.4. Importing Data into a Live System
    5. 7.5. Importing with a Single Procedure
    6. 7.6. Hiding the Import Procedure
    7. 7.7. Folding Tables
    8. 7.8. Pivoting Tables
    9. 7.9. Joining Arrays with Tables
    10. 7.10. Joining Arrays with Master Tables
    11. 7.11. Joining Arrays with Multiple Master Records
    12. 7.12. Extracting Master Records from Tables
    13. 7.13. Generating Master Records Online
    14. 7.14. Working with Duplicates
  11. 8. Statistics in SQL
    1. 8.1. Statistical Concepts
    2. 8.2. The Light-Bulb Factory Example
    3. 8.3. Calculating a Mean
    4. 8.4. Calculating a Mode
    5. 8.5. Calculating a Median
    6. 8.6. Calculating Standard Deviation, Variance, and Standard Error
    7. 8.7. Building Confidence Intervals
    8. 8.8. Calculating Correlation
    9. 8.9. Exploring Patterns with Autocorrelation
    10. 8.10. Using a Simple Moving Average
    11. 8.11. Extending Moving Averages
  12. A. The T-Distribution Table
  13. About the Authors
  14. Colophon
  15. Copyright

Product information

  • Title: Transact-SQL Cookbook
  • Author(s): Ales Spetic, Jonathan Gennick
  • Release date: March 2002
  • Publisher(s): O'Reilly Media, Inc.
  • ISBN: 9781565927568