MCSD/MCSE：SQL Server 7 Database Design学习指南 英文原版

Intoroduction

Chapter 1 An 1ntroduction to SQL Server 7

SQL Server 7 Is New and Improved

SQL Enterprise Manager

Transact-SQL

SQL--DMO

SQL Server Wizards

SQL Server Capacities

Tools and Utilities Included in SQL Server 7

Query Analyzer

Working with Queries

Viewing the Execution Plan

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 guestions

Chapter 2 Data Modeling

The Relational Model

The Client/Server Model

File-Server vsDistributed 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 and Foreign Keys

Column Restrictions

Understanding NULL and NOT NULL

Disallowing Duplicates

Adding Defaults

Prohibiting Changes

Improving the ER Model fOr the Downtown Delivery Servlce

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

Third Normal Form

Benefits of Normalization

Drawbacks of Normalization

Summary

Review guestions

Chapter 3 System Databases and Tables

Examlning the System Databases

Identifytng the Role of the Model Database

Identifylng the Role of the Msdb Database

Identifying the Role of the Tempdb Database

Discussing System Tables

System Catalog

The Database Catalog

guetying System Tables

Accessing the System with System Stored Procedures

Catalog Stored Procedures

Extended Stored Procedures

Replication Stored Procedures

System Stored Procedures

Executing Stored Procedures

sp--help [objectnamel

sp--helpdb [dbnamel

sp--Who

Configurlng SQL Server

Summary

Review guestions

Chapter 4 Data Definition

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 with 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 DATAASE

Shrinking a Database

Using SQL EnterPrise Manager to Shrink a Database

Shrinking a Database with DBCC

Using DBCC SHRINKDATABASE 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 7 Data Whes

System--Supplied Data bees

User--Defined Data bees

Managing Tables in SQL Server 7

Creating a Tab1e with SQL EnterPrise Manager

T Jsing the CREATE TABLE Statement to Create a Table

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

Understanding Data Integrity

Identifying the Whes of Data Integrity

EnfOrcing Data Iritegrity with the IDENTITY Property

Identifying Guidelines Associated with the IDENTITY

Property

Creating an Identity Column

Creating a Table with an ldentity Column

Adding a Column with the IDENTITY Property to an Existing

Table

Using Uniqueidentifier and NEWID to Generate Unique Values

Using Constraints to Enforce Data Integrity

Managing Constraints with the SQL EnterPrise Manager

Managing Constraints with the CREATE TABKE and ALTER

TABLK StotementH

Creating a PRIMARY KEY Constraint

Creating a FOREIGN KEY Constraint

Creating a UNIgUE Constraint

Creating a CHECK Constraint

Creating a DEFAULT Constraint

Summary

Review guestions

Chapter 5 Data Retrieval

A Refresher Course in SQL

Coding an SQL SELECT Statement

Using Character Strings and guotes

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 SQLs Built--In Functions to Format Output Data

Converting Data twes with the CONVERT Function

Manipulating Datetime Values with Date Functions

Performing Mathematical Operations with the built--In

Mathematlcal Functions

PerfOrming Operations with String Functions

Retrieving Special Server or Database Information with

System Functions

Using Text and Image Functions

Summary

Review guestions

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 Data with the GROUP BY Clause

Examining 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 HAWNG Clause

Including Multiple Coriditions 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 the COMPUTE BY Multiple Times in the Same SELECT

Statement

Returning TOP n Queries

Using CUBE and ROLLUP

Joining Tables

ANSI--Standard vsOld--Style Joins

Creating a CROSS JOIN

Creating an INNER JOIN

Creating OUTER JOINs

Creating a LErv OUTER JOIN

Creatin

Creating a RIGHT OUTER JOIN

Creating a FULL OUTER JOIN

Using Self Joins

Using Subqueries

bees 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

Distributed Queries

Ad Hoc Queries

Setting Up and Using a Linked Server

Summary -

Review guestions -

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

Adding Rows with the INSERT Statement

INSERT Statement Syntax

EXPlorlng 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

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

Understanding the Rules for the UPDATE Statement

Updating Rows

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

DELETE Statement Syntax

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 Data Transformation Services to Transfer Data and

Objects

Creating a DTS Package

Tfansferring the Authors Table with DTS

Verifying the Results of the Transfer

Transferring Data with the Bulk Copy Program

Permissions Requirements to Perform a Transfer with the

Bulk Copy Program"

Defining BCP Syntax and Parameters

Understanding Some BCP Rules

Defining the BCP Mode bees

Saving Your BCP Options in a Format File

Copying a Table with the BCP Utility

Comparing Data TransfOrmation Services and the Bulk Copy

Program

Summary

Review guestions

Chapter 8 Uslng 1ndexes

Advantages to Using Indexes

Disadvantages to Using Indexes

Creating Useful Indexes

Selectlng the Right Index

Guldellnes for Choosing What to Index

Guidellnes for Choosing What Not to Index

SQL Server Index bees

Examining Clustered Indexes

ExPlorlng 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

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 Indexs PerfOrmance and Use

Full--Text Searches

Search Service Objects

Creating Full-Text Indexes

Deactivating Full-Text Indexes

Creating a Full-Text Index

Using Full-Text Queries

Summary

Review guestions

Chapter 9 Designing and Using Views

An Overview of 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 Views Limitations

Creating Views

Selecting Specific Columns

Selecting Specific Rows

Joining Tables to Create a View

Including Aggregate Furictions in Views

Creating Views ofl Views

Modifying Data through Views

Inserting Rows through Views

Deleting Rows through Views

Updatirig Rows through Views

Dropping Views

Defining the DROP VIEW Statement

Summary

Review guestions

ChaPter 1O Programmability

Using Batehes

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

Uslng Coritrol-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 Sy11tax

Transferring ExecutioIl with a GOTO Command

Controlling PrograIn Execution with IFELSE

IFELSE Guidelines

Executillg Code Based on an lFELSE Statement Block

Timing Execlltion with WAITFOR

Processing in the Future with WAITFOR

Creating Loops with WHILE Blocks

Using the WHILE I3lock to Create a Loop

Working with C1Jrsors

Exploring Cursor States

Declaring a Cursor

Guidelines for Declaring Cllrsors

Opening a Cursor

Counting the Number of Rows in an Operied Cursor

Fetching Data from a Cursor

Guidelines for the FETCH Statement

Keepirig Track of the FETCH Status

Closing a Cursor

Deallocating a Cursor

Demonstrating Cursors

The Benefits of Usirig Stored Procedures

Examining the String and Variable Extensions to the

EXECUTE Statement

Executing Procedures

EXECUTE Procedure Guidelines

Executing a Stored Procedure

Returning the Status of a Stored Procedure

Executing a Procedure with a Variable

Executing String Statements

Executing a Character String

Using Input and Output Parameters

Managing User-Defined Error Messages

Providing Feedback with the RAISERROR Statement

RAISERROR Specifics

Raising a User--Defined Error

Stopping Execution with the RETURN Command

Exiting a Batch with the RETURN Statement

Recompiling Stored Procedures

Using Transactions

Defining the Transaction Isolation Level

Designing Transactions of APpropriate Length

Avoiding or Handling Deadlocks

Using ..trancount to Implement Error Handling

Summary

Review guestions

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 guestions

peter 12 Malntaining a Database

Using the DataBase Consistency Checker

Optimizing Execution Plan Performance

Query Execution Plans

SQL Server Locks

Using SQL Server Profiler

Summary

Review guestions

7 Review guestions

f Wendis A Revlew 9uestions and Answers

Chapter l

Chapter 2

Chapter 3

Chapter 4

Chapter 5

Chapter 6

Chapter 7

Chapter 8

Chapter 9

Chapter 1O

Chapter l1

Chapter l2