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