MCSD：SQL SERVER 6.5 DATABASE DESIGN学习指南（英文原版）

Table of Contents

Introduction

Chapter 1 An Introdution SQL Server 6.5

SQL Server 6.5 Is new and Improved

SQL Enterprise Manager

Transact-SQL

SQL-DMO

Database Maintenance Plan Wizard

SQL Server Capacities

Tools and Utilities Included in SQL Server 6.5

ISQL/W

Working with Queries

Using Graphical Statistics I/O

MS Query

Starting MS Query

Working with an MS Query SQL Statement

SQL Enterprise Manager

Starting SQL Enterprise Manager

Accessing the Pubs Sample Database

Verifying the Pubs Database

Installing the Pubs Sample Database

SQL Server Books Online

The Downtown Delivery Service Project

Summary

Review Questions

Chapter 2 Data Modeling

The Relational Model

The Client/Server Model

File-Server Versus Distributed Client/Server

The Entity-Relationship Model

Entity-Relationship Diagramming

Entities

Entities in a Data Model

Naming Entities

Drawing Entities

Adding Attributes to a Data Model

Divide and Conquer

Uniqueness and Keys

Primary Keys

Column Constraints

Understanding NULL and NOT NULL

Disallowing Duplicates

Adding Defaults

Prohibiting Changes

Improving the ER Model for the Downtown Delivery Service

Relationships

One-to-One Relationships

One-to Many Relationships

Many-to-Many Relationships

Enforcing Data Integrity

Entity Integrity

Domain Integrity

Referential Integrity

Normalizing a Database Design

First Normal Form

Second Normal Form

Benefits of Normalization

Drawbacks of Normalization

Summary

Review Questions

Chapter 3 System Databases and Tables

Examining the System Databases

Identifying the Role of the Model Database

Identifying the Role of the msdb Database

Identifying the Types of Temporary Tables

Adjusting the Size of the Tempdb Database

Placing empdb in RAM

Discussing System Tables

System Catalog

Database Catalog

Querying System Tables

Accessing the System with System Stored Procedures

Catalog Stored Procedures

Extended Stored Procedures

Replication Stored Procedures

SQL Executive Stored Procedures

System Stored Procedures

Executing Stored Procedures

sp_help [objectname]

sp_helpdb[bdname]

sp_helpsql[topic]

sp_who

Estimating Space Requirements

Understanding SQL Server's Units of Storage

Data Pages

Data Rows

Allocation Units

Calculating Row and Table Size

Calculating the Number of Nonclustered Index Pages

Calculating the Size of the Inventory Table

Summary

Review Questions

Chapter 4 Data Definition

Managing Database Devices

Creating a Device

Creating the DownTown2 Device

Viewing Information about a Device

Using SQL Enterprise Manager to View Information about All Devices

Using sp_helpdevice to View Information about a Device

Expanding a Database Device

Using the Enterprise Manager to Expand a Database Device

Using Transact-SQL to Expand a Database Device

Expanding a Device with DISK RESIZE

Dropping a Device

Dropping a Device with SQL Enterprise Manager

Dropping a Device with sp_dropdevice Stored Procedure

Introducing SQL Server Databases

Exploring the Transaction Logs Role in SQL Server

Following the Execution Path of the Transaction Log

Managing SQL Server Databases

Creating a Database Using SQL Enterprise Manager

Creating a Database with the CREATE DATABASE Statement

Using CREATE DATABASE to Create a Database

Exploring the Available Database Options

Setting Database Options from within SQL Enterprise manager

Using sp_dboption to Set Database Options

Setting Off an ANSI Null default with sp_dboption

Modifying Databases and Transaction Logs

Expanding a Database

Using SQL Enterprise manager to Expand a Database

increasing the Size of a Database with ALTER DATABASE

Using ALTER DATABASE to Increase a Database's Size

Shrinking a Database

Using SQL Enterprise Manager to Shrink a Database

Shrinking a Database with DBCC SHRINKDB

Using DBCC SHRINKDB to Shrink the DowntownDB Database

Dropping a Database

Dropping a Database from SQL Enterprise Manager

Using DROP DATABASE to Drop a Database

Dropping a Database with DROP DATABASE

Understanding SQL Server 6.5 Datatypes

System Supplied Datatypes

