MCSE Database Design on SQL Server 7考前辅导PDF电子书下载

CHAPTER 1 AN OVERVIEW OF MICROSOFT SQL SERVER 7 1

Microsoft SQL Server Version 7 2

Features In Microsoft SQL Server 7 4

Speed 4

Tuning 5

Platforms 5

Upgrades 5

Data Transformations 6

Improvements From Microsoft SQL Server 6.5 6

New Support For Windows 95 And Windows 98 Servers 6

Dynamic Database Size Management 7

Wizards 8

Locking Enhancements 9

Transact SQL Enhancements 10

New Programming Interfaces 11

COM 12

OLE DB 12

ADO 12

Replication Enhancements 13

SQL Server Agent 13

Full-Text Search 14

Improved Support For Data Warehousing 15

Tools 15

Enterprise Manager 16

Query Analyzer 17

SQL Profiler 18

Index Tuning Wizard 19

Data Transformation Services(DTS) 20

osql 21

SQL Scheduler 21

The Database Diagram Tool 22

Chapter Summary 23

Review Questions 25

Hands-On Projects 27

CHAPTER 2 SQL DATABASE ARCHITECTURE 31

The Relational Data Model 32

The Architecture Of A Relational Database System 32

Further Definition Of The Relational Model 34

Structures Within A Relational Database 34

Data Integrity 37

Data Manipulation 39

SQL Server Database Architecture 41

Logical Database Components 43

Physical Database Components 46

Chapter Summary 51

Review Questions 52

Hands-On Projects 55

CHAPTER 3 DEVELOPING A LOGICAL DATABASE DESIGN 59

The Database Life Cycle 60

Gathering Database Requirements 61

Logical Database Design 62

Entity-Relationship Modeling 63

Basic Objects 63

Characteristics Of Relationships 65

Primary,Alternate,And Foreign Keys 68

Nonkey Attributes 71

An ER Model Of Northwind(Part 1) 72

Data Normalization 75

First Normal Form 75

Second Normal Form 76

Third Normal Form 78

Boyce-Codd Normal Form 78

Completing The Normalization Process 79

An ER Model Of Northwind(Part 2) 81

Business Rules 83

Insert/Update Rules 84

Data Domains 86

Other Business Rules 87

Chapter Summary 88

Review Questions 90

Hands-On Projects 94

CHAPTER 4 DERIVING THE PHYSICAL DESIGN 101

Physical Design In The Database Life Cycle 102

Database Design Considerations 104

Database Diagrams 105

Preliminary Physical Design 107

Table Definitions 108

Column Definitions 109

Column Attributes 109

Physical Design For Northwind(Part 1) 111

Logical Data Integrity 113

Primary Keys And Unique Constraints 114

Foreign Keys 116

Check Constraint 118

User-Defined Integrity 118

Physical Design For Northwind(Part 2) 119

Performance Expectations 121

Indexes For Performance 122

Denormalization 123

Table Definitions 124

Security 126

Physical Design For Northwind(Part 3) 126

Impact Of Database Design 127

Chapter Summary 128

Review Questions 130

Hands-On Projects 134

CHAPTER 5 TABLES 137

Table Types 138

Column Data Types 139

NULL/NOT NULL Columns 141

Identity Columns 142

Inserting Explicit Values Into An Identity Column 143

Checking And Correcting Identity Column Values 143

Row Global Unique Identifier Column 143

Defaults 144

Default Object 144

Default Definition 145

Rules 147

Column-Level And Table-Level Constraints 148

CHECK Constraints 149

PRIMARY KEY Constraint 150

UNIQUE Constraint 151

FOREIGN KEY Constraint 153

Creating Tables 154

Altering Tables 156

Adding A Column 156

Dropping A Column 157

Modifying A Column Definition 158

Adding/Dropping The Row Global Unique Identifier Property(ROWGUIDCOL) 159

Adding A Constraint 159

Dropping A Constraint 160

Enabling And Disabling A FOREIGN KEY And CHECK Constraint 160

Enabling And Disabling A Trigger 161

Dropping Tables 161

System Tables 161

Chapter Summary 162

Review Questions 164

Hands-On Projects 167

CHAPTER 6 INDEXES 171

Index Basics 172

Single-Column Index And Composite(Multicolumn)Index 173

Index Intersection And Covered Indexes 173

Clustered Index 174

Nonclustered Index 175

Unique Index 177

Fill Factors 178

PAD_INDEX 180

Creating An Index 180

CREATE INDEX Command 181

Create Index Wizard 183

DBCC DBREINDEX 185

Dropping An Index 186

Viewing An Index 187

Statistics 189

Updating Statistics 191

Creating Statistics 192

Dropping Statistics 193

DBCC SHOW_STATISTICS 193

Index Tuning 194

Index Tuning Wizard 195

Index Analysis Through The Query Analyzer 201

Impact On Total Database Size 201

Impact On Query Performance 202

Impact On DML Performance 203

Chapter Summary 203

Review Questions 205

