数据库与数据仓库实用操作大全 英文本PDF电子书下载

Part1 Introduction and Overview 1

Chapter1 Welcome to the SQL Server2000 Reference Library 3

Chapter2 Failover Clustering 7

Creating a Failover Cluster 9

Upgrading to a SQL Server 2000 Failover Cluster 15

Handling a Failover Cluster Installation 15

Maintaining a Failover Cluster 21

Using SQL Server Tools with Failover Clustering 22

Failover Cluster Troubleshooting 24

Part2 Creating and Maintaining Databases 27

Chapter3 Databases 29

Parts of a Database 29

Files and Filegroups 30

Transaction Logs 35

Database Design Considerations 37

Creating a Database Plan 37

Online Transaction Processing vs.Decision Support 39

Normalization 41

Data Integrity 44

Data Security 45

Database Performance 46

Maintenance 47

Estimating the Size of a Database 47

Creating a Database 56

Using Raw Partitions 58

Modifying a Database 59

Expanding a DatabaseI 60

Shrinking a Database 61

Creating Filegroups 63

Changing the Default Filegroup 64

Setting Database Options 64

Creating a Removable Database 74

Attaching and Detaching a Database 75

Renaming a Database 76

Changing the Database Owner 77

Transaction Termination for Changing Database States 77

Displqying Database and Transaction Log Space 78

Documenting and Scripting Databases 78

Viewing a Database 78

Database Maintenance Plan Wizard 79

Deleting a Database 80

Chapter4 Tables 81

Designing Tables 81

Specifying a Column Data Type 82

Text in Row Data 87

Autonumbering and Identifier Columns 90

Using Constraints,Defaults,and Null Values 90

Creating and Modifying a Table 100

Modifying Column Properties 102

Adding and Deleting Columns 104

Creating and Modifying PRIMARY KEY Constraints 104

Creating and Modifying FOREIGN KEY Congstraints 105

Creating and Modifying UNIQUE Constraints 107

Creating and Modifying CHECK Constraints 108

Creating and Modifying DEFAULT Definitions 109

Creating and Modifying Identifier Columns 110

Viewing a Table 111

Deleting a Table 112

Chapter5 Indexes 113

Designing an Index 114

Creating an Index 125

Rebuilding an Index 137

Viewing an Index 138

Deleting an Index 138

Renaming an Index 138

Chapter6 Views 141

Scenarios for Using Views 142

Creating a View 144

Designing an Indexed View 146

Creating an Indexed View 150

Creating a Partitioned View 154

Modifying and Renaming a View 160

Modifying Data Through a View 161

Getting Information About a View 163

Deleting a View 163

Chapter7 Stored Procedures 165

Extended Stored Procedures 166

Creating a Stored Procedure 167

Specifying Parameters 169

Programming Stored Procedures 173

Returning Data from a Stored Procedure 181

Executing a Stored Procedure 186

Automatic Execution of Stored Procedures 187

Modifying and Renaming a Stored Procedure 188

Recompiling a Stored Procedure 188

Viewing a Stored Procedure 189

Deleting a Storde Procedure 190

Chapter8 Enforcing Business Rules With Triggers 191

Designing Triggers 193

Trigger Execution 194

Specifying When a Trigger Fires 194

Designing INSTEAD OF Triggers 195

Creating a Trigger 198

Programming Triggers 200

Using Triggers that Include ROLLBACK TRANSACTION 202

Using the inserted and deleted Tables 203

Multirow Considerations 205

Conditional INSERT Trigger 207

Specifying First and Last Triggers 209

Using Nested Triggers 211

Using INSTEAD OF Triggers 214

Using text ,ntext,and image Data in INSTEAD OF Triggers 223

Activating Triggers with Implicit and Explicit Null Values 224

Viewing a Trigger 226

Modifying and Renaming a Trigger 226

Deleting a Trigger 227

Chapter9 User-Defined Functions 229

User-Defined Functions That Return a Table Data Type 233

Inline User-Defined Functions 234

