1. Chapter 1 Pivot Tables

   1. Using a Pivot Table

2. Chapter 2 Sets

   1. Introduction

   2. The Students Example

   3. Implementing Set Difference

   4. Comparing Two Sets for Equality

   5. Implementing Partial Intersection

   6. Implementing Full Intersection

   7. Classifying Subsets

   8. Summarizing Classes of Sets

   9. Aggregating Aggregates

   10. Summarizing Aggregated Classes

   11. Including Nonaggregated Columns

   12. Finding the Top N Values in a Set

   13. Reporting the Size of a Set's Complement

   14. Finding the Complement of a Set

   15. Finding the Complement of a Missing Set

   16. Finding Complements of Sets with Different Universes

   17. Comparing a Set with Its Universe

   18. Dynamic Classification System

3. Chapter 3 Data Structures

   1. Types of Data Structures

   2. Working Example

   3. Finding Regions

   4. Reporting Region Boundaries

   5. Limiting Region Size

   6. Ranking Regions by Size

   7. Working with Sequences

   8. Working with Runs

   9. Cumulative Aggregates in Lists

   10. Implementing a Stack

   11. Implementing Queues

   12. Implementing Priority Queues

   13. Comparing Two Rows in an Array

   14. Printing Matrices and Arrays

   15. Transposing a Matrix

   16. Calculating a Matrix Trace

   17. Comparing Two Matrices for Size

   18. Adding and Subtracting Matrices

   19. Multiplying Matrices

4. Chapter 4 Hierarchies in SQL

   1. Types of Hierarchies

   2. Creating a Permission Hierarchy

   3. Changing Individual Permissions

   4. Adding New Individual Permissions

   5. Centralizing Authorization Logic

   6. Implementing General Hierarchies

   7. Traversing Hierarchies Recursively

   8. Manipulating Hierarchies Recursively

   9. Aggregating Hierarchies

   10. Preparing Multilevel Operations

   11. Aggregating Hierarchies Revised

5. Chapter 5 Temporal Data

   1. Introduction

   2. The Schedules Example

   3. Enforcing Granularity Rules

   4. Storing Out-of-Range Temporal Values

   5. Deriving the First and Last Dates of the Month

   6. Printing Calendars

   7. Calculating Durations

   8. Reporting Durations

   9. Querying Periods

   10. Querying Periods and Respecting Granularity

   11. Finding Available Periods

   12. Finding Common Available Periods

   13. Excluding Recurrent Events

   14. Excluding Nonrecurring Events

   15. Finding Continuous Periods

   16. Using Calendar Information with Periods

   17. Using Calendar Information with Durations

6. Chapter 6 Audit Logging

   1. Audit Logs

   2. The Warehouse Example

   3. Row-Level Logging

   4. Reporting Log Events

   5. Generating Current Snapshots

   6. Generating Time-Restricted Snapshots

   7. Undoing Table Changes

   8. Minimizing Audit-Log Space Consumption

   9. Online Account Balancing

   10. Activity-Level Logging

   11. Partitioning Large Log Tables

   12. Server Push

7. Chapter 7 Importing and Transforming Data

   1. Considerations When Importing Data

   2. Working Examples

   3. Importing External Data

   4. Importing Data into a Live System

   5. Importing with a Single Procedure

   6. Hiding the Import Procedure

   7. Folding Tables

   8. Pivoting Tables

   9. Joining Arrays with Tables

   10. Joining Arrays with Master Tables

   11. Joining Arrays with Multiple Master Records

   12. Extracting Master Records from Tables

   13. Generating Master Records Online

   14. Working with Duplicates

8. Chapter 8 Statistics in SQL

   1. Statistical Concepts

   2. The Light-Bulb Factory Example

   3. Calculating a Mean

   4. Calculating a Mode

   5. Calculating a Median

   6. Calculating Standard Deviation, Variance, and Standard Error

   7. Building Confidence Intervals

   8. Calculating Correlation

   9. Exploring Patterns with Autocorrelation

   10. Using a Simple Moving Average

   11. Extending Moving Averages

1. Appendix A The T-Distribution Table

2. Colophon

Our look is the result of reader comments, our own experimentation, and feedback from distribution channels. Distinctive covers complement our distinctive approach to technical topics, breathing personality and life into potentially dry subjects. The animal on the cover of Transact-SQL Cookbook is a tree swift. Swifts are small, swallow-like birds related to hummingbirds. They are found all over the world, especially in the tropics. There are many different species of swifts that are specific to certain regions.

Swifts range from 3.5 to 9 inches in length. Their powerful, streamlined bodies have long wings; small, weak legs; and small feet. Swifts perch on vertical surfaces, but, due to their weak legs, once they perch, they have a difficult time returning to flight. Because of this, swifts do nearly everything on the wing, including feeding, drinking, bathing, courting, and sometimes mating. Their strong wings also make them rapid fliers.

Jeffrey Holcomb was the production editor and proofreader for Transact-SQL Cookbook . Tatiana Apandi Diaz was the copyeditor. Linley Dolby, Leanne Soylemez, and Claire Cloutier provided quality control. Phil Dangler, Leanne Soylemez, and David Chu provided production assistance. Johnna VanHoose Dinse wrote the index. Ellie Volckhausen designed the cover of this book, based on a series design by Edie Freedman. The cover image is a 19th-century engraving from the Dover Pictorial Archive. Emma Colby produced the cover layout with QuarkXPress 4.1 using Adobe's ITC Garamond font.

David Futato designed the interior layout. Neil Walls converted the files from Microsoft Word to FrameMaker 5.5.6 using tools created by Mike Sierra. The text font is Linotype Birka; the heading font is Adobe Myriad Condensed; and the code font is LucasFont's TheSans Mono Condensed. The illustrations that appear in the book were produced by Robert Romano and Jessamyn Read using Macromedia FreeHand 9 and Adobe Photoshop 6. The tip and warning icons were drawn by Christopher Bing. This colophon was written by Linley Dolby.