Contents

Acknowledgments

Foreword

Chapter 1: Introducing Transact-SQL and Data Management Systems

Transact-Structured Query Language

T-SQL: Programming Language or Query Language?

What's New in SQL Server 2005

Database Management System (DBMS)

SQL Server as a Relational Database Management System

Tables

Relationships

RDBMS and Data Integrity

SQL Server and Other Products

Microsoft SQL Server

Oracle

IBM DB2

Informix

Sybase SQLAnywhere

Microsoft Access

MySQL

Summary

Chapter 2: SQL Server Fundamentals

Who Uses SQL Server?

SQL Server Editions and Features

SQL Server 2000

SQL Server 2005

Relational Database Engine

Semantics

Changing Terminology

Relationships

Primary Keys

Foreign Keys

Normalization Rules

Transforming Information into Data

Applying Normalization Rules

Thinking Ahead

Multiple Associations

Multi-valued Columns

To Normalize or to De-normalize?

Question Authority

Client/Server Processes

The Mechanics of Query Processing

The Adventure Works Cycles Database

Summary

Chapter 3: Tools for Accessing SQL Server

Tools for SQL Server 2000

Enterprise Manager

Query Analyzer

Using Books Online

OSQL Command-line Utility

Tools for SQL Server 2005

SQL Computer Manager

SQL Server Management Studio

Business Intelligence Development Studio

SQLCMD Command-line Utility

Summary

Exercises

Exercise 1

Exercise 2

Exercise 3

Exercise 4

Chapter 4: Introducing Transact-SQL Language

The Nature of SQL

Where to Begin

Data Manipulation Language (DML)

Queries Have Layers

Set-Based Operations

Row-Based Operations

Query Syntax Basics

Naming Conventions

Commenting Script

Using Templates

Generating Script

Managing Script

Version Control

Data Definition Language

Creating a Table

Creating a View

Creating a Stored Procedure

Creating a Trigger

Creating a User-Defined Function

Scripting Practices

Data Control Language

Summary

Exercises

Exercise 1

Exercise 2

Chapter 5: Data Retrieval

Storage and Retrieval

The SELECT Statement

Choosing Columns

SQL Server 2005 Schemas

Column Aliasing

Calculated and Derived Columns

Filtering Rows

The WHERE Clause

Using Parentheses

Sorting Results

Top Values

Summary

Exercises

Exercise 1

Exercise 2

Exercise 3

Exercise 4

Chapter 6: SQL Functions

The Anatomy of a Function

I'd Like to Have an Argument

Deterministic Functions

Using Variables with Functions

Using Functions in Queries

Nested Functions

Aggregate Functions

AVG()

COUNT()

MIN() and MAX()

SUM()

Configuration Variables

@@ERROR

@@SERVICENAME

@@TOTAL_ERRORS

@@TOTAL_READ

@@VERSION

Conversion Functions

CAST()

Cursor Functions and Variables

Date Functions

String Manipulation Functions

Image/Text Functions

Mathematical Functions

Metadata Functions

Ranking Functions

Security Functions

System Functions and Variables

System Statistical Functions and Variables

Summary

Exercises

Exercise 1

Exercise 2

Exercise 3

Exercise 4

Exercise 5

Chapter 7: Aggregation and Grouping

Using Aggregate Functions

The COUNT() Function

The SUM() Function

The AVG() Function

Understanding Statistical Functions

The VAR() Function

The VARP() Function

The STDEV() Function

The STDDEVP() Function

User-Defined Aggregate Functions

Grouping Data

GROUP BY

HAVING

Totals and Subtotals

Subgrouping

WITH ROLLUP

WITH CUBE

The GROUPING() Function

COMPUTE and COMPUTE BY

Summary

Exercises

Exercise 1

Exercise 2

Exercise 3

Chapter 8: Multi-Table Queries

Understanding Subqueries and Joins

Joining Tables in the WHERE Clause

Joining Tables in the FROM Clause

Inner Joins

Outer Joins

Multicolumn Joins

Non-equijoins

Special-Purpose Join Operations

Union Queries

Summary

Exercises

Exercise 1

Exercise 2

Exercise 3

Chapter 9: Data Transactions

Introducing Transactions

Transaction Types

The ACID Test

The Transaction Log

Logged Operations

Let's Do CRUD with Data

Adding Records

Modifying Records

Removing Records

Explicit Transactions

Summary

Exercises

Exercise 1

Exercise 2

Exercise 3

Chapter 10: Advanced Queries and Scripting

Subqueries

Scalar Expressions

Alternate Join Operations

Correlated Subqueries

Business Cases for Subqueries

Cursors

Rowset Versus Cursor Operations

Creating and Navigating a Cursor

Summary

Exercises

Exercise 1

Exercise 2

Chapter 11: Full-Text Index Queries

Microsoft Search Service

Soundex Matching

The DIFFERENCE() Function

Managing and Populating Catalogs

SQL Server 2000

SQL Server 2005

Full-Text Query Expressions

Summary

Chapter 12: Creating and Managing Database Objects

Data Definition Language

Creating Objects

Altering Objects

Dropping Objects

Names

CREATE TABLE

UniqueIdentifier

Constraints

CREATE VIEW

Indexed Views

CREATE PROCEDURE

CREATE FUNCTION

Securing Database Objects

Managing Security Objects

Data Control Language

Summary

Exercises

Exercise 1

Exercise 2

Chapter 13: Transact-SQL Programming Objects

Views

Virtual Tables

Creating a View

Securing Data

Hiding Complexity

Modifying Data through Views

Stored Procedures

Stored Procedures as Parameterized Views

Using Parameters

Returning Values

Record Maintenance

Handling and Raising Errors

Processing Business Logic

Looping

User-Defined Functions

Scalar Functions

Inline Table-Valued Functions

Multi-Statement Table-Valued Functions

Transaction Management

Locking Options

Summary

Exercises

Exercise 1

Exercise 2

Exercise 3

Exercise 4

Chapter 14: Transact-SQL in Applications and Reporting

Application Programming Models

Selecting a Model

Desktop Database Applications

Client/Server Database Solutions

n-tier Component Solutions

Web Server Application

Multi-tier Web Service Solutions

Multi-system Integrated Solutions

SQL Server Reporting Services

Reporting Services Architecture

The Report Designer

Report Data Caching

Report Application Integration

Summary

Appendix A: Command Syntax Reference

Appendix B: System Variables and Functions Reference

Appendix C: System Stored Procedure Reference

Appendix D: Information Schema Views Reference

Appendix E: Answers to Exercises

Index