Cover image for The guru's guide to Transact-SQL
The guru's guide to Transact-SQL
Henderson, Ken.
Personal Author:
Publication Information:
Reading, Mass. : Addison-Wesley, [2000]

Physical Description:
xx, 551 pages : illustrations ; 24 cm + 1 computer optical disc (4 3/4 in.)
Format :


Call Number
Material Type
Home Location
Item Holds
QA76.73.S67 H47 2000 TEXT Book and Software Set Central Closed Stacks

On Order



Since its introduction over a decade ago, the Microsoft SQL Server query language, Transact-SQL, has become increasingly popular and more powerful. The current version sports such advanced features as OLE Automation support, cross-platform querying facilities, and full-text search management.

This book is the consummate guide to Microsoft Transact-SQL. From data type nuances to complex statistical computations to the bevy of undocumented features in the language, The Guru's Guide to Transact-SQL imparts the knowledge you need to become a virtuoso of the language as quickly as possible.

In this book, you will find the information, explanations, and advice you need to master Transact-SQL and develop the best possible Transact-SQL code. Some 600 code examples not only illustrate important concepts and best practices, but also provide working Transact-SQL code that can be incorporated into your own real-world DBMS applications.

Your journey begins with an introduction explaining language fundamentals such as database and table creation, inserting and updating data, queries, joins, data presentation, and managing transactions. Moving on to more advanced topics, the journey continues with in-depth coverage of:

Transact-SQL performance tuning using tools such as Query Analyzer and Performance Monitor Nuances of the various T-SQL data types Complex statistical calculations such as medians, modes, and sliding aggregates Run, sequence, and series identification and interrogation Advanced Data Definition Language (DDL) and Data Management Language (DML) techniques Stored procedure and trigger best practices and coding methods Transaction management Optimal cursor use and caveats to look out for Full-text search Hierarchies and arrays Administrative Transact-SQL OLE Automation More than 100 undocumented commands and language features, including numerous unpublished DBCC command verbs, trace flags, stored procedures, and functions Comprehensive, written in understandable terms, and full of practical information and examples, The Guru's Guide to Transact-SQL is an indispensable reference for anyone working with this database development language. The accompanying CD-ROM includes the complete set of code examples found in the book as well as a SQL programming environment that will speed the development of your own top-notch Transact-SQL code.

Author Notes

Ken Henderson, a nationally recognized consultant and leading DBMS practitioner, consults on high-end client/server projects for such customers as the U.S. Air Force, the U.S. Navy, H&R Block, Travelers Insurance, J.P. Morgan, the CIA, Owens-Corning, and CNA Insurance. He is the author of five previous books on client/server and DBMS development, a frequent magazine contributor to such publications as Software Development Magazine and DBMS Magazine, and a speaker at technical conferences.




