Transact-SQL Programming: Covers Microsoft SQL Server 6.5 /7.0 and Sybase Adaptive Server 11.5 [With CDROM] (Paperback)

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