User-Defined Datatypes

Managing Tables in SQL Server 6.5

Creating a Table with SQL Enterprise Manager

Using the CREATE TABLE Statement to Create a Table

Creating a Table with CREATE TABLE

Dropping a Table

Dropping a Table with SQL Enterprise Manager

Using DROP TABLE to Delete a Table

Dropping a Table with DROP TABLE

Discussing Data Integrity

Identifying the Types of Data Integrity

Enforcing Data Integrity with the IDENTITY Property

Identifying Guidelines Associated with the IDENTITY Property

Creating an Identity Column

Creating a Table with an Identity Column

Adding a Column with the IDENTITY Property to an Existing Table

Using Constraints to Enforce Data Integrity

Managing Constraints with the SQL Enterprise Manager

Managing Constraints with the CREATE TABLE and ALTERTABLE Statements

Creating a PRIMARY KEY Constraint

Creating a FOREIGN KEY Constraint

Creating a UNIQUE Constraint

Creating a CHECK Constraint

Summary

Review Questions

Chapter 5 Data Retrieval

A Refresher Course in SQL

Coding a SQL SELECT Statement

Using Character Strings and Quotes

Arithmetic Operators

Operator Precedence

Comparison Operators in SQL Statements

Keywords Used in a WHERE Clause

Using SQL Statements

Coding the SELECT Statement

Querying with Wildcards and the LIKE Clause

Sorting Records with the ORDER BY Clause

Using SQL's Built-In Functions to Format Output Data

Converting Datatypes with the CONVERT Functions

Performing Mathematical Operations with the Built-in Mathematical Functions

Performing Operations with String Functions

Retrieving Special Server or Database Information with System Functions

Using Text and Image Functions

Summary

Review Questions

Chapter 6 Advanced Data Retrieval Techniques

Returning Summary Values with Aggregate Functions

Calculating an Average

Counting Rows with the COUNT Aggregate Function

Counting Rows With Nulls

Counting All Rows with COUNT(*)

Finding the Maximum Value with MAX

Finding the Minimum Value with MIN

Adding the Value of a Column with SUM

Using the GROUP BY Clause

Grouping the Impact of the ALL Keyword on a GROUP BY Clause

Counting Groups of Data

AVG. and SUM with the GROUP BY Clause

Restricting Rows with the HAVING Clause

Coding a HAVING Clause

Including Multiple Conditions in a HAVING Clause

Using the COMPUTE and COMPUTE BY Clauses

Computing Totals with COMPUTE

Computing Multiple Values for the Same Column

Adding BY to a COMPUTE Clause

Using COMPUTE BY Multiple Times in the Same SELECT Statement

Joining Tables

ANSI-Standard versus Old-Style Joins

Creating a CROSS JOIN

Creating an INNER JOIN

Creating Outer Joins

Creating a LEFT OUTER JOIN

Creating a RIGHT OUTER JOIN

Creating a RIGHT OUTER JOIN

Creating a FULL OUTER JOIN

Using Self-Joins

Creating Joins with MS Query

Using Subqueries

Types of Subqueries

Defining Subquery Rules

Creating Subqueries That Return a Single Value

Creating Subqueries That Return Multiple Values

Adding NOT IN to a Subquery

Testing Existence with Subqueries

Writing Correlated Subqueries

Creating Derived Tables

Summary

Review Questions

Chapter 7 Creating Action Queries with Transact-SQL to Modify Data

Adding Rows with the Insert Statement

Exploring the INSERT Statement Rules

Inserting Rows with Values

Inserting a Partial Row with a Column List

Adding a Row with Defaults and Nulls

Using DEFAULT to Insert a Value for a Column a Row

Inserting Rows in a Table with an IDENTITY Column

Inserting Specific Values into an IDENTITY Column

Inserting Multiple Rows with a SELECT Statement

Inserting Rows with a SELECT Statement

Inserting Rows with a SELECT Clause

Copying a Subset of Data

Copying a Subset of Rows

Adding Constants to the Copied Data

Cleaning UP

Modifying Data with the UPDATE Statement

Overview of Direct and Deferred Updates

Writing a Simple UPDATE Statement

Updating Select Rows

Using Arithmetic in Updates

Including Subqueries in an UPDATE Statement

