Microsoft SQL Server 2012 Bible

Part I: Laying the Foundations 1

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

SQL Server History ................................................................................................ 3

SQL Server in the Database Market ........................................................................ 4

SQL Server Components ......................................................................................... 5

Editions of SQL Server 2012 ................................................................................. 12

Notable SQL Server 2012 Enhancements ................................................................ 12

Summary ............................................................................................................ 14

Chapter 2: Data Architecture . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 15

Information Architecture Principle ...................................................................... 16

Database Objectives ............................................................................................. 17

Smart Database Design ........................................................................................ 22

Summary ............................................................................................................ 29

Chapter 3: Installing SQL Server . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 31

Preparing the Server ........................................................................................... 32

Selecting the Edition .......................................................................................... 34

The Installation Process ...................................................................................... 42

Summary ............................................................................................................ 69

Chapter 4: Client Connectivity . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 71

Enabling Server Connectivity ............................................................................... 72

SQL Server Native Client Features ..........................................................................74

Summary ............................................................................................................ 79

Chapter 5: SQL Server Management and Development Tools . . . . . . . . . . . . . . . . . . . . . . 81

Organizing the Interface ..................................................................................... 82

Registered Servers .............................................................................................. 86

Object Explorer ................................................................................................... 89

Using the Query Editor ........................................................................................ 99

Using the Solution Explorer ................................................................................105

Getting a Jumpstart on Code with Templates and Code Snippets ............................106

Summary ...........................................................................................................106

Part II: Building Databases and Working with Data 109

Chapter 6: Introducing Basic Query Flow . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 111

Understanding Query Flow ..................................................................................112

FROM Clause Data Sources ...................................................................................117

WHERE Conditions .............................................................................................120

(0 row(s) affected)Columns, Stars, Aliases, and Expressions ...................................129

Ordering the Result Set ......................................................................................131

Select Distinct ...................................................................................................137

TOP () ...............................................................................................................138

Summary ...........................................................................................................142

Chapter 7: Relational Database Design and Creating the Physical

Database Schema . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 143

Database Basics .................................................................................................143

Data Design Patterns ..........................................................................................154

Normal Forms ....................................................................................................164

Strategy Considerations ......................................................................................170

Summary ...........................................................................................................171

Chapter 8: Data Types, Expressions, and Scalar Functions . . . . . . . . . . . . . . . . . . . . . . . 173

Data Types .........................................................................................................173

Building Expressions ..........................................................................................177

Scalar Functions ................................................................................................190

String Functions ................................................................................................197

Soundex Functions .............................................................................................203

Data-Type Conversion Functions ..........................................................................207

Server Environment Information .........................................................................210

Summary ...........................................................................................................211

Chapter 9: Merging Data with Joins, Subqueries, and CTEs . . . . . . . . . . . . . . . . . . . . . . 213

Using Joins........................................................................................................215

Set Difference Queries ........................................................................................232

Using Unions .....................................................................................................233

Subqueries ........................................................................................................237

Summary ...........................................................................................................247

Chapter 10: Aggregating, Windowing, and Ranking Data . . . . . . . . . . . . . . . . . . . . . . . . 249

Aggregating Data ...............................................................................................249

Grouping Within a Result Set ..............................................................................252

Windowing and Ranking .....................................................................................256

Ranking Functions .............................................................................................259

Summary ...........................................................................................................269

Chapter 11: Projecting Data Through Views . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 271

Why Use Views? .................................................................................................271

The Basic View ...................................................................................................274

A Broader Point of View ......................................................................................279

Locking Down the View ......................................................................................284

Using SQL Synonyms ..........................................................................................288

Summary ...........................................................................................................289

Chapter 12: Modifying Data In SQL Server . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 291

Inserting Data ...................................................................................................292

Updating Data ...................................................................................................302

Deleting Data .....................................................................................................310

Merging Data .....................................................................................................313

