Transact-SQL Cookbook


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 ...

See more details below
$38.37 price
(Save 4%)$39.99 List Price
Other sellers (Paperback)
  • All (15) from $2.77   
  • New (8) from $22.81   
  • Used (7) from $2.74   
Transact-SQL Cookbook

Available on NOOK devices and apps  
  • NOOK Devices
  • Samsung Galaxy Tab 4 NOOK 7.0
  • Samsung Galaxy Tab 4 NOOK 10.1
  • NOOK HD Tablet
  • NOOK HD+ Tablet
  • NOOK eReaders
  • NOOK Color
  • NOOK Tablet
  • Tablet/Phone
  • NOOK for Windows 8 Tablet
  • NOOK for iOS
  • NOOK for Android
  • NOOK Kids for iPad
  • PC/Mac
  • NOOK for Windows 8
  • NOOK for PC
  • NOOK for Mac
  • NOOK for Web

Want a NOOK? Explore Now

NOOK Book (eBook)
$15.49 price
(Save 44%)$27.99 List Price


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.

This guide contains a wealth of solutions to problems that SQl Server programmers face. The recipes in the book range from those that show how to perform simple tasks to ones that are more complicated.

Read More Show Less

Product Details

  • ISBN-13: 9781565927568
  • Publisher: O'Reilly Media, Incorporated
  • Publication date: 3/21/2002
  • Edition number: 1
  • Pages: 304
  • Sales rank: 1,399,155
  • Product dimensions: 7.10 (w) x 9.26 (h) x 0.68 (d)

Meet the Author

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, and you can learn more about him personally by visiting his website at

Read More Show Less

Table of Contents

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;
Chapter 1: Pivot Tables;
1.1 Using a Pivot Table;
Chapter 2: Sets;
2.1 Introduction;
2.2 The Students Example;
2.3 Implementing Set Difference;
2.4 Comparing Two Sets for Equality;
2.5 Implementing Partial Intersection;
2.6 Implementing Full Intersection;
2.7 Classifying Subsets;
2.8 Summarizing Classes of Sets;
2.9 Aggregating Aggregates;
2.10 Summarizing Aggregated Classes;
2.11 Including Nonaggregated Columns;
2.12 Finding the Top N Values in a Set;
2.13 Reporting the Size of a Set's Complement;
2.14 Finding the Complement of a Set;
2.15 Finding the Complement of a Missing Set;
2.16 Finding Complements of Sets with Different Universes;
2.17 Comparing a Set with Its Universe;
2.18 Dynamic Classification System;
Chapter 3: Data Structures;
3.1 Types of Data Structures;
3.2 Working Example;
3.3 Finding Regions;
3.4 Reporting Region Boundaries;
3.5 Limiting Region Size;
3.6 Ranking Regions by Size;
3.7 Working with Sequences;
3.8 Working with Runs;
3.9 Cumulative Aggregates in Lists;
3.10 Implementing a Stack;
3.11 Implementing Queues;
3.12 Implementing Priority Queues;
3.13 Comparing Two Rows in an Array;
3.14 Printing Matrices and Arrays;
3.15 Transposing a Matrix;
3.16 Calculating a Matrix Trace;
3.17 Comparing Two Matrices for Size;
3.18 Adding and Subtracting Matrices;
3.19 Multiplying Matrices;
Chapter 4: Hierarchies in SQL;
4.1 Types of Hierarchies;
4.2 Creating a Permission Hierarchy;
4.3 Changing Individual Permissions;
4.4 Adding New Individual Permissions;
4.5 Centralizing Authorization Logic;
4.6 Implementing General Hierarchies;
4.7 Traversing Hierarchies Recursively;
4.8 Manipulating Hierarchies Recursively;
4.9 Aggregating Hierarchies;
4.10 Preparing Multilevel Operations;
4.11 Aggregating Hierarchies Revised;
Chapter 5: Temporal Data;
5.1 Introduction;
5.2 The Schedules Example;
5.3 Enforcing Granularity Rules;
5.4 Storing Out-of-Range Temporal Values;
5.5 Deriving the First and Last Dates of the Month;
5.6 Printing Calendars;
5.7 Calculating Durations;
5.8 Reporting Durations;
5.9 Querying Periods;
5.10 Querying Periods and Respecting Granularity;
5.11 Finding Available Periods;
5.12 Finding Common Available Periods;
5.13 Excluding Recurrent Events;
5.14 Excluding Nonrecurring Events;
5.15 Finding Continuous Periods;
5.16 Using Calendar Information with Periods;
5.17 Using Calendar Information with Durations;
Chapter 6: Audit Logging;
6.1 Audit Logs;
6.2 The Warehouse Example;
6.3 Row-Level Logging;
6.4 Reporting Log Events;
6.5 Generating Current Snapshots;
6.6 Generating Time-Restricted Snapshots;
6.7 Undoing Table Changes;
6.8 Minimizing Audit-Log Space Consumption;
6.9 Online Account Balancing;
6.10 Activity-Level Logging;
6.11 Partitioning Large Log Tables;
6.12 Server Push;
Chapter 7: Importing and Transforming Data;
7.1 Considerations When Importing Data;
7.2 Working Examples;
7.3 Importing External Data;
7.4 Importing Data into a Live System;
7.5 Importing with a Single Procedure;
7.6 Hiding the Import Procedure;
7.7 Folding Tables;
7.8 Pivoting Tables;
7.9 Joining Arrays with Tables;
7.10 Joining Arrays with Master Tables;
7.11 Joining Arrays with Multiple Master Records;
7.12 Extracting Master Records from Tables;
7.13 Generating Master Records Online;
7.14 Working with Duplicates;
Chapter 8: Statistics in SQL;
8.1 Statistical Concepts;
8.2 The Light-Bulb Factory Example;
8.3 Calculating a Mean;
8.4 Calculating a Mode;
8.5 Calculating a Median;
8.6 Calculating Standard Deviation, Variance, and Standard Error;
8.7 Building Confidence Intervals;
8.8 Calculating Correlation;
8.9 Exploring Patterns with Autocorrelation;
8.10 Using a Simple Moving Average;
8.11 Extending Moving Averages;
Appendix A: The T-Distribution Table;