Hands-On Projects 209

CHAPTER 7 RETRIEVING AND MODIFYING DATA 211

Retrieving Data With SELECT 212

Simple SELECT Statements 212

The WHERE Clause 215

Eliminating Duplicate Rows With DISTINCT 216

Sorting Query Output With ORDER BY 217

Creating Tables With SELECT...INTO 218

Using SELECT To Perform Joins 221

UNION Queries 228

Subqueries 230

Using INSERT To Add Data 235

Using INSERT To Add Individual Rows 235

Using INSERT...SELECT To Insert Multiple Rows 238

Using INSERT...EXECUTE To Insert Multiple Rows 238

Removing Rows With DELETE 240

Removing All Rows With TRUNCATE TABLE 242

Using UPDATE To Modify Data 244

Using UPDATE With Nested Subqueries 245

Using UPDATE...FROM 246

Query Tools And Interfaces 246

Query Tools 246

Programming Interfaces 250

Chapter Summary 251

Review Questions 252

Hands-On Projects 258

CHAPTER 8 TRANSACT-SQL SCRIPTING AND COMMAND BATCHES 265

Putting The Pieces Together 266

Batches And Scripts 266

Local Variables 269

Declaring Local Variables 269

Using Local Variables 269

Scope Of Local Variable Names 270

Dynamic Batch Execution 270

Transact-SQL Comments 271

Control-Of-Flow Language 273

Statements:The Basic Unit For Control-Of-Flow 274

Statement Blocks:Using BEGIN...END 274

IF...ELSE 275

WHILE/BREAK/CONTINUE 276

GOTO 278

RETURN 279

WAITFOR 280

Cursors 280

The Life Cycle Of A Cursor 282

Types Of Cursors 286

Modifying Data With WHERE CURRENT OF 289

Grandma s Secret:Making Better Batches 291

Chapter Summary 293

Review Questions 295

Hands-On Projects 299

CHAPTER 9 SUMMARIZING AND TRANSFORMING DATA 309

Going Beyond Rows And Columns 310

Manipulating Data With Scalar Functions 310

Configuration Functions 310

Cursor Functions 316

Date And Time Functions 318

Mathematical Functions 320

Metadata Functions 322

Security Functions 326

String Functions 329

System Functions 338

System Statistical Functions 348

Text And Image Functions 349

Summarizing Data With Aggregate Functions 352

Scalar Aggregation 353

Vector Aggregation And GROUP BY 354

Other Data Summarization Methods 357

Viewing Only The TOP Query Results 357

Summarizing Multiple Grouping Levels With CUBE 360

ROLLUP——A Hierarchical Subset Of CUBE 362

Chapter Summary 366

Review Questions 368

Hands-On Projects 372

CHAPTER 10 VIEWS 379

Database Views 380

Use Of A View 381

Focus The Data For Users And As A Security Mechanism 381

Shield The Complexity Of Data Manipulation 382

Organize Data Import And Export 383

Working With Partitioned Data Across Multiple Tables 383

Creating A View 384

Restrictions On Views 385

Create View Command 385

Create View Wizard 388

Altering A View 390

Dropping A View 392

Querying Data Through A View 392

Modifying Data Through A View 392

System Views 394

Nested Views And Performance 397

Chapter Summary 398

Review Questions 399

Hands-On Projects 402

CHAPTER 11 TRANSACTIONS AND LOCKING 405

Defining A Transaction 406

Definition 406

ACID Properties 406

Isolation Level 407

Database Locking 412

Locking Scope 412

Types Of Locking 413

Observing Database Locks 414

Dynamic Locking 416

Overriding Default Locking Behavior 416

Deadlocking 419

Observing Deadlock Behavior 420

Avoiding Deadlocks 421

Programming Transactions 423

Implicit,Explicit,And Autocommit Transactions 423

Naming Transactions 424

Nesting 425

Savepoints Within A Transaction 426

Transaction Behavior Within Triggers 427

Distributed Transactions 427

Cursors And Optimistic Locking 428

Chapter Summary 430

Review Questions 432

Hands-On Projects 434

CHAPTER 12 STORED PROCEDURES 439

Stored Procedures 440

Why Use A Stored Procedure? 440

Types Of Stored Procedures 441

System Stored Procedures 441

Extended Stored Procedures 442

Local(User)Stored Procedures 442

Remote Stored Procedures 443

Temporary Stored Procedures 443

Deferred Name Resolution And Compilation Of Stored Procedures 443

Parsing 443

Resolution 444

Optimization 444

Compilation 445

Creating A Stored Procedure 445

Grouping Stored Procedures 447

CREATE PROCEDURE Statement 447

Create Stored Procedure Wizard 450

Nesting Stored Procedures 451

Stored Procedures Parameters 453

Input Parameters 454

Output Parameters 455

Returning Data From Stored Procedures 457

Altering A Stored Procedure 459

Dropping A Stored Procedure 461

Executing A Stored Procedure 461

Enforcing Business Rules With Stored Procedures 464