Returning Modifi ed Data ....................................................................................318

Summary ...........................................................................................................321

Part III: Advanced T-SQL Data Types and Querying

Techniques 323

Chapter 13: Working with Hierarchies . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 325

HierarchyID .......................................................................................................326

HierarchyID Methods ..........................................................................................331

Indexing Strategies ............................................................................................334

Hierarchical Data Alternatives ............................................................................334

Summary ...........................................................................................................337

Chapter 14: Using XML Data . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 339

The XML Data Type .............................................................................................340

XML Data Type Methods ......................................................................................353

FOR XML ............................................................................................................355

XQuery and FLWOR Operations ............................................................................363

Summary ...........................................................................................................365

Chapter 15: Executing Distributed Queries . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 367

Distributed Query Overview ................................................................................367

Developing Distributed Queries ...........................................................................377

Performance Consideration .................................................................................383

Summary ...........................................................................................................386

Part IV: Programming with T-SQL 387

Chapter 16: Programming with T-SQL . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 389

Transact-SQL Fundamentals ................................................................................390

Working with Variables .......................................................................................393

Procedural Flow .................................................................................................399

Examining SQL Server with Code .........................................................................403

Temporary Tables and Table Variables ..................................................................406

What’s New in T-SQL for 2012 ..............................................................................409

Error Handling ...................................................................................................412

Bulk Operations .................................................................................................424

Summary ...........................................................................................................428

Chapter 17: Developing Stored Procedures . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 431

Managing Stored Procedures ...............................................................................432

Passing Data to Stored Procedures .......................................................................437

Returning Data from Stored Procedures .............................................................. 444

Summary ...........................................................................................................451

Chapter 18: Building User-Defi ned Functions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 453

Scalar Functions ................................................................................................455

Inline Table-Valued Functions .............................................................................458

Multistatement Table-Valued Functions ...............................................................462

Best Practices with User-Defi ned Functions ..........................................................464

Summary ...........................................................................................................465

Part V: Enterprise Data Management 467

Chapter 19: Confi guring SQL Server . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 469

Setting the Options ............................................................................................469

Confi guration Options ........................................................................................477

Summary ...........................................................................................................529

Chapter 20: Policy Based Management . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 531

Defi ning Policies ................................................................................................532

Evaluating Policies .............................................................................................543

Summary ...........................................................................................................545

Chapter 21: Backup and Recovery Planning . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 547

Recovery Concepts .............................................................................................548

Recovery Models ................................................................................................549

Backing Up the Database ....................................................................................555

Working with the Transaction Log .......................................................................563

Recovery Operations...........................................................................................568

System Databases Recovery ................................................................................579

Performing a Complete Recovery .........................................................................582

Summary ...........................................................................................................583

Chapter 22: Maintaining the Database . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 585

DBCC Commands .................................................................................................586

Managing Database Maintenance .........................................................................604

Summary ...........................................................................................................614

Chapter 23: Transferring Databases . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 615

Copy Database Wizard.........................................................................................616

Working with SQL Script .....................................................................................620

Detaching and Attaching ....................................................................................624

Import and Export Wizard ..................................................................................627

Data-Tier Application (DAC) ................................................................................630

Summary ...........................................................................................................632

Chapter 24: Database Snapshots . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 635

How Do Database Snapshots Work? ......................................................................636

Using Database Snapshots ...................................................................................637

Summary ...........................................................................................................645

Chapter 25: Asynchronous Messaging with Service Broker . . . . . . . . . . . . . . . . . . . . . . . 647

Confi guring a Message Queue ..............................................................................649

Working with Dialogs .........................................................................................651

What’s New in Service Broker for SQL Server 2012? ...............................................655

Monitoring and Troubleshooting Service Broker ...................................................656

Summary ...........................................................................................................659

Chapter 26: Log Shipping . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 661

Availability Testing ............................................................................................662

Warm Standby Availability .................................................................................663

