Transact-SQL Cookbook: Help for Database Programmers

by Ales Spetic, Jonathan Gennick

Overview

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.

Product Details

ISBN-13:	9780596552039
Publisher:	O'Reilly Media, Incorporated
Publication date:	03/19/2002
Sold by:	Barnes & Noble
Format:	eBook
Pages:	304
File size:	3 MB

About 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 SQL*Loader (O'Reilly & Associates, 2001); Oracle SQL*Plus 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 Universityin 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 [e-mail], and you can learn more about him personally by visiting his website at http://gennick.com.

Table of Contents

	Preface	xi
1.	Pivot Tables	1
	Using a Pivot Table	1
2.	Sets	5
	Introduction	5
	The Students Example	12
	Implementing Set Difference	13
	Comparing Two Sets for Equality	16
	Implementing Partial Intersection	18
	Implementing Full Intersection	21
	Classifying Subsets	22
	Summarizing Classes of Sets	24
	Aggregating Aggregates	26
	Summarizing Aggregated Classes	27
	Including Nonaggregated Columns	28
	Finding the Top N Values in a Set	30
	Reporting the Size of a Set's Complement	33
	Finding the Complement of a Set	34
	Finding the Complement of a Missing Set	36
	Finding Complements of Sets with Different Universes	38
	Comparing a Set with Its Universe	39
	Dynamic Classification System	40
3.	Data Structures	45
	Types of Data Structures	45
	Working Example	50
	Finding Regions	53
	Reporting Region Boundaries	54
	Limiting Region Size	55
	Ranking Regions by Size	57
	Working with Sequences	58
	Working with Runs	60
	Cumulative Aggregates in Lists	61
	Implementing a Stack	63
	Implementing Queues	65
	Implementing Priority Queues	66
	Comparing Two Rows in an Array	67
	Printing Matrices and Arrays	69
	Transposing a Matrix	70
	Calculating a Matrix Trace	71
	Comparing Two Matrices for Size	72
	Adding and Subtracting Matrices	73
	Multiplying Matrices	74
4.	Hierarchies in SQL	78
	Types of Hierachies	79
	Creating a Permission Hierarchy	84
	Changing Individual Permissions	88
	Adding New Individual Permissions	90
	Centralizing Authorization Logic	92
	Implementing General Hierarchies	93
	Traversing Hierarchies Recursively	96
	Manipulating Hierarchies Recursively	99
	Aggregating Hierarchies	100
	Preparing Multilevel Operations	103
	Aggregating Hierarchies Revised	106
5.	Temporal Data	110
	Introduction	110
	The Schedules Example	119
	Enforcing Granularity Rules	119
	Storing Out-of-Range Temporal Values	121
	Deriving the First and Last Dates of the Month	122
	Printing Calendars	124
	Calculating Durations	126
	Reporting Durations	128
	Querying Periods	129
	Querying Periods and Respecting Granularity	130
	Finding Available Periods	132
	Finding Common Available Periods	134
	Excluding Recurrent Events	135
	Excluding Nonrecurring Events	136
	Finding Continuous Periods	138
	Using Calendar Information with Periods	145
	Using Calendar Information with Durations	147
6.	Audit Logging	150
	Audit Logs	150
	The Warehouse Example	155
	Row-Level Logging	155
	Reporting Log Events	160
	Generating Current Snapshots	163
	Generating Time-Restricted Snapshots	165
	Undoing Table Changes	166
	Minimizing Audit-Log Space Consumption	168
	Online Account Balancing	173
	Activity-Level Logging	178
	Partitioning Large Log Tables	183
	Server Push	184
7.	Importing and Transforming Data	189
	Considerations When Importing Data	189
	Working Examples	196
	Importing External Data	198
	Importing Data into a Live System	200
	Importing with a Single Procedure	204
	Hiding the Import Procedure	205
	Folding Tables	208
	Pivoting Tables	211
	Joining Arrays with Tables	215
	Joining Arrays with Master Tables	217
	Joining Arrays with Multiple Master Records	219
	Extracting Master Records from Tables	220
	Generating Master Records Online	223
	Working with Duplicates	229
8.	Statistics in SQL	235
	Statistical Concepts	236
	The Light-Bulb Factory Example	249
	Calculating a Mean	252
	Calculating a Mode	253
	Calculating a Median	255
	Calculating Standard Deviation, Variance, and Standard Error	259
	Building Confidence Intervals	260
	Calculating Correlation	261
	Exploring Patterns with Autocorrelation	262
	Using a Simple Moving Average	265
	Extending Moving Averages	267
Appendix	The T-Distribution Table	271
	Index	273