 |
Transact-SQL Cookbook
Customer Reviews: 1 Average Customer Rating:  Write a Review and tell the world about this title! People who purchase this book frequently purchase: Books on similar topics, in best-seller order:Books from the same publisher, in best-seller order:
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 Alex 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
Preface
1. Pivot Tables
Using a Pivot Table
2. Sets
Introduction
The Students Example
Implementing Set Difference
Comparing Two Sets for Equality
Implementing Partial Intersection
Implementing Full Intersection
Classifying Subsets
Summarizing Classes of Sets
Aggregating Aggregates
Summarizing Aggregated Classes
Including Nonaggregated Columns
Finding the Top N Values in a Set
Reporting the Size of a Set's Complement
Finding the Complement of a Set
Finding the Complement of a Missing Set
Finding Complements of Sets with Different Universes
Comparing a Set with Its Universe
Dynamic Classification System
3. Data Structures
Types of Data Structures
Working Example
Finding Regions
Reporting Region Boundaries
Limiting Region Size
Ranking Regions by Size
Working with Sequences
Working with Runs
Cumulative Aggregates in Lists
Implementing a Stack
Implementing Queues
Implementing Priority Queues
Comparing Two Rows in an Array
Printing Matrices and Arrays
Transposing a Matrix
Calculating a Matrix Trace
Comparing Two Matrices for Size
Adding and Subtracting Matrices
Multiplying Matrices
4. Hierarchies in SQL
Types of Hierachies
Creating a Permission Hierarchy
Changing Individual Permissions
Adding New Individual Permissions
Centralizing Authorization Logic
Implementing General Hierarchies
Traversing Hierarchies Recursively
Manipulating Hierarchies Recursively
Aggregating Hierarchies
Preparing Multilevel Operations
Aggregating Hierarchies Revised
5. Temporal Data
Introduction
The Schedules Example
Enforcing Granularity Rules
Storing Out-of-Range Temporal Values
Deriving the First and Last Dates of the Month
Printing Calendars
Calculating Durations
Reporting Durations
Querying Periods
Querying Periods and Respecting Granularity
Finding Available Periods
Finding Common Available Periods
Excluding Recurrent Events
Excluding Nonrecurring Events
Finding Continuous Periods
Using Calendar Information with Periods
Using Calendar Information with Durations
6. Audit Logging
Audit Logs
The Warehouse Example
Row-Level Logging
Reporting Log Events
Generating Current Snapshots
Generating Time-Restricted Snapshots
Undoing Table Changes
Minimizing Audit-Log Space Consumption
Online Account Balancing
Activity-Level Logging
Partitioning Large Log Tables
Server Push
7. Importing and Transforming Data
Considerations When Importing Data
Working Examples
Importing External Data
Importing Data into a Live System
Importing with a Single Procedure
Hiding the Import Procedure
Folding Tables
Pivoting Tables
Joining Arrays with Tables
Joining Arrays with Master Tables
Joining Arrays with Multiple Master Records
Extracting Master Records from Tables
Generating Master Records Online
Working with Duplicates
8. Statistics in SQL
Statistical Concepts
The Light-Bulb Factory Example
Calculating a Mean
Calculating a Mode
Calculating a Median
Calculating Standard Deviation, Variance, and Standard Error
Building Confidence Intervals
Calculating Correlation
Exploring Patterns with Autocorrelation
Using a Simple Moving Average
Extending Moving Averages
Appendix: The T-Distribution Table
Index
Customer Reviews
Customer Reviews: 1 Average Customer Rating: May 20, 2002
one of the best
This book contains concise explanations of many important topics. Anyone who would like to learn about the concepts should read it since it covers almost all levels of understanding from the overall picture to the nitty-gritty details of Transact-SQL. Examples are clear and well structured.
Not for beginners and probably not for hard-core gurus. Overall, this book has everything you need to master TSQL programming on your own.
|
|
