Microsoft® SQL Server® 2008 Bible

Foreword ..................................................................................................................................................xxxiii

Introduction .............................................................................................................................................. xxxv

Part I Laying the Foundation

Chapter 1: The World of SQL Server ................................................................................................................................3

Chapter 2: Data Architecture ............................................................................................................................................27

Chapter 3: Relational Database Design ..............................................................................................................................43

Chapter 4: Installing SQL Server 2008 .............................................................................................................................73

Chapter 5: Client Connectivity .........................................................................................................................................93

Chapter 6: Using Management Studio ............................................................................................................................101

Chapter 7: Scripting with PowerShell .............................................................................................................................129

Part II Manipulating Data with Select

Chapter 8: Introducing Basic Query Flow ......................................................................................................................167

Chapter 9: Data Types, Expressions, and Scalar Functions ...........................................................................................197

Chapter 10: Merging Data with Joins and Unions .........................................................................................................227

Chapter 11: Including Data with Subqueries and CTEs ................................................................................................259

Chapter 12: Aggregating Data .........................................................................................................................................289

Chapter 13: Windowing and Ranking ............................................................................................................................319

Chapter 14: Projecting Data Through Views ..................................................................................................................329

Chapter 15: Modifying Data ...........................................................................................................................................347

Chapter 16: Modification Obstacles ...............................................................................................................................377

Part III Beyond Relational

Chapter 17: Traversing Hierarchies ................................................................................................................................399

Chapter 18: Manipulating XML Data ..............................................................................................................................435

Chapter 19: Using Integrated Full-Text Search ..............................................................................................................491

Part IV Developing with SQL Server

Chapter 20: Creating the Physical Database Schema .....................................................................................................513

Chapter 21: Programming with T-SQL ...........................................................................................................................559

Chapter 22: Kill the Cursor! ...........................................................................................................................................579

Chapter 23: T-SQL Error Handling ...............................................................................................................................593

Chapter 24: Developing Stored Procedures ....................................................................................................................607

Chapter 25: Building User-Defined Functions ................................................................................................................623

Chapter 26: Creating DML Triggers ...............................................................................................................................635

Chapter 27: DDL Triggers ..............................................................................................................................................657

Chapter 28: Building Out the Data Abstraction Layer ...................................................................................................665

Chapter 29: Dynamic SQL and Code Generation ..........................................................................................................673

Part V Data Connectivity

Chapter 30: Bulk Operations ..........................................................................................................................................685

Chapter 31: Executing Distributed Queries ....................................................................................................................691

Chapter 32: Programming with ADO.NET 3.5 ..............................................................................................................715

Chapter 33: Sync Framework .........................................................................................................................................751

Chapter 34: LINQ ...........................................................................................................................................................775

Chapter 35: Asynchronous Messaging with Service Broker ...........................................................................................807

Chapter 36: Replicating Data ..........................................................................................................................................813

Chapter 37: Performing ETL with Integration Services .................................................................................................829

Chapter 38: Access as a Front End to SQL Server ........................................................................................................867

Part VI Enterprise Data Management

Chapter 39: Configuring SQL Server ..............................................................................................................................883

Chapter 40: Policy-Based Management ...........................................................................................................................939

Chapter 41: Recovery Planning ......................................................................................................................................953

Chapter 42: Maintaining the Database ............................................................................................................................985

Chapter 43: Automating Database Maintenance with SQL Server Agent ...................................................................1011

Chapter 44: Transferring Databases ..............................................................................................................................1039

Chapter 45: Database Snapshots ..................................................................................................................................1059

Chapter 46: Log Shipping .............................................................................................................................................1069

Chapter 47: Database Mirroring ...................................................................................................................................1091

Chapter 48: Clustering ..................................................................................................................................................1119

Part VII Security

Chapter 49: Authenticating Principals ...........................................................................................................................1169

Chapter 50: Authorizing Securables ..............................................................................................................................1187

Chapter 51: Data Cryptography ...................................................................................................................................1197

Chapter 52: Row-Level Security ...................................................................................................................................1205

Part VIII Monitoring and Auditing

Chapter 53: Data Audit Triggers ..................................................................................................................................1223

Chapter 54: Schema Audit Triggers ..............................................................................................................................1233

Chapter 55: Performance Monitor ................................................................................................................................1237

Chapter 56: Tracing and Profiling ................................................................................................................................1243

Chapter 57: Wait States ................................................................................................................................................1255

Chapter 58: Extended Events .......................................................................................................................................1261

Chapter 59: Change Tracking .......................................................................................................................................1267

Chapter 60: Change Data Capture ...............................................................................................................................1285

Chapter 61: SQL Audit .................................................................................................................................................1297

Chapter 62: Management Data Warehouse ..................................................................................................................1305

Part IX Performance Tuning and Optimization

Chapter 63: Interpreting Query Execution Plans .........................................................................................................1313

Chapter 64: Indexing Strategies ....................................................................................................................................1321

Chapter 65: Query Plan Reuse .....................................................................................................................................1357

Chapter 66: Managing Transactions, Locking, and Blocking ......................................................................................1363

Chapter 67: Data Compression ....................................................................................................................................1415

Chapter 68: Partitioning ................................................................................................................................................1427

Chapter 69: Resource Governor ...................................................................................................................................1453

Part X Business Intelligence

Chapter 70: BI Design ...................................................................................................................................................1461

Chapter 71: Building Multidimensional Cubes with Analysis Services .......................................................................1469

Chapter 72: Programming MDX Queries ......................................................................................................................1509

Chapter 73: Authoring Reports with Reporting Services .............................................................................................1527

Chapter 74: Administering Reporting Services .............................................................................................................1559

Chapter 75: Analyzing Data with Excel ........................................................................................................................1577

Chapter 76: Data Mining with Analysis Services .........................................................................................................1593

Appendix A: SQL Server 2008 Specifications ..........................................................................................1613

Appendix B: Using the Sample Databases ................................................................................................1619

Index .........................................................................................................................................................1625