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.
Chapter 1 Pivot Tables
Using a Pivot Table
Chapter 2 Sets
The Students Example
Implementing Set Difference
Comparing Two Sets for Equality
Implementing Partial Intersection
Implementing Full Intersection
Summarizing Classes of Sets
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
Chapter 3 Data Structures
Types of Data Structures
Reporting Region Boundaries
Limiting Region Size
Ranking Regions by Size
Working with Sequences
Working with Runs
Cumulative Aggregates in Lists
Implementing a Stack
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
Chapter 4 Hierarchies in SQL
Types of Hierarchies
Creating a Permission Hierarchy
Changing Individual Permissions
Adding New Individual Permissions
Centralizing Authorization Logic
Implementing General Hierarchies
Traversing Hierarchies Recursively
Manipulating Hierarchies Recursively
Preparing Multilevel Operations
Aggregating Hierarchies Revised
Chapter 5 Temporal Data
The Schedules Example
Enforcing Granularity Rules
Storing Out-of-Range Temporal Values
Deriving the First and Last Dates of the Month
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
Chapter 6 Audit Logging
The Warehouse Example
Reporting Log Events
Generating Current Snapshots
Generating Time-Restricted Snapshots
Undoing Table Changes
Minimizing Audit-Log Space Consumption
Online Account Balancing
Partitioning Large Log Tables
Chapter 7 Importing and Transforming Data
Considerations When Importing Data
Importing External Data
Importing Data into a Live System
Importing with a Single Procedure
Hiding the Import Procedure
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
Chapter 8 Statistics in SQL
The Light-Bulb Factory Example
Calculating a Mean
Calculating a Mode
Calculating a Median
Calculating Standard Deviation, Variance, and Standard Error
Ales Spetic, an independent consultant specializing in the design and implementation of financial applications, has been programming in SQL for the past six years. He has a MBA California State University, Hayward.
Jonathan Gennick is an O'Reilly & Associates editor specializing in database and programming titles. Prior to joining O'Reilly, Jonathan amassed some 17 years of programming and database management experience. During the latter part of his career he specialized in relational database programming, and more recently in database management. Jonathan got his start with relational database systems in 1990, first working with Ingres, and later with Digital's Rdb software. During that period he developed a fondness for SQL, and for the challenge of applying SQL creatively in ways that leveraged it's set-oriented capabilities. In 1994 Jonathan made the leap to Oracle, and is now often found writing about it. Recent projects include Oracle SQLLoader (O'Reilly & Associates, 2001); Oracle SQLPlus Pocket Reference (O'Reilly & Associates, 2000); Oracle SQL*Plus: The Definitive Guide (O'Reilly & Associates, 1999); More recently, Jonathan has made forays into other database products, coauthoring Transact-SQL Cookbook (O'Reilly & Associates, 2002) and editing Practical PostgreSQL (O'Reilly & Associates, 2002). Jonathan is certified as an Oracle DBA and is a member of MENSA and the Oracle Technology Network. He holds a Bachelor of Arts degree in Information and Computer Science, with a Business Administration minor, from Andrews University in Berrien Springs, Michigan. Jonathan currently resides in Munising, Michigan with his wife Donna and their two children: Jenny and Jeff. Jonathan may be contacted by email at email@example.com, and you can learn more about him personally by visiting his website at http://gennick.com.
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.
I have owned this book for about 2 years and it has paid for itself over and over in saved time and resources. Using the set based techniques described in this book I have been able to release many expensive cursors from production environments by processing data with time bound sets. Anyone who uses TSQL daily should own and re-read this book quarterly. I do.
Bottom Line Yes, I would recommend this to a friend
About Me Designer, Developer, Educator, Maker, Sys Admin
Comments about oreilly Transact-SQL Cookbook:
I dip into this whenever I am running out of ideas on how to approach problems. Every time I learn new stuff and as I grow, the book gets more useful. Pleased I bought it. Glad the guys took the time and effort to write it. If it was twice as thick it would be no more informative, helpful or thought-provoking.
Bottom Line Yes, I would recommend this to a friend