Defi ning Log Shipping .......................................................................................664

Checking Log Shipping Confi guration ..................................................................676

Monitoring Log Shipping ....................................................................................677

Modifying or Removing Log Shipping ..................................................................678

Switching Roles .................................................................................................682

Summary ...........................................................................................................683

Chapter 27: Database Mirroring . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 685

Database Mirroring Overview ..............................................................................686

Defi ning and Confi guring Database Mirroring ......................................................688

Checking Database Mirroring Confi guration .........................................................701

Monitoring Database Mirroring ............................................................................703

Pausing or Removing Database Mirroring .............................................................708

Role Switching ...................................................................................................709

High Availability/AlwaysOn ................................................................................711

Summary ...........................................................................................................725

Chapter 28: Replicating Data . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 727

Moving Data Between Servers .............................................................................727

Replication Concepts ..........................................................................................730

Confi guring Replication ......................................................................................732

Summary ...........................................................................................................744

Chapter 29: Clustering . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 745

What Does Clustering Do? ...................................................................................745

Confi guring Clustering .......................................................................................748

Summary ...........................................................................................................768

Chapter 30: Confi guring and Managing SQL Server with PowerShell . . . . . . . . . . . . . . . . 769

Why Use PowerShell? ..........................................................................................770

Basic PowerShell ................................................................................................770

SQL Server PowerShell Extensions .......................................................................781

Communicating with SQL Server via SMO .............................................................785

Scripting SQL Server Tasks ..................................................................................793

Summary ...........................................................................................................799

Chapter 31: Managing Data in Windows Azure SQL Database . . . . . . . . . . . . . . . . . . . . 801

Overview of Azure SQL Database .........................................................................801

Managing Windows Azure SQL Database ...............................................................802

High Availability and Scalability .........................................................................806

Migrating Data to SQL Database...........................................................................807

Summary ...........................................................................................................829

Part VI: Securing Your SQL Server 831

Chapter 32: Authentication Types in SQL Server . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 833

Windows Authentication ....................................................................................835

SQL Authentication ............................................................................................836

Differences Between SQL and Windows Authentication .........................................837

Kerberos and Windows Authentication Delegation ................................................838

Summary ...........................................................................................................839

Chapter 33: Authorizing Securables . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 841

Permission Chains ..............................................................................................841

Object Ownership ...............................................................................................842

Securables Permissions .......................................................................................843

Object Security ..................................................................................................844

A Sample Security Model ...................................................................................850

Views and Security .............................................................................................851

Summary ...........................................................................................................852

Chapter 34: Data Encryption . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 853

Introducing Data Encryption ..............................................................................853

Summary ...........................................................................................................859

Chapter 35: Row-Level Security . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 861

The Security Table .............................................................................................862

Assigning Permissions ........................................................................................863

Checking Permissions .........................................................................................870

Summary ...........................................................................................................875

Part VII: Monitoring and Auditing 877

Chapter 36: Creating Triggers . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 879

Trigger Basics ....................................................................................................879

Working with the Transaction .............................................................................886

Multiple-Trigger Interaction ................................................................................891

Transaction-Aggregation Handling ......................................................................896

DDL Triggers ......................................................................................................901

Managing DDL Triggers .......................................................................................901

Developing DDL Triggers .....................................................................................904

Summary ...........................................................................................................907

Chapter 37: Performance Monitor and PAL . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 909

Using PerfMon ...................................................................................................909

Summary ...........................................................................................................917

Chapter 38: Using Profi ler and SQL Trace . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 919

Features of SQL Server Profi ler ............................................................................919

Running Profi ler ................................................................................................920

Using SQL Trace .................................................................................................927

Summary ...........................................................................................................929

Chapter 39: Wait States . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 931

The SQL Server OS ..............................................................................................931

Examining Wait Statistics ...................................................................................932

Common Red-Flag Wait Types ..............................................................................935