Providing a Column Value with a Subquery

Transact-SQL Extensions for the UPDATE Statement

Transact-SQL UPDATE Extension Example

Cleaning Up after Inserting Data

Deleting Rows of Data with the DELETE Statement

Defining the Rules for the DELETE Statement

Deleting All Rows from a Table

Deleting Selected Rows

Deleting Rows of Data Based on a Subquery

Deleting Rows Based on the Value in a Subquery

Transact-SQL Extensions for the DELETE Statement

Deleting Data with Transact -SQL Extensions

Cleaning Up after Deleting Data

Importing and Exporting

Using SQL Transfer Manager to Import and Export Data and Objects

Verifying Permissions Needed to Transfer Data

Identifying the Transfer Manager Options

Transferring the Authors Table with the Transfer Manager

Verifying the Results of the Transfer Manager

Examining the Files Created by the Transfer Manager

Transferring Data with the Bulk Copy Program

Permissions Requirements to Perform a Transfer with the Bulk Copy Program

Defining BCP Rules

Defining the BCP Mode Types

Saving Your BCP Options in a Format File

Copying a Table with the BCP Utility

Comparing the Transfer Manager and the Bulk Copy Program Review Questions

Chapter 8 Using Indexes

Advantages to Using Indexes

Disadvantages to Using Indexes

Creating Useful Indexes

Selecting the Right Index

Guidelines for Choosing Indexes

Guidelines for Choosing What Not to Index

SQL Server Index Types

Examining Clustered Indexes

Exploring Nonclustered Indexes

Refining Index Characteristics

Keeping Up with Index Statistics

Managing SQL Server Indexes

Defining the CREATE INDEX Options

Applying the FILLFACTOR and PAD_INDEX Options

Using the SORTED_DATA and SORTED_DATA_REORG Options

Rebuilding an Index

Examining the DBCC DBREINDEX Syntax

Rebuilding a Specific Index

Rebuilding All Indexes in a Table

Dropping Indexes

Understanding DROP INDEX Permissions

Dropping an Index

Monitoring an Index's Performance and Use

Managing Indexes with SQL Enterprise manager

Summary

Review Questions

Chapter 9 Designing and Using Views,Defaults ,and Rules

An Overview to a View

Creating a View

Guidelines for Creating Views

The CREATE VIEW Statement

Recognizing the Benefits of Using Views

Simplifying Queries

Focusing on Data

Providing Constant Data

Enforcing Security

Exporting Data

Recognizing a View's Limitations

Creating Views

Selecting Specific Columns

Selecting Specific Rows

Joining Tables to Create a View

Including Aggregate Functions in Views

Creating Views on Views

Modifying Data through Views

Inserting Rows through Views

Deleting Rows through Views

Updating Rows through Views

Dropping Rows through Views

Dropping Views

Defining the DROP VIEW Statement

Understanding Rules and Defaults

Creating Rules

Examining the CREATE RULE Statement

Understanding a Rule's Limitations

Creating a Sample Rule

Creating a Rule with the SQL Enterprise Manager

Creating Defaults

Examining the CREATE DEFAULT Statement

Defining a Default's Limitations

Creating a Sample Default

Creating a Default with the SQL Enterprise manager

Binding Rules

Some Guidelines for Binding Rules

Examining the sp_bindrule System Stored Procedure

Binding a Rule

Using SQL Enterprise Manager to Bind a Rule

Unbinding Rules

Examining the sp_unbindrule System Stored Procedure

An Example of unbinding a Rule

Using SQL Enterprise Manager to Unbind a Rule

Dropping Rules

Using SQL Enterprise Manager to Unbind a Rule

Dropping Rules

Using SQL Enterprise Manager to Unbind a Rule

Dropping Rules

Using SQL Enterprise Manager to Drop a Rule

Binding Defaults

Guidelines for Binding Defaults

Examining the sp_bindefault System Stored Procedure

Binding a Default

Using SQL Enterprise Manager to Bind a Default

Unbinding Default

Using SQL Enterprise Manager to Bind a Default

Unbinding Defaults

Examining the sp_unbindefault System Stored Procedure

Binding a Default

Using SQL Enterprise manager to Bind a Default

Dropping Defaults Examining the DROP DEFAULT Statement

Using SQL Enterprise Manager to Drop a Default

