Contents Listing

Foreword

Preface

I. The Basics: Programming in Transact-SQL

1. Introduction to Transact-SQL

SQL and the Introduction of Transact-SQL

The Relational Database Model

Normalization

Row Processing Versus Set Processing

The History of SQL Server

What Is Transact-SQL?

Summary

2. Database Analysis and Design

The Need for Adequate Analysis and Design

The Systems Life Cycle

Overview of Analysis

Overview of Design

Putting it Together with CASE Technology

Summary

3. SQL Primer

SQL Versus Transact-SQL

Data Definition Language

Data Manipulation Language

Summary

4. Transact-SQL Fundamentals

Server Character Sets

Server Sort Orders

Identifiers

Foundation Units

Executing Transact-SQL

The GO Statement

Transact-SQL Batches

Summary

5. Format and Style

Why Bother?

What Is a Good Coding Style?

Case of Keywords and User-Defined Names

Naming Conventions and Abbreviations

Standard Error Handling

Style Odds and Ends

Formatting DML and DDL Statements

Formatting Transaction Blocks and Procedural Blocks

Commenting Your Code

Summary

II. The Building Blocks: Transact-SQL Language Elements

6. Datatypes and Variables

Datatypes

User-Defined Datatypes

Processing Nulls

Variables

Parameters

Summary

7. Conditional Processing

BEGIN . . . END Constructs

IF Constructs

GOTO Branching and Error Handling

WAITFOR Construct

WHILE Loops

RETURN Statement

Summary

8. Row Processing with Cursors

Cursor Fundamentals

Declaring a Cursor

Opening a Cursor

Asynchronous Keysets

Fetching Data

Closing and Deallocating a Cursor

Working with Cursors

Performance Issues for Cursors

Summary

9. Error Handling

Code Processing and Error Handling: Basic Concepts

Displaying an Error Message with PRINT

Raising an Error with RAISERROR

Finding Error Conditions with Global Variables

Handling Errors with GOTO

Creating Specialized Error Messages

Finding Error Messages in Operating System Files

Summary

10. Temporary Objects

Creating and Manipulating TemporaryObjects

The tempdb Database

Classes of Temporary Objects

Query Optimization with Temporary Tables

Management of the tempdb Database

Summary

11

and Locking 297

About Transactions

Locking

Transactions and the Database

Summary

III. Functions and Extensions

12. Functions

Aggregate Functions

Mathematical Functions

String Functions

Date Functions

Text and Image Functions

Type Conversion Functions

System Functions

Summary

13. CASE Expressions and Transact-SQL Extensions

CASE Expressions

COMPUTE

CUBE and ROLLUP

Summary

IV. Programming Transact-SQL Objects

14. Stored Procedures and Modular Design

About Stored Procedures

CREATE PROCEDURE

Types of Stored Procedures

Advantages of Stored Procedures

Stored Procedures Versus Other Objects

Executing Stored Procedures

Remote Procedure Calls (RPCs)

Stored Procedure Design

Conditional Data Retrieval

Data Formatting

Data Security and Encryption

Performance Issues

Summary

15. Triggers and Constraints

Triggers

Interaction of Triggers and Constraints

Summary

16. Views

About Views

Managing Views

Querying Views

View Dependencies, Permissions, and Data Manipulations

Limitations of Views

View Overhead

Summary

17. System and Extended Stored Procedures

System Stored Procedures

Common System Stored Procedures

Extended Stored Procedures

Summary

V. Performance Tuning and Optimization

18. Transact-SQL Code Design

Coding for Reuse

The Black Box Program

Categorization Schemas

RETURN

RAISERROR

Temporary Data Storage

Using Parameters Effectively

Help

Summary

19. Code Maintenance in SQL Server

Using SQL Server System Tables

Interdependence of Server-Side Objects

Source Code Management

Code Maintenance

Related Topics

Summary

20. Transact-SQL Optimization and Tuning

About Optimization

Efficient Physical Database Design

Indexing Strategies

SQL Server Query Optimizer

SHOWPLAN Output

Optimizer Hints

Query Analysis

Optimization Tips and Techniques

Summary

21. Debugging Transact-SQL Programs

Testing Versus Debugging

Successful Debugging

Specific Debugging Techniques

The SET Command

Debugging Utilities

Summary

VI. Appendixes

A. System Tables

B. What's New for Transact-SQL in Microsoft SQL Server 7.0

C. The BCP Utility