Other Ways to Gather Wait data ...........................................................................936

Summary ...........................................................................................................936

Chapter 40: Extended Events . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 937

The Extended Events Object Model .......................................................................937

The system_health Session .................................................................................941

The Extended Events Profi ler ..............................................................................942

Summary ...........................................................................................................944

Chapter 41: Data Change Tracking and Capture . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 945

Confi guring Change Tracking ..............................................................................946

Querying Change Tracking ..................................................................................950

Removing Change Tracking .................................................................................953

Change Data Capture ..........................................................................................954

New in SQL Server 2012 ......................................................................................955

Enabling CDC .....................................................................................................955

Working with Change Data Capture ......................................................................957

Removing Change Data Capture ...........................................................................964

Summary ...........................................................................................................965

Chapter 42: SQL Audit . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 967

SQL Audit Technology Overview ..........................................................................967

Creating an Audit...............................................................................................968

Server Audit Specifi cations .................................................................................971

Database Audit Specifi cations .............................................................................973

Viewing the Audit Trail ......................................................................................974

Summary ...........................................................................................................974

Chapter 43: Management Data Warehouse . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 977

Using the Management Data Warehouse ...............................................................977

Confi guring MDW ...............................................................................................978

Setting Up Data Collection ..................................................................................983

Viewing MDW Reports .........................................................................................985

Creating Custom Data Collector Sets ....................................................................989

Summary ...........................................................................................................992

Part VIII: Performance Tuning and Optimization 993

Chapter 44: Interpreting Query Execution Plans . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 995

Viewing Query Execution Plans ...........................................................................996

Understanding Execution Plan Operators ........................................................... 1001

Summary ......................................................................................................... 1002

Chapter 45: Indexing Strategies. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1005

Zen and the Art of Indexing ............................................................................. 1006

Indexing Basics ............................................................................................... 1006

The Path of the Query ...................................................................................... 1014

A Comprehensive Indexing Strategy .................................................................. 1032

Specialty Indexes ............................................................................................. 1035

Summary ......................................................................................................... 1039

Chapter 46: Maximizing Query Plan Reuse . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1041

Query Compiling .............................................................................................. 1041

Query Recompiles .............................................................................................1044

Summary .........................................................................................................1046

Chapter 47: Managing Transactions, Locking, and Blocking . . . . . . . . . . . . . . . . . . . . . 1047

The ACID Properties .........................................................................................1048

Programming Transactions ............................................................................... 1049

Default Locking and Blocking Behavior ............................................................. 1054

Monitoring Locking and Blocking ...................................................................... 1057

Dealing with Deadlocks .................................................................................... 1060

Understanding SQL Server Locking .................................................................... 1066

Transaction Isolation Levels ............................................................................. 1070

Application Locks ............................................................................................ 1087

Application Locking Design ..............................................................................1088

Transaction-Log Architecture ........................................................................... 1089

Transaction Performance Strategies .................................................................. 1093

Summary ......................................................................................................... 1095

Chapter 48: Data Compression . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1097

Understanding Data Compression ...................................................................... 1097

Applying Data Compression ............................................................................... 1103

Summary ......................................................................................................... 1108

Chapter 49: Partitioning . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1109

Partitioning Strategies ..................................................................................... 1109

Partitioned Views ............................................................................................. 1110

Partitioned Tables and Indexes ......................................................................... 1117

Summary ......................................................................................................... 1129

Chapter 50: Resource Governor . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1131

Exploring Fundamentals of Resource Governor ................................................... 1132

Performance Monitoring of Resource Governor ................................................... 1138

Views and Limitations ...................................................................................... 1140

Summary ......................................................................................................... 1140

Part IX: Business Intelligence 1141

Chapter 51: Business Intelligence Database Design . . . . . . . . . . . . . . . . . . . . . . . . . . . 1143

Data Warehousing ............................................................................................ 1144

Designing a Data Warehouse Using a Star Schema ............................................... 1144