This is a coder's book. It's intended to help developers build applications that make use of Transact-SQL. It's not about database administration or design. It's not about end-user or GUI application development. It's not even about server or database performance tuning. It's about developing the best Transact-SQL code possible, regardless of the application. When I began writing this book, I had these design goals in mind: Be very generous with code samples--don't just tell readers how to do something, show them. Include complete code samples within the chapter texts so that the book can be read through without requiring a computer or CD-ROM. Use modern coding techniques, with specific emphases on ANSI compliance and current version features and enhancements. Construct chapters so that they're self-contained--so that they rely as little as possible on objects created in other chapters. Provide real-world code samples that have intrinsic value apart from the book. Avoid rehashing what's already covered extensively in the SQL Server Books Online. Highlight aspects of Transact-SQL that differentiate it from other SQL dialects; don't just write another ANSI SQL book. Avoid excessive screenshots and other types of filler mechanisms often seen in computer books. Proceed from the simple to the complex within each chapter and throughout the book. Provide an easygoing, relaxed commentary with a de-emphasis on formality. Be the reader's indulgent, amiable tutor. Attempt to communicate in writing the way that people speak. You'll have to judge for yourself whether these goals have been met, but my hope is that, regardless of the degree of success, the effort will at least be evident. About the Sample Databases This book uses SQL Server's Northwind and pubs sample databases extensively. You'll nearly always be able to determine which database a particular example uses from the surrounding commentary or from the code itself. The pubs database is used more often than Northwind, so, when it's not otherwise specified or when in doubt, use pubs. Usually, modifications to these databases are made within transactions so that they can be reversed; however, for safety's sake, you should probably drop and recreate them after each chapter in which they're modified. The scripts to rebuild them (instnwnd.sql and instpubs.sql) can be found in the \Install subdirectory under the root SQL Server folder. Results Abridged If I have a pet peeve about computer books, it's the shameless use of space-filling devices to lengthen them--the dirty little secret of the computer publishing industry. Many technical books these days overflow with gratuitous helpings of screenshots, charts, diagrams, outlines, sidebars, icons, line art, etc. There are people who assign more value to a book that's heavy, and many authors and publishers have been all too happy to accommodate them. They seem to take the old saying that "a picture is worth a thousand words" literally--in some cases turning out books that are little more than picture books. I think there's a point at which comprehensiveness gives way to corpulence, a time when exhaustiveness becomes exhausting. In this book, I've tried to strike a balance between being thorough and being space-efficient. To that end, I've often truncated or clipped query result sets, especially those too wide to fit on a page and those of excessive length (I always point this out). On occasion I also list them using reduced font sizes. I don't include screenshots unless doing so benefits the discussion at hand materially (only one chapter contains any screenshots). This is in keeping with my design goal of being complete without being overwrought. Nearly 600 SQL scripts are used in this book, and they are all included in the chapters that reference them. Hopefully none of the abridgements will detract from the book's overall usefulness Excerpted from The Guru's Guide to Transact-SQL by Kenneth W. Henderson All rights reserved by the original copyright owners. Excerpts are provided for display purposes only and may not be reproduced, reprinted or distributed without the written permission of the publisher.

Table of Contents