Summary

Review Questions

Chapter 10 Programmability

Statement Execution with Batches

Working with Variables

Defining and Using Local Variables

Examining the Syntax Required to Declare and Assign a Local Variable

Declaring and Assigning a Local Variable

Using Global Variables

obtaining the Version of SQL Server with a Global Variable

Adding Comments to Your Code

Using Control-of-Flow Statements

Creating Statement Blocks with BEGIN and END

Using CASE Expressions

Inspecting the Syntax of the CASE Expression

Testing a CASE Expression

Moving to a Label with GOTO

Investigating the GOTO Syntax

Transferring Execution with a GOTO Command

Controlling Program Execution with IF...ELSE

IF...ELSE Guidelines

Executing Code Based on an IF...ELSE Statement Block

Stopping Execution with the RETURN Command

Exiting a Batch with the RETURN Statement

Timing Execution with WAITFOR

Processing in the Future with WAITFOR

Creating Loops with WHILE Blocks

Creating a Loop with a WHILE Block

Working with Cursors

Exploring Cursor States

Declaring a Cursor

Guidelines for Declaring Cursors

Opening a Cursor

Counting the Number of Rows in an Opened Cursor

Fetching Data for a Cursor

Guidelines for the FETCH Statement

Keeping Track of the FETCH Status

Closing a Cursor

Deallocating a Cursor

Demonstrating Cursors

Discussing the Benefits of Using Stored Procedures

Achieving Faster Execution

Producing Modula Programming

Realizing Enhanced Reliability

Enforcing Consistency

Enhancing Security

Discussing the Capabilities of SQL Server and MAPI

Using MAPI to Respond to Server Problems

Understanding MAPI's Stored Procedures

Examining the String and Variable Extensions to the EXECUTE Statement

Executing Procedures

Examining the EXECUTE Statement Syntax for a Stored Procedure

EXECUTE Procedure Guidelines

Executing a Stored Procedure

Returning the Status of a Stored Procedure

Executing a Procedure with a Variable

Executing String Statements

Examining the EXECUTE Statement Syntax for a Character String

Executing a Character String

Managing User-Defined Error Messages

Providing Feedback with the RAISERROR Statement

Discussing RAISERROR Specifics

Raising a User-Defined Error

Summary

Review Questions

Chapter 11 Triggers

Understanding How Triggers Work

Creating Triggers

Examining the Syntax of the CREATE TRIGGER Statement

Trigger Guidelines

Identifying the Limitations of Triggers

Creating an INSERT Trigger

Creating an UPDATE Trigger

Creating a Table Level UPDATE Trigger

Creating a Column Level UPDATE Trigger

Creating a DELETE Trigger

Dropping Triggers

Enforcing Referential Integrity

Creating Triggers to Enforce Referential Integrity

Enforcing Data Integrity

Summary

Review Questions

Chapter 12 Replication

Examining the Publisher/Subscriber Metaphor

Applying Replication Appropriately

Applying the Appropriate Replication Model

Central Publisher

Central Publisher with a Remote Distributor

Publishing Subscriber

Multiple Publishers of One Table

Downloaded Data

Tracing the Log Reader Process

Examining the Server's Role in Replication

Recognizing the Role of the Publication Server

Managing the Publications

Managing Articles

Replication Synchronization

Replication Security

Examining the Frequency of Replication

Recognizing the Role of the Distribution Server

Recognizing the Role of the Subscription Server

Identifying the Tables Used in Replication

Tracing the Replication Process

The Publisher

The Distributor

The Subscriber

Summary

Review Questions

Chapter 13 Application Development and Open Data Services

Defining Open Architecture

Open Database Connectivity's Role in SQL Server

Creating an ODBC Data Source

Using ODBC with Visual Basic and SQL Server

Data Access Objects

Remote Data Objects

ActiveX Data Objects

The Benefits of Integrating OLE Architecture with SQL Server

Exposing Objects with SQL-Distributed Management Objects

Examining the SQL-DMO Model

Understanding Application Objects and Collections

Using SQL-DMO

Extending SQL Server with DB-Library

Extending SQL Server with Open Data Services

Introducing SQL Distributed Management Framework

The SQL-DMF Model

Summary

Review Questions

Appendix A Review Questions and Answers