Cover image for Transact-SQL Cookbook

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.

Table of Contents

  1. Transact-SQL Cookbook
  2. Dedication
  3. A Note Regarding Supplemental Files
  4. 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
      1. From Ales
      2. From Jonathan
  5. 1. Pivot Tables
    1. 1.1. Using a Pivot Table
      1. 1.1.1. Problem
      2. 1.1.2. Solution
      3. 1.1.3. Discussion
  6. 2. Sets
    1. 2.1. Introduction
      1. 2.1.1. Components
        1. 2.1.1.1. Sets
        2. 2.1.1.2. Elements
        3. 2.1.1.3. Universes
      2. 2.1.2. Set Operations
        1. 2.1.2.1. Contains
        2. 2.1.2.2. Intersection
        3. 2.1.2.3. Union
        4. 2.1.2.4. Complement
        5. 2.1.2.5. Difference
    2. 2.2. The Students Example
    3. 2.3. Implementing Set Difference
      1. 2.3.1. Problem
      2. 2.3.2. Solution
        1. 2.3.2.1. Subtracting one set from another
        2. 2.3.2.2. Subtracting one set from all others
      3. 2.3.3. Discussion
        1. 2.3.3.1. Subtracting one set from another
        2. 2.3.3.2. Subtracting one set from all others
        3. 2.3.3.3. Subtracting other sets from one
    4. 2.4. Comparing Two Sets for Equality
      1. 2.4.1. Problem
      2. 2.4.2. Solution
      3. 2.4.3. Discussion
    5. 2.5. Implementing Partial Intersection
      1. 2.5.1. Problem
      2. 2.5.2. Solution
      3. 2.5.3. Discussion
    6. 2.6. Implementing Full Intersection
      1. 2.6.1. Problem
      2. 2.6.2. Solution
      3. 2.6.3. Discussion
    7. 2.7. Classifying Subsets
      1. 2.7.1. Problem
      2. 2.7.2. Solution
      3. 2.7.3. Discussion
    8. 2.8. Summarizing Classes of Sets
      1. 2.8.1. Problem
      2. 2.8.2. Solution
      3. 2.8.3. Discussion
    9. 2.9. Aggregating Aggregates
      1. 2.9.1. Problem
      2. 2.9.2. Solution
      3. 2.9.3. Discussion
    10. 2.10. Summarizing Aggregated Classes
      1. 2.10.1. Problem
      2. 2.10.2. Solution
      3. 2.10.3. Discussion
    11. 2.11. Including Nonaggregated Columns
      1. 2.11.1. Problem
      2. 2.11.2. Solution
      3. 2.11.3. Discussion
    12. 2.12. Finding the Top N Values in a Set
      1. 2.12.1. Problem
      2. 2.12.2. Solution
      3. 2.12.3. Discussion
        1. 2.12.3.1. Using TOP
        2. 2.12.3.2. Using a self-join
    13. 2.13. Reporting the Size of a Set's Complement
      1. 2.13.1. Problem
      2. 2.13.2. Solution
      3. 2.13.3. Discussion
    14. 2.14. Finding the Complement of a Set
      1. 2.14.1. Problem
      2. 2.14.2. Solution
        1. 2.14.2.1. Step 1: Create the Pivot table
        2. 2.14.2.2. Step 2: Run the query
      3. 2.14.3. Discussion
    15. 2.15. Finding the Complement of a Missing Set
      1. 2.15.1. Problem
      2. 2.15.2. Solution
        1. 2.15.2.1. Solution 1: Define empty rows in the Students table
        2. 2.15.2.2. Solution 2: Create a student master table
      3. 2.15.3. Discussion
    16. 2.16. Finding Complements of Sets with Different Universes
      1. 2.16.1. Problem
      2. 2.16.2. Solution
      3. 2.16.3. Discussion
    17. 2.17. Comparing a Set with Its Universe
      1. 2.17.1. Problem
      2. 2.17.2. Solution
      3. 2.17.3. Discussion
    18. 2.18. Dynamic Classification System
      1. 2.18.1. Problem
      2. 2.18.2. Solution
        1. 2.18.2.1. Step 1: Creating the rules table
        2. 2.18.2.2. Step 2: Running the query
      3. 2.18.3. Discussion
        1. 2.18.3.1. The query
  7. 3. Data Structures
    1. 3.1. Types of Data Structures
      1. 3.1.1. Lists
        1. 3.1.1.1. Cumulative aggregation queries
        2. 3.1.1.2. Regions
        3. 3.1.1.3. Runs
        4. 3.1.1.4. Sequences
      2. 3.1.2. Stacks and Queues
        1. 3.1.2.1. Stacks
        2. 3.1.2.2. Queues
        3. 3.1.2.3. Priority queues
      3. 3.1.3. Arrays and Matrices
        1. 3.1.3.1. Arrays
        2. 3.1.3.2. Matrices
    2. 3.2. Working Example
      1. 3.2.1. Linear Structures
      2. 3.2.2. Arrays
      3. 3.2.3. Matrices
    3. 3.3. Finding Regions
      1. 3.3.1. Problem
      2. 3.3.2. Solution
      3. 3.3.3. Discussion
    4. 3.4. Reporting Region Boundaries
      1. 3.4.1. Problem
      2. 3.4.2. Solution
      3. 3.4.3. Discussion
    5. 3.5. Limiting Region Size
      1. 3.5.1. Problem
      2. 3.5.2. Solution
      3. 3.5.3. Discussion
    6. 3.6. Ranking Regions by Size
      1. 3.6.1. Problem
      2. 3.6.2. Solution
      3. 3.6.3. Discussion
    7. 3.7. Working with Sequences
      1. 3.7.1. Problem
      2. 3.7.2. Solution
      3. 3.7.3. Discussion
    8. 3.8. Working with Runs
      1. 3.8.1. Problem
      2. 3.8.2. Solutions
      3. 3.8.3. Discussion
    9. 3.9. Cumulative Aggregates in Lists
      1. 3.9.1. Problem
      2. 3.9.2. Solution
      3. 3.9.3. Discussion
    10. 3.10. Implementing a Stack
      1. 3.10.1. Problem
      2. 3.10.2. Solution
        1. 3.10.2.1. TOP function in SQL
        2. 3.10.2.2. POP function in SQL
        3. 3.10.2.3. PUSH function in SQL
      3. 3.10.3. Discussion
    11. 3.11. Implementing Queues
      1. 3.11.1. Problem
      2. 3.11.2. Solution
        1. 3.11.2.1. TOP function in SQL
        2. 3.11.2.2. DEQUEUE function in SQL
        3. 3.11.2.3. ENQUEUE function in SQL
      3. 3.11.3. Discussion
    12. 3.12. Implementing Priority Queues
      1. 3.12.1. Problem
      2. 3.12.2. Solution
        1. 3.12.2.1. TOP function in SQL
        2. 3.12.2.2. DEQUEUE function in SQL
        3. 3.12.2.3. ENQUEUE function in SQL
      3. 3.12.3. Discussion
    13. 3.13. Comparing Two Rows in an Array
      1. 3.13.1. Problem
      2. 3.13.2. Solution
      3. 3.13.3. Discussion
    14. 3.14. Printing Matrices and Arrays
      1. 3.14.1. Problem
      2. 3.14.2. Solution
      3. 3.14.3. Discussion
    15. 3.15. Transposing a Matrix
      1. 3.15.1. Problem
      2. 3.15.2. Solution
      3. 3.15.3. Discussion
    16. 3.16. Calculating a Matrix Trace
      1. 3.16.1. Problem
      2. 3.16.2. Solution
      3. 3.16.3. Discussion
    17. 3.17. Comparing Two Matrices for Size
      1. 3.17.1. Problem
      2. 3.17.2. Solution
      3. 3.17.3. Discussion
    18. 3.18. Adding and Subtracting Matrices
      1. 3.18.1. Problem
      2. 3.18.2. Solution
      3. 3.18.3. Discussion
    19. 3.19. Multiplying Matrices
      1. 3.19.1. Problem
      2. 3.19.2. Solution
        1. 3.19.2.1. Multiplying a matrix by a scalar value
        2. 3.19.2.2. Multiplying a matrix with a vector
        3. 3.19.2.3. Multiplying two matrices
      3. 3.19.3. Discussion
        1. 3.19.3.1. Multiplying by a scalar
        2. 3.19.3.2. Multiplying by a vector
        3. 3.19.3.3. Multiplying by a matrix
        4. 3.19.3.4. Squaring a matrix
  8. 4. Hierarchies in SQL
    1. 4.1. Types of Hierarchies
      1. 4.1.1. Specialized Hierarchies
      2. 4.1.2. General Hierarchies
    2. 4.2. Creating a Permission Hierarchy
      1. 4.2.1. Problem
      2. 4.2.2. Solution
      3. 4.2.3. Discussion
        1. 4.2.3.1. Checking permissions for a trader
        2. 4.2.3.2. Revoking permissions for a group
    3. 4.3. Changing Individual Permissions
      1. 4.3.1. Problem
      2. 4.3.2. Solution
      3. 4.3.3. Discussion
    4. 4.4. Adding New Individual Permissions
      1. 4.4.1. Problem
      2. 4.4.2. Solution
      3. 4.4.3. Discussion
    5. 4.5. Centralizing Authorization Logic
      1. 4.5.1. Problem
      2. 4.5.2. Solution
      3. 4.5.3. Discussion
    6. 4.6. Implementing General Hierarchies
      1. 4.6.1. Problem
      2. 4.6.2. Solution
        1. 4.6.2.1. List all leaf nodes
        2. 4.6.2.2. List all nodes that are not leaf nodes
        3. 4.6.2.3. Find the most expensive vertices
        4. 4.6.2.4. Find the immediate children of a node
        5. 4.6.2.5. Find the root
      3. 4.6.3. Discussion
    7. 4.7. Traversing Hierarchies Recursively
      1. 4.7.1. Problem
      2. 4.7.2. Solution
      3. 4.7.3. Discussion
    8. 4.8. Manipulating Hierarchies Recursively
      1. 4.8.1. Problem
      2. 4.8.2. Solution
      3. 4.8.3. Discussion
    9. 4.9. Aggregating Hierarchies
      1. 4.9.1. Problem
      2. 4.9.2. Solution
      3. 4.9.3. Discussion
    10. 4.10. Preparing Multilevel Operations
      1. 4.10.1. Problem
      2. 4.10.2. Solution
      3. 4.10.3. Discussion
    11. 4.11. Aggregating Hierarchies Revised
      1. 4.11.1. Problem
      2. 4.11.2. Solution
      3. 4.11.3. Discussion
  9. 5. Temporal Data
    1. 5.1. Introduction
      1. 5.1.1. Granularity
      2. 5.1.2. Temporal Datatypes in Transact-SQL
      3. 5.1.3. Date and Time Input
      4. 5.1.4. Temporal Types
        1. 5.1.4.1. Instants
        2. 5.1.4.2. Duration
        3. 5.1.4.3. Periods
    2. 5.2. The Schedules Example
    3. 5.3. Enforcing Granularity Rules
      1. 5.3.1. Problem
      2. 5.3.2. Solution
      3. 5.3.3. Discussion
    4. 5.4. Storing Out-of-Range Temporal Values
      1. 5.4.1. Problem
      2. 5.4.2. Solution
      3. 5.4.3. Discussion
    5. 5.5. Deriving the First and Last Dates of the Month
      1. 5.5.1. Problem
      2. 5.5.2. Solution
      3. 5.5.3. Discussion
    6. 5.6. Printing Calendars
      1. 5.6.1. Problem
      2. 5.6.2. Solution
      3. 5.6.3. Discussion
    7. 5.7. Calculating Durations
      1. 5.7.1. Problem
      2. 5.7.2. Solution
      3. 5.7.3. Discussion
    8. 5.8. Reporting Durations
      1. 5.8.1. Problem
      2. 5.8.2. Solution
      3. 5.8.3. Discussion
    9. 5.9. Querying Periods
      1. 5.9.1. Problem
      2. 5.9.2. Solution
      3. 5.9.3. Discussion
    10. 5.10. Querying Periods and Respecting Granularity
      1. 5.10.1. Problem
      2. 5.10.2. Solution
      3. 5.10.3. Discussion
    11. 5.11. Finding Available Periods
      1. 5.11.1. Problem
      2. 5.11.2. Solution
      3. 5.11.3. Discussion
    12. 5.12. Finding Common Available Periods
      1. 5.12.1. Problem
      2. 5.12.2. Solution
      3. 5.12.3. Discussion
    13. 5.13. Excluding Recurrent Events
      1. 5.13.1. Problem
      2. 5.13.2. Solution
      3. 5.13.3. Discussion
    14. 5.14. Excluding Nonrecurring Events
      1. 5.14.1. Problem
      2. 5.14.2. Solution
      3. 5.14.3. Discussion
    15. 5.15. Finding Continuous Periods
      1. 5.15.1. Problem
      2. 5.15.2. Solution
      3. 5.15.3. Discussion
    16. 5.16. Using Calendar Information with Periods
      1. 5.16.1. Problem
      2. 5.16.2. Solution
      3. 5.16.3. Discussion
    17. 5.17. Using Calendar Information with Durations
      1. 5.17.1. Problems
      2. 5.17.2. Solution
      3. 5.17.3. Discussion
  10. 6. Audit Logging
    1. 6.1. Audit Logs
      1. 6.1.1. Your Objectives for an Audit Log
      2. 6.1.2. Row-Level Versus Activity Logging
      3. 6.1.3. Database Tables Versus Operating System Files
      4. 6.1.4. Storage Units
      5. 6.1.5. Global Versus Local Logs
    2. 6.2. The Warehouse Example
    3. 6.3. Row-Level Logging
      1. 6.3.1. Problem
      2. 6.3.2. Solution
      3. 6.3.3. Discussion
        1. 6.3.3.1. Log table structure
        2. 6.3.3.2. Log triggers
    4. 6.4. Reporting Log Events
      1. 6.4.1. Problem
      2. 6.4.2. Solution
      3. 6.4.3. Discussion
    5. 6.5. Generating Current Snapshots
      1. 6.5.1. Problem
      2. 6.5.2. Solution
      3. 6.5.3. Discussion
    6. 6.6. Generating Time-Restricted Snapshots
      1. 6.6.1. Problem
      2. 6.6.2. Solution
      3. 6.6.3. Discussion
    7. 6.7. Undoing Table Changes
      1. 6.7.1. Problem
      2. 6.7.2. Solution
      3. 6.7.3. Discussion
    8. 6.8. Minimizing Audit-Log Space Consumption
      1. 6.8.1. Problem
      2. 6.8.2. Solution
        1. 6.8.2.1. Step 1: Create the audit table
        2. 6.8.2.2. Step 2: Implement the audit triggers
      3. 6.8.3. Discussion
        1. 6.8.3.1. Snapshot generation
        2. 6.8.3.2. Deleted records
        3. 6.8.3.3. Dealing with NULL values
    9. 6.9. Online Account Balancing
      1. 6.9.1. Problem
      2. 6.9.2. Solution
      3. 6.9.3. Discussion
    10. 6.10. Activity-Level Logging
      1. 6.10.1. Problem
      2. 6.10.2. Solution
        1. 6.10.2.1. Step 1: Create a global log table
        2. 6.10.2.2. Step 2: Create an event master table
        3. 6.10.2.3. Step 3: Create a global severity-limit variable
        4. 6.10.2.4. Step 4: Create a stored procedure to log events
      3. 6.10.3. Discussion
        1. 6.10.3.1. Defining severity levels
        2. 6.10.3.2. Extending error messages
        3. 6.10.3.3. Reporting
    11. 6.11. Partitioning Large Log Tables
      1. 6.11.1. Problem
      2. 6.11.2. Solution
      3. 6.11.3. Discussion
    12. 6.12. Server Push
      1. 6.12.1. Problem
      2. 6.12.2. Solution
      3. 6.12.3. Discussion
        1. 6.12.3.1. Ticketing
        2. 6.12.3.2. Polling as an alternative to triggers
        3. 6.12.3.3. Client stability
        4. 6.12.3.4. Support for multiple tables
  11. 7. Importing and Transforming Data
    1. 7.1. Considerations When Importing Data
      1. 7.1.1. Bulk Importing
      2. 7.1.2. Normalized Data
        1. 7.1.2.1. Eliminate repeating columns
        2. 7.1.2.2. Factor out dependent columns
        3. 7.1.2.3. Reduced memory requirements
      3. 7.1.3. Master/Detail Framework
      4. 7.1.4. Duplicates
        1. 7.1.4.1. Counting duplicates
    2. 7.2. Working Examples
      1. 7.2.1. Bookstore
      2. 7.2.2. Rankings
      3. 7.2.3. Scores
      4. 7.2.4. Dissertations
    3. 7.3. Importing External Data
      1. 7.3.1. Problem
      2. 7.3.2. Solution
        1. 7.3.2.1. Step 1: Make sure that the data to be imported is in an ASCII file
        2. 7.3.2.2. Step 2: Filter the data for obvious inconsistencies
        3. 7.3.2.3. Step 3: Import the data into your database
        4. 7.3.2.4. Step 4: Check for errors
      3. 7.3.3. Discussion
    4. 7.4. Importing Data into a Live System
      1. 7.4.1. Problem
      2. 7.4.2. Solution
        1. 7.4.2.1. Step 1: Create buffer tables
        2. 7.4.2.2. Step 2: Build validation procedures
        3. 7.4.2.3. Step 3: Insert rows
        4. 7.4.2.4. Step 4: Check for rejected data
      3. 7.4.3. Discussion
    5. 7.5. Importing with a Single Procedure
      1. 7.5.1. Problem
      2. 7.5.2. Solution
      3. 7.5.3. Discussion
    6. 7.6. Hiding the Import Procedure
      1. 7.6.1. Problem
      2. 7.6.2. Solution
      3. 7.6.3. Discussion
    7. 7.7. Folding Tables
      1. 7.7.1. Problem
      2. 7.7.2. Solution
        1. 7.7.2.1. Using a union query to fold data
        2. 7.7.2.2. Using a self-join to fold data
        3. 7.7.2.3. Using the Pivot table to fold data
      3. 7.7.3. Discussion
    8. 7.8. Pivoting Tables
      1. 7.8.1. Problem
      2. 7.8.2. Solution
        1. 7.8.2.1. Step 1: Identifying key items
        2. 7.8.2.2. Step 2: Writing the query
        3. 7.8.2.3. Step 3: Run the query
      3. 7.8.3. Discussion
    9. 7.9. Joining Arrays with Tables
      1. 7.9.1. Problem
      2. 7.9.2. Solution
      3. 7.9.3. Discussion
    10. 7.10. Joining Arrays with Master Tables
      1. 7.10.1. Problem
      2. 7.10.2. Solution
      3. 7.10.3. Discussion
    11. 7.11. Joining Arrays with Multiple Master Records
      1. 7.11.1. Problem
      2. 7.11.2. Solution
      3. 7.11.3. Discussion
    12. 7.12. Extracting Master Records from Tables
      1. 7.12.1. Problem
      2. 7.12.2. Solution
        1. 7.12.2.1. Step 1: Creating the new master table
        2. 7.12.2.2. Step 2: Extracting master record values
        3. 7.12.2.3. Step 3: Creating the new fact table
        4. 7.12.2.4. Step 4: Populating the new fact table
      3. 7.12.3. Discussion
    13. 7.13. Generating Master Records Online
      1. 7.13.1. Problem
      2. 7.13.2. Solution
        1. 7.13.2.1. Step 1: Setting up the tables
        2. 7.13.2.2. Step 2: Creating the INSERT substitute
        3. 7.13.2.3. Step 3: Supporting the SELECT statement
        4. 7.13.2.4. Step 4: Supporting the UPDATE statement
        5. 7.13.2.5. Step 5: Supporting the DELETE statement
      3. 7.13.3. Discussion
        1. 7.13.3.1. The INSERT trigger
        2. 7.13.3.2. The UPDATE trigger
    14. 7.14. Working with Duplicates
      1. 7.14.1. Problem
      2. 7.14.2. Solution
        1. 7.14.2.1. Reduction
        2. 7.14.2.2. Selective reduction
        3. 7.14.2.3. Selecting duplicates
        4. 7.14.2.4. Counting occurrences
        5. 7.14.2.5. Selecting by number of occurrences
        6. 7.14.2.6. Selecting nonduplicates
        7. 7.14.2.7. Selecting duplicates with an odd occurrence count
        8. 7.14.2.8. Selecting duplicates with an even occurrence count
        9. 7.14.2.9. Deleting duplicate rows
        10. 7.14.2.10. Preventing duplicates
      3. 7.14.3. Discussion
  12. 8. Statistics in SQL
    1. 8.1. Statistical Concepts
      1. 8.1.1. Mean
      2. 8.1.2. Mode
      3. 8.1.3. Median
      4. 8.1.4. Standard Deviation and Variance
      5. 8.1.5. Standard Error
      6. 8.1.6. Confidence Intervals
        1. 8.1.6.1. Creating the t-distribution table
        2. 8.1.6.2. Calculating the confidence interval
      7. 8.1.7. Correlation
      8. 8.1.8. Autocorrelation
      9. 8.1.9. Moving Averages
    2. 8.2. The Light-Bulb Factory Example
      1. 8.2.1. Bulb-Life Data
      2. 8.2.2. Sales Data
    3. 8.3. Calculating a Mean
      1. 8.3.1. Problem
      2. 8.3.2. Solution
      3. 8.3.3. Discussion
    4. 8.4. Calculating a Mode
      1. 8.4.1. Problem
      2. 8.4.2. Solution
      3. 8.4.3. Discussion
    5. 8.5. Calculating a Median
      1. 8.5.1. Problem
      2. 8.5.2. Solution
      3. 8.5.3. Discussion
    6. 8.6. Calculating Standard Deviation, Variance, and Standard Error
      1. 8.6.1. Problem
      2. 8.6.2. Solution
      3. 8.6.3. Discussion
    7. 8.7. Building Confidence Intervals
      1. 8.7.1. Problem
      2. 8.7.2. Solution
      3. 8.7.3. Discussion
    8. 8.8. Calculating Correlation
      1. 8.8.1. Problem
      2. 8.8.2. Solution
      3. 8.8.3. Discussion
    9. 8.9. Exploring Patterns with Autocorrelation
      1. 8.9.1. Problem
      2. 8.9.2. Solution
      3. 8.9.3. Discussion
    10. 8.10. Using a Simple Moving Average
      1. 8.10.1. Problem
      2. 8.10.2. Solution
      3. 8.10.3. Discussion
    11. 8.11. Extending Moving Averages
      1. 8.11.1. Problem
      2. 8.11.2. Solution
      3. 8.11.3. Discussion
  13. A. The T-Distribution Table
  14. About the Authors
  15. Colophon
  16. Copyright