Forewordp. xvi
Prefacep. xvii
About the Sample Databasesp. xviii
Results Abridgedp. xviii
On Formalityp. xviii
Acknowledgmentsp. xix
Chapter 1 Introductory Transact-SQLp. 1
Choosing a SQL Editorp. 2
Creating a Databasep. 3
Creating Tablesp. 3
Inserting Datap. 5
Updating Datap. 6
Deleting Datap. 7
Querying Datap. 7
Filtering Datap. 11
Grouping Datap. 18
Ordering Datap. 19
Column Aliasesp. 20
Table Aliasesp. 21
Managing Transactionsp. 21
Summaryp. 22
Chapter 2 Transact-SQL Data Type Nuancesp. 23
Datesp. 23
Stringsp. 34
Numericsp. 54
BLOBsp. 59
Bitsp. 66
Uniqueidentifierp. 67
Cursor Variablesp. 68
Timestampsp. 73
Summaryp. 75
Chapter 3 Missing Valuesp. 77
NULL and Expressionsp. 78
NULL and Functionsp. 78
NULL and ANSI SQLp. 79
NULL and Stored Proceduresp. 81
NULL If You Mustp. 83
Chapter 4 DDL Insightsp. 85
Create Tablep. 86
Dropping Objectsp. 89
Create Indexp. 90
Temporary Objectsp. 91
Object Naming and Dependenciesp. 93
Summaryp. 95
Chapter 5 DML Insightsp. 97
Insertp. 97
Updatep. 107
Deletep. 114
Detecting DML Errorsp. 118
Summaryp. 118
Chapter 6 The Mighty Select Statementp. 119
Simple Selectsp. 119
Computational and Derived Fieldsp. 120
Select Topp. 121
Derived Tablesp. 122
Joinsp. 126
Predicatesp. 128
Subqueriesp. 140
Aggregate Functionsp. 147
Group By and Havingp. 150
Unionp. 157
Order Byp. 159
Summaryp. 161
Chapter 7 Viewsp. 163
Restrictionsp. 163
ANSI SQL Schema VIEWsp. 165
Getting a VIEW's Source Codep. 166
Updatable VIEWsp. 166
With Check Optionp. 167
Derived Tablesp. 167
Dynamic VIEWsp. 168
Partitioning Data Using Viewsp. 170
Summaryp. 172
Chapter 8 Statistical Functionsp. 173
The Case for CASEp. 174
Efficiency Concernsp. 176
Variance and Standard Deviationp. 176
Mediansp. 177
Clippingp. 185
Returning the Top n Rowsp. 186
Rankingsp. 190
Modesp. 193
Histogramsp. 193
Cumulative and Sliding Aggregatesp. 195
Extremesp. 197
Summaryp. 199
Chapter 9 Runs and Sequencesp. 201
Sequencesp. 201
Runsp. 207
Intervalsp. 210
Summaryp. 212
Chapter 10 Arraysp. 213
Arrays as Big Stringsp. 213
Arrays as Tablesp. 220
Summaryp. 228
Chapter 11 Setsp. 229
Unionsp. 230
Differencesp. 231
Intersectionsp. 234
Subsetsp. 235
Summaryp. 239
Chapter 12 Hierarchiesp. 241
Simple Hierarchiesp. 241
Multilevel Hierarchiesp. 242
Indented Listsp. 249
Summaryp. 250
Chapter 13 Cursorsp. 251
On Cursors and ISAMsp. 251
Types of Cursorsp. 253
Appropriate Cursor Usep. 258
T-SQL Cursor Syntaxp. 263
Configuring Cursorsp. 272
Updating Cursorsp. 276
Cursor Variablesp. 277
Cursor Stored Proceduresp. 279
Optimizing Cursor Performancep. 279
Summaryp. 281
Chapter 14 Transactionsp. 283
Transactions Definedp. 283
How SQL Server Transactions Workp. 284
Types of Transactionsp. 285
Avoiding Transactions Altogetherp. 287
Automatic Transaction Managementp. 288
Transaction Isolation Levelsp. 289
Transaction Commands and Syntaxp. 293
Debugging Transactionsp. 299
Optimizing Transactional Codep. 300
Summaryp. 301
Chapter 15 Stored Procedures and Triggersp. 303
Stored Procedure Advantagesp. 304
Internalsp. 305
Creating Stored Proceduresp. 307
Executing Stored Proceduresp. 316
Environmental Concernsp. 317
Parametersp. 320
Important Automatic Variablesp. 323
Flow Control Languagep. 324
Errorsp. 325
Nestingp. 328
Recursionp. 328
Autostart Proceduresp. 330
Encryptionp. 330
Triggersp. 330
Debugging Proceduresp. 334
Summaryp. 335
Chapter 16 Transact-SQL Performance Tuningp. 337
General Performance Guidelinesp. 337
Database Design Performance Tipsp. 338
Index Performance Tipsp. 340
SELECT Performance Tipsp. 342
INSERT Performance Tipsp. 344
Bulk Copy Performance Tipsp. 344
DELETE and UPDATE Performance Tipsp. 346
Cursor Performance Tipsp. 346
Stored Procedure Performance Tipsp. 347
SARGsp. 351
Denormalizationp. 368
The Query Optimizerp. 384
The Index Tuning Wizardp. 394
Profilerp. 396
Perfmonp. 397
Summaryp. 399
Chapter 17 Administrative Transact-SQLp. 401
GUI Administrationp. 401
System Stored Proceduresp. 402
Administrative Transact-SQL Commandsp. 402
Administrative System Functionsp. 402
Administrative Automatic Variablesp. 402
Where's the Beef?p. 404
Summaryp. 461
Chapter 18 Full-Text Searchp. 463
Full-Text Predicatesp. 468
Rowset Functionsp. 471
Summaryp. 474
Chapter 19 OLE Automationp. 475
@sp_exporttablep. 476
@sp_importtablep. 480
@sp_getSQLregistryp. 484
Summaryp. 486
Chapter 20 Undocumented T-SQLp. 487
Defining Undocumentedp. 487
Undocumented DBCC Commandsp. 488
Undocumented Functions and Variablesp. 499
Undocumented Trace Flagsp. 502
Undocumented Proceduresp. 503
Summaryp. 509
Chapter 21 Potpourrip. 511
Obscure Functionsp. 511
Data Scrubbingp. 521
Iteration Tablesp. 525
Summaryp. 526
Appendix Suggested Resourcesp. 527
Booksp. 527
Internet Resourcesp. 528
Indexp. 529