Introduction xxix
Part I: Laying the Foundations 1
Chapter 1: The World of SQL Server 3
Chapter 2: Data Architecture 15
Chapter 3: Installing SQL Server 31
Chapter 4: Client Connectivity 71
Chapter 5: SQL Server Management and Development Tools 81
Part II: Building Databases and Working with Data 109
Chapter 6: Introducing Basic Query Flow 111
Chapter 7: Relational Database Design and Creating the Physical Database Schema 143
Chapter 8: Data Types, Expressions, and Scalar Functions 173
Chapter 9: Merging Data with Joins, Subqueries, and CTEs 213
Chapter 10: Aggregating, Windowing, and Ranking Data 249
Chapter 11: Projecting Data Through Views 271
Chapter 12: Modifying Data In SQL Server 291
Part III: Advanced T-SQL Data Types and Querying Techniques 323
Chapter 13: Working with Hierarchies 325
Chapter 14: Using XML Data 339
Chapter 15: Executing Distributed Queries 367
Part IV: Programming with T-SQL 387
Chapter 16: Programming with T-SQL 389
Chapter 17: Developing Stored Procedures 431
Chapter 18: Building User-Defi ned Functions 453
Part V: Enterprise Data Management 467
Chapter 19: Confi guring SQL Server 469
Chapter 20: Policy Based Management 531
Chapter 21: Backup and Recovery Planning 547
Chapter 22: Maintaining the Database 585
Chapter 23: Transferring Databases 615
Chapter 24: Database Snapshots 635
Chapter 25: Asynchronous Messaging with Service Broker 647
Chapter 26: Log Shipping 661
Chapter 27: Database Mirroring 685
Chapter 28: Replicating Data 727
Chapter 29: Clustering 745
Chapter 30: Confi guring and Managing SQL Server with PowerShell 769
Chapter 31: Managing Data in Windows Azure SQL Database 801
Part VI: Securing Your SQL Server 831
Chapter 32: Authentication Types in SQL Server 833
Chapter 33: Authorizing Securables 841
Chapter 34: Data Encryption 853
Chapter 35: Row-Level Security 861
Part VII: Monitoring and Auditing 877
Chapter 36: Creating Triggers 879
Chapter 37: Performance Monitor and PAL 909
Chapter 38: Using Profi ler and SQL Trace 919
Chapter 39: Wait States 931
Chapter 40: Extended Events 937
Chapter 41: Data Change Tracking and Capture 945
Chapter 42: SQL Audit 967
Chapter 43: Management Data Warehouse 977
Part VIII: Performance Tuning and Optimization 993
Chapter 44: Interpreting Query Execution Plans 995
Chapter 45: Indexing Strategies 1005
Chapter 46: Maximizing Query Plan Reuse 1041
Chapter 47: Managing Transactions, Locking, and Blocking 1047
Chapter 48: Data Compression 1097
Chapter 49: Partitioning 1109
Chapter 50: Resource Governor 1131
Part IX: Business Intelligence 1141
Chapter 51: Business Intelligence Database Design 1143
Chapter 52: Building, Deploying, and Managing ETL Workfl ows in Integration Services 1155
Chapter 53: Building Multidimensional Cubes in Analysis Services with MDX 1195
Chapter 54: Confi guring and Administering Analysis Services 1223
Chapter 55: Authoring Reports in Reporting Services 1233
Chapter 56: Configuring and Administering Reporting Services 1261
Chapter 57: Data Mining with Analysis Services 1281
Chapter 58: Creating and Deploying BI Semantic Models 1299
Chapter 59: Creating and Deploying Power View Reports 1327
Index 1347