Deterministic and Nondeterministic Functions 237

Rewriting Stored Procedures as Functions 240

Using Extended Properties on Database Objects 241

Chapter10 Full-Text Indexes 245

Administering Full-Text Features Using SQL Enterprise Manager 247

Administering Full-Text Features Using Stored Procedures and Scalar Functions 247

Enabling Others to Issue Full-Text Queries 249

Performing Investigation and Clean-up Tasks for Full Text Catalogs 252

Performing Infrequent Tasks 254

Maintaining Full-Text Indexes 255

Filtering Supported File Types 257

Column-Level Linguistic Analysis 258

Part3 Creating and Using Data Warehouses 259

Chapter11 Data Warehousing Overview 261

SQL Server 2000 Tools for Data Warehouses 261

Chapter12 Parts of a Data Warehouse 265

Data Marts 265

Relational Databases 267

Data Sources 267

Data Preparation Area 268

Presentation Services 269

End-User Analysis 272

Chapter13 Creating a Data Warehouse 273

Designing a Data Warehouse 273

Using Dimensional Modeling 275

Fact Tables 275

Aggregation Tables 277

Dimension Tables 278

Indexes 281

Creating the Data Preparation Area 281

Creating the Data Warehouse Database 282

Extracting Data from Operational Systems 283

Cleansing and Transforming Data 284

Loading Data into the Data Warehouse Database 285

Preparing Presentation Information 287

Distributing Data to Data Marts 287

Chapter14 Using a Data Warehouse 289

SQL Queries 290

OLAP and Data Mining 291

English Query 291

Microsoft Office 2000 292

Web Access and Reporting 292

Offline OLAP Cubes 293

Third-Party Applications 293

Custom Applications 294

Chapter15 Maintaining a Data Warehouse 295

Updating Data Warehouse Data 295

Scheduling Data Updates 296

Synchronizing OLAP Cubes 297

Updating Data Marts 298

Administering a Data Warehouse 298

Backing Up Data Warehouse Data 299

Automating Data Warehouse Tasks 300

Tuning Data Warehouse Performance 300

Part4 Optimizing Database Performance 301

Chapter16 Designing Federated Database Servers 303

Designing Partitions 304

Designing Federated Database Servers for High Availability 307

Backing Up and Restoring Federated Database Servers 308

Logical Database Design 309

Chapter17 Database Design 309

Database Design Considerations:Data Types 310

Physical Database Design 311

RAID 311

Partitioning 319

Data Placement Using Filegroups 321

Index Tuning Recommendations 323

Optimizing Transaction Log Performance 324

Optimizing tempdb Performance 325

File Systems 326

Chapter18 Query Tuning 327

Analyzing a Query 329

Graphically Displqying the Execution Plan Using SQL Query Analyzer 330

Logical and Physical Operatiors 335

Query Tuning Recommendations 355

Advanced Query Tuning Concepts 357

Understanding Nested Loops Joins 358

Understanding Merge Joins 358

Understanding Hash Joins 359

Chapter19 Application Design 361

Networking and Performance 364

Named Pipes vs,TCP/IP Sockets 364

Optimizing Application Performance Using Efficient Data Retrieval 365

Effects of Transactions and Batches on Application Performance 367

Effects of Stored Procedures on Application Performance 368

Understanding and Avoiding Blocking 369

Optimizing Distributed Queries 371

Chapter20 Optimizing Utility and Tool Performance 375

Optimizing Backup and Restore Performance 375

Optimizing Bulk Copy Performance 379

Optimizing DBCC Performance 382

Chapter21 Optimizing Server Performance 383

Optimizing Server Performance Using Memory Configuration Options 383

Optimizing Server Performance Using I/O Configuration Options 385

Optimizing Server Performance Using Windows NT Options 386

Maximizing Throughput 386

Configuring Server Tasking 386

Configuring Virtual Memory 387

IndexA Topics in the SQL Server 2000 Reference Library 389

IndexB Index of Optimizing Database Performance Elements 421