The Guru's Guide to Transact-SQL

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.

Table of contents

Foreword

Preface

About the Sample Databases

Results Abridged

On Formality

Acknowledgments

Chapter 1: Introductory Transact-SQL

Choosing a SQL Editor

Creating a Database

Creating Tables

Inserting Data

Updating Data

Deleting Data

Querying Data

Filtering Data

Grouping Data

Ordering Data

Column Aliases

Table Aliases

Managing Transactions

Summary

Chapter 2: Transact-SQL Data Type Nuances

Dates

Strings

Numerics

BLOBs

Bits

Uniqueidentifier

Cursor Variables

Timestamps

Summary

Chapter 3: Missing Values

NULL and Expressions

NULL and Functions

NULL and ANSI SQL

NULL and Stored Procedures

NULL If You Must

Chapter 4: DDL Insights

Create Table

Dropping Objects

Create Index

Temporary Oblects

Object Naming and Dependencies

Summary

Chapter 5: DML Insights

Insert

Update

Delete

Detecting DML Errors

Summary

Chapter 6: The Mighty SELECT Statement

Simple SELECTs

Computational and Derived Fields

Select Top

Derived Tables

Joins

Predicates

Subqueries

Aggregate Functions

Group By and Having

Union

Order By

Summary

Chapter 7: Views

Restrictions

ANSI SQL Schema VIEWs

Getting a VIEW's Source Code

Updatable VIEWs

With Check Option

Derived Tables

Dynamic Views

Partitioning Data Using Views

Summary

Chapter 8: Statistical Functions

The Case for CASE

Efficiency Concerns

Variance and Standard Deviation

Medians

Clipping

Returning the Top n Rows

Rankings

Modes

Histograms

Cumulative and Sliding Aggregates

Extremes

Summary

Chapter 9: Runs and Sequences

Sequences

Runs

Intervals

Summary

Chapter 10: Arrays

Arrays as Big Strings

Arrays as Tables

Summary

Chapter 11: Sets

Unions

Differences

Intersections

Subsets

Summary

Chapter 12: Hierarchies

Simple Hierarchies

Multilevel Hierarchies

Indented Lists

Summary

Chapter 13: Cursors

On Cursors and ISAMs

Types of Cursors

Appropriate Cursor Use

T-SQL Cursor Syntax

Cofiguring Cursors

Updating Cursors

Cursor Variables

Cursor Stored Procedures

Optimizing Cursor Performance

Summary

Chapter 14: Transactions

Transactions Defined

How SQL Server Transactions Work

Types of Transactions

Avoiding Transactions Altogether

Automatic Transaction Management

Transaction Isolation Levels

Transaction Commands and Syntax

Debugging Transactions

Optimizing Transactional Code

Summary

Chapter 15: Stored Procedures and Triggers

Stored Procedure Advantages

Internals

Creating Stored Procedures

Executing Stored Procedures

Environmental Concerns

Parameters

Important Automatic Variables

Flow Control Language

Errors

Nesting

Recursion

Autostart Procedures

Encryption

Triggers

Debugging Procedures

Summary

Chapter 16: Transact-SQL Performance Tuning

General Performance Guidelines

Database Design Performance Tips

Index Performance Tips

Select Performance Tips

Insert Performance Tips

Bulk Copy Performance Tips

Delete and Update Performance Tips

Cursor Performance Tips

Stored Procedure Performance Tips

SARGs

Denormalization

The Query Optimizer

The Index Tuning Wizard

Profiler

Perfmon

Summary

Chapter 17: Administrative Transact-SQL

GUI Administration

System Stored Procedures

Administrative Transact-SQL Commands

Administrative System Functions

Administrative Automatic Variables

Where's the Beef?

Summary

Chapter 18: Full-Text Search

Full-Text Predicates

Rowset Functions

Summary

Chapter 19: OLE Automation

sp_exporttable

sp_importtable

sp_getSQLregistry

Summary

Chapter 20: Undocumented T-SQL

Defining Undocumented

Undocumented DBCC Commands

Undocumented Functions and Variables

Undocumented Trace Flags

Undocumented Procedures

Summary

Chapter 21: Potpourri

Obscure Functions

Data Scrubbing

Iteration Tables

Summary

Appendix: Suggested Resources

Books

Internet Resources

Index