Designing your Data Warehouse using a Snowfl ake Schema ................................. 1146

Ensuring Consistency within a Data Warehouse .................................................. 1147

Loading Data ................................................................................................... 1147

Summary ......................................................................................................... 1153

Chapter 52: Building, Deploying, and Managing ETL Workfl ows in Integration

Services . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1155

Exploring the SSIS Environment in Brief ............................................................ 1157

Exploring the SSIS Environment in More Detail .................................................. 1167

Deploying and Executing Projects and Packages ................................................. 1188

Summary ......................................................................................................... 1194

Chapter 53: Building Multidimensional Cubes in Analysis Services with MDX . . . . . . . . 1195

Analysis Services Quick Start ............................................................................ 1195

Analysis Services Architecture .......................................................................... 1196

Building a Database ......................................................................................... 1198

Dimensions ...................................................................................................... 1204

Cubes .............................................................................................................. 1210

Data Storage .................................................................................................... 1219

Cube Processing ............................................................................................... 1219

Summary ......................................................................................................... 1222

Chapter 54: Confi guring and Administering Analysis Services . . . . . . . . . . . . . . . . . . . 1223

Installing Analysis Services .............................................................................. 1223

Confi guring Basic Analysis Services Settings ...................................................... 1227

Advanced SSAS Deployments ............................................................................ 1230

Reviewing Query Performance with SQL Profi ler ................................................. 1230

Summary ......................................................................................................... 1232

Chapter 55: Authoring Reports in Reporting Services . . . . . . . . . . . . . . . . . . . . . . . . . . 1233

Report Authoring Environments ........................................................................ 1234

The Basic Elements of a Report ......................................................................... 1235

Building a Report with the Report Wizard .......................................................... 1237

Authoring a Report from Scratch ....................................................................... 1239

Exploring the Report Designer ......................................................................... 1240

Using Reporting Services Features to Visualize

Your Data .................................................................................................... 1244

Designing the Report Layout ............................................................................ 1251

Building Reports with Report Builder ................................................................ 1255

Summary ......................................................................................................... 1259

Chapter 56: Confi guring and Administering Reporting Services . . . . . . . . . . . . . . . . . . 1261

Installing Reporting Services ............................................................................ 1261

Deploying Reporting Services Reports ............................................................... 1265

Managing Security with Reporting Services ....................................................... 1271

Disaster Recovery ........................................................................................... 1277

Summary ......................................................................................................... 1280

Chapter 57: Data Mining with Analysis Services . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1281

The Data Mining Process .................................................................................. 1282

Modeling with Analysis Services ....................................................................... 1283

Algorithms ...................................................................................................... 1289

Cube Integration .............................................................................................. 1296

Summary ......................................................................................................... 1297

Chapter 58: Creating and Deploying BI Semantic Models . . . . . . . . . . . . . . . . . . . . . . . 1299

What Is a BI Semantic Model? ........................................................................... 1299

The Development Environment .......................................................................... 1301

Creating BI Semantic Models Using PowerPivot ................................................... 1303

Extending a BI Semantic Model with PowerPivot ................................................ 1307

Deploying BI Semantic Models to SharePoint ...................................................... 1317

Managing Automatic Data Refresh of PowerPivot Workbooks in SharePoint 2010 ... 1318

Creating BI Semantic Models Using SQL Server Data Tools.................................... 1319

Extending a BI Semantic Model with SQL Server Data Tools ................................. 1322

Deploying BI Semantic Models to an Analysis Services Instance .......................... 1324

Summary ......................................................................................................... 1326

Chapter 59: Creating and Deploying Power View Reports . . . . . . . . . . . . . . . . . . . . . . . 1327

Power View Requirements ................................................................................. 1327

Creating and Deploying Reports with Power View ............................................... 1328

Deploying Power View Reports ..........................................................................1344

Summary ......................................................................................................... 1345

Index . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1347