Read More Show Less

Customer Reviews

Be the first to write a review
( 0 )
Rating Distribution

5 Star


4 Star


3 Star


2 Star


1 Star


Your Rating:

Your Name: Create a Pen Name or

Barnes & Review Rules

Our reader reviews allow you to share your comments on titles you liked, or didn't, with others. By submitting an online review, you are representing to Barnes & that all information contained in your review is original and accurate in all respects, and that the submission of such content by you and the posting of such content by Barnes & does not and will not violate the rights of any third party. Please follow the rules below to help ensure that your review can be posted.

Reviews by Our Customers Under the Age of 13

We highly value and respect everyone's opinion concerning the titles we offer. However, we cannot allow persons under the age of 13 to have accounts at or to post customer reviews. Please see our Terms of Use for more details.

What to exclude from your review:

Please do not write about reviews, commentary, or information posted on the product page. If you see any errors in the information on the product page, please send us an email.

Reviews should not contain any of the following:

  • - HTML tags, profanity, obscenities, vulgarities, or comments that defame anyone
  • - Time-sensitive information such as tour dates, signings, lectures, etc.
  • - Single-word reviews. Other people will read your review to discover why you liked or didn't like the title. Be descriptive.
  • - Comments focusing on the author or that may ruin the ending for others
  • - Phone numbers, addresses, URLs
  • - Pricing and availability information or alternative ordering information
  • - Advertisements or commercial solicitation


  • - By submitting a review, you grant to Barnes & and its sublicensees the royalty-free, perpetual, irrevocable right and license to use the review in accordance with the Barnes & Terms of Use.
  • - Barnes & reserves the right not to post any review -- particularly those that do not follow the terms and conditions of these Rules. Barnes & also reserves the right to remove any review at any time without notice.
  • - See Terms of Use for other conditions and disclaimers.
Search for Products You'd Like to Recommend

Recommend other products that relate to your review. Just search for them below and share!

Create a Pen Name

Your Pen Name is your unique identity on It will appear on the reviews you write and other website activities. Your Pen Name cannot be edited, changed or deleted once submitted.

Your Pen Name can be any combination of alphanumeric characters (plus - and _), and must be at least two characters long.

Continue Anonymously

    If you find inappropriate content, please report it to Barnes & Noble
    Why is this product inappropriate?
    Comments (optional)