The RAISERROR Statement 465

Chapter Summary 467

Review Questions 468

Hands-On Projects 471

CHAPTER 13 TRIGGERS 475

What s A Trigger? 476

Types Of Triggers 476

Special Tables:Deleted Table And Inserted Table 477

Creating A Trigger 478

CREATE TRIGGER Statement 480

Altering A Trigger 482

Dropping A Trigger 484

Checking For Changes To A Column 484

IF UPDATE(<column_name>) 484

IF COLUMNS_UPDATED() 485

Triggers And Transactions 487

Nested Triggers 488

Recursive Triggers 489

TRIGGER_NESTLEVEL()Function 489

Enforcing Business Rules And Data Integrity With Triggers 491

Cascading Updates And Cascading Deletes 493

Coding Triggers 494

Chapter Summary 495

Review Questions 497

Hands-On Projects 501

CHAPTER 14 CREATING THE PHYSICAL DATABASE 505

Sizing SQL Server Database Components 506

Database File 506

Primary Data File 507

Secondary Data File 507

Transaction Log File 508

Database Filegroups 508

Primary Filegroup 509

User-Defined Filegroup 510

Creating A Database 510

CREATE DATABASE Statement 511

Create Database Wizard 514

Modifying A Database 516

Adding Data Files 516

Adding Transaction Log Files 517

Removing A Data Or Transaction Log File 517

Modifying A Data Or Transaction Log File 517

Adding A Filegroup 518

Removing A Filegroup 518

Modifying A Filegroup Property(READONLY,READWRITE,DEFAULT) 519

Dropping A Database 519

Expanding A Database 520

Shrinking A Database 520

Automatically Shrinking A Databse 520

DBCC SHRINKDATABASE Statement 521

DBCC SHRINKFILE Statement 521

Database Options 522

Attaching And Detaching A Database 525

sp_detach_db 525

sp_attach_db 526

Renaming A Database 527

Database Owner 528

System Databases 528

master Database 529

model Database 529

msdb Database 529

tempdb Database 529

Storage Options And RAID Levels 529

Chapter Summary 531

Review Questions 533

Hands-On Projects 536

CHAPTER 15 REMOTE DATA SOURCES 541

Remote Data Sources 542

Remote Server Architecture 542

Configuring A Remote Server 543

Executing Remote Procedures 544

Linked Server Architecture 544

Seamless SELECT 546

FREQUENT OPENQUERY 548

Occasional OPENROWSET 549

Leaving Locality 550

Undeniable Updates 552

Configuring Linked Servers 553

SQL Server Data Sources 554

MS Access Data Sources 557

Oracle Data Sources 558

Other Data Sources 560

Additional Configuration Settings 560

Chapter Summary 563

Review Questions 564

Hands-On Projects 567

CHAPTER 16 DATA TRANSFERS 569

Importing And Exporting 570

Deciding Between Transfer Tools 570

Bulk Copy 572

Specifying Connection Information 573

Working With The Host File 574

Bulk Loads 580

Processing Options 582

Optimizing Performance 582

BULK INSERT 584

Data Transformation Services 585

Import And Export Wizard 585

DTS Designer 587

Chapter Summary 600

Review Questions 601

Hands-On Projects 604

CHAPTER 17 IMPLEMENTING FULL-TEXT SEARCH 607

Full-Text Architecture 608

Full-Text Catalog Administration 612

Configuring A New Catalog 614

Using The Full-Text Catalog Wizard 616

Maintaining The Catalog 618

Querying 623

CONTAINS 624

FREETEXT 627

CONTAINSTABLE 627

FREETEXTTABLE 629

Chapter Summary 629

Review Questions 630

Hands-On Projects 635

CHAPTER 18 MAINTAINING A DATABASE 639

Database Consistency 640

Storage Primer 640

DBCC Commands 642

Efficiency-Contiguous Storage 648

DBCC SHOWCONTIG 649

DBCC Reindex 653

Identity Columns 654

Distribution Statistics 656

Database Maintenance Planning Wizard 664

Chapter Summary 665

Review Questions 666

Hands-On Projects 669

CHAPTER 19 DIAGNOSING PROBLEMS AND IMPROVING QUERY PERFORMANCE 675

Query Plans 676

Simple Text Query Plans 676

Comprehensive Text Query Plans 678

Graphical Query Plans 678

Index Tuning Wizard 682

Workload Files 682

Running The Index Tuning Wizard 684

SQL Profiler 686

Tracing An Application 686

Table Scans 688

Finding Deadlocks 689

Procedure Usage Profile 690

Performance Monitor 691

Access Methods 693

Databases 694

General Statistics 695

Locks 695

SQL Statistics 696

User Settable Statistics 697

Chapter Summary 697

Review Questions 698

Hands-On Projects 701

APPENDIX A DESIGN ON SQL SERVER 7 EXAM OBJECTIVES 707

APPENDIX B MCSE REQUIREMENTS 711

GLOSSARY 713

INDEX 731