Transact-SQL Cookbook Ales Spetic, Jonathan Gennick

Spis treści książki

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

pokaż cały spis treści

ukryj recenzje