Microsoft SQL Server 2014 Unleashed

Ray Rankins is owner and president of Gotham Consulting Services, Inc. (http://www.gothamconsulting.com), near Saratoga Springs, New York. Ray has been working with Sybase and Microsoft SQL Server for more than 27 years and has experience in database administration, database design, project management, application development, consulting, courseware development, and training. He has worked in a variety of industries, including financial, manufacturing, health care, retail, insurance, communications, public utilities, and state and federal government. His expertise is in database performance and tuning, query analysis, advanced SQL programming and stored procedure development, database design, data architecture, and database application design and development, with recent specialization in Sybase to SQL Server migrations. Ray's presentations on these topics at user group conferences have been very well received. Ray is coauthor of Microsoft SQL Server 2012 Unleashed, Microsoft SQL Server 2008 R2 Unleashed, Microsoft SQL Server 2005 Unleashed, Microsoft SQL Server 2000 Unleashed, Microsoft SQL Server 6.5 Unleashed, Sybase SQL Server 11 Unleashed, and Sybase SQL Server 11 DBA Survival Guide, all published by Sams Publishing. As an instructor, Ray brings his real-world experience into the classroom, teaching courses on SQL, advanced SQL programming and optimization, database design, database administration, and database performance and tuning. Ray can be reached at rrankins@gothamconsulting.com.

Paul Bertucci is the founder of Data by Design, LLC (www.dataxdesign.com), a global database consulting firm with offices in the United States and Paris, France. He recently spent 6 years as the Chief Architect and Director of the global Shared Services team for Autodesk, Inc. running BI/DW/ODS, Big Data, Identity Management, SOA, Integration (EAI & ETL), MDM, Collaboration/Social, SaaS application platforms, and Enterprise Architecture teams. Prior to Autodesk, he was the Chief Data Architect at Symantec for 4 years. He is also co-founder and CTO for Diginome, Inc. (www.diginome.com), a data provenance/integrity software company. Paul has more than 30 years of experience with database design, data architecture, big data, data replication, performance and tuning, master data management (MDM), data provenance/DataDNA, distributed data systems, data integration, high-availability, enterprise architecture, identity management, SOA, SaaS, and systems integration for numerous Fortune 500 companies, including Intel, Coca-Cola, Apple, Toshiba, Lockheed, Wells Fargo, Safeway, Sony, Charles Schwab, Cisco Systems, Sybase, Symantec, Veritas, and Honda, to name a few. He has authored numerous database articles, data standards, and high-profile database courses, such as Sybase's "Performance and Tuning" and "Physical Database Design" courses. Other Sams Publishing books that he has authored include the highly popular Microsoft SQL Server 2000 Unleashed, Teach Yourself ADO.NET in 24 Hours, Microsoft SQL Server High Availability, Microsoft SQL Server 2005 Unleashed, Microsoft SQL Server 2008 R2 Unleashed, and Microsoft SQL Server 2012 Unleashed. Mr. Bertucci is a frequent speaker at industry conferences such as Informatica World, Oracle World, and the MDM Summit, and at Microsoft-oriented conferences such as SQL Saturday's, Silicon Valley Code-Camp, PASS conferences, Tech Ed's, and SQL Server User Groups. He has deployed numerous systems with Microsoft SQL Server, Sybase, DB2, Postgres, MySQL, NoSQL, Paraccel, Hadoop and Oracle database engines, and he has designed/architected several commercially available tools in the database, data modeling, performance and tuning, data integration, digital DNA, and multidimensional planning spaces. Paul just finished a major "Master Data Management for Big Data" project with Intel Corporation that deployed MDS (with Profisee's Maestro) in a data mastering as a service architecture. Paul received his formal education in computer science and electrical engineering from UC Berkeley (Go Bears!). He lives in the great Pacific Northwest (Oregon) with his five children: Donny, Juliana, Nina, Marissa, and Paul Jr. Mr. Bertucci can be reached at pbertucci@dataxdesign.com or Bertucci@Alum.CalBerkeley.Org.

Alex T. Silverstein is owner and chief technologist of Unified Digital Group, LLC (http://unifieddigital.com), a custom software development firm headquartered near Saratoga Springs, New York. He specializes in designing high-availability software systems using SQL Server and Microsoft .NET. Alex has more than 18 years of experience providing application development, database administration, and training services worldwide to a variety of industries. He was also a coauthor of previous editions of this book. You can reach Alex anytime via email at alex@unifieddigital.com.

Introduction 1
Who This Book Is For 2
What This Book Covers 2
Conventions Used in This Book 4
Good Luck! 5


Part I Welcome to Microsoft SQL Server


1 SQL Server 2014 Overview 9
SQL Server Components and Features 9
The SQL Server Database Engine 10
SQL Server 2014 Administration and Management Tools 12
Replication 15
Merge Replication 16
SQL Server AlwaysOn Features 17
SQL Server Service Broker 18
Full-Text and Semantic Search 18
SQL Server Integration Services (SSIS) 20
SQL Server Analysis Services (SSAS) 21
SQL Server Reporting Services (SSRS) 23
Master Data Services 23
Data Quality Services 24
SQL Server 2014 Editions 24
SQL Server 2014 Standard Edition 25
SQL Server 2014 Enterprise Edition 26
Differences Between the Enterprise and Standard Editions of SQL Server 26
Other SQL Server 2014 Editions 28
SQL Server Licensing 30
Web Edition Licensing 31
Developer Edition Licensing 32
Express Edition Licensing 32
Choosing a Licensing Model 32
Mixing Licensing Models 32
Licensing SQL Server of High Availability 32
Licensing SQL Server in a Virtual Environment 34
Summary 35
2 What’s New in SQL Server 2014 37
New SQL Server 2014 Features 37
Memory-Optimized Tables/In-Memory OLTP 38
New Cardinality Estimation Logic 38
Delayed Durability for Transactions 38
Buffer Pool Extension 38
SQL Server Data Tools for Business Intelligence 39
SQL Server 2014 Enhancements 39
Resource Governor Enhancements 39
Security Enhancements 39
Backup and Restore Enhancements 40
Indexing Enhancements 40
Monitoring Enhancements 41
SQL Server AlwaysOn and Availability Groups Enhancements 42
New Transact-SQL Enhancements 42
Deprecated and Discontinued Features 42
Summary 45


Part II SQL Server Tools and Utilities


3 SQL Server Management Studio 49
What’s New in SSMS 50
The Integrated Environment 50
Window Management 50
Integrated Help 53
Administration Tools 56
Registered Servers 56
Object Explorer 58
Activity Monitor 60
Log File Viewer 62
SQL Server Utility 64
Development Tools 69
The Query Editor 69
Managing Projects in SSMS 77
Integrating SSMS with Source Control 78
Using SSMS Templates 80
Using SSMS Snippets 84
T-SQL Debugging 85
Multiserver Queries 86
Summary 87
4 SQL Server Command-Line Utilities 89
What’s New in SQL Server Command-Line Utilities 90
The sqlcmd Command-Line Utility 91
Executing the sqlcmd Utility 93
Using Scripting Variables with sqlcmd 95
The dta Command-Line Utility 96
The tablediff Command-Line Utility 99
The bcp Command-Line Utility 102
The sqldiag Command-Line Utility 103
The sqlservr Command-Line Utility 105
The sqlLocalDB Command-Line Utility 106
Summary 108
5 SQL Server Profiler 111
What’s New with SQL Server Profiler 111
SQL Server Profiler Architecture 112
Creating Traces 113
Events 115
Data Columns 117
Filters 120
Executing Traces and Working with Trace Output 122
Saving and Exporting Traces 123
Saving Trace Output to a File 123
Saving Trace Output to a Table 124
Saving the Profiler GUI Output 124
Importing Trace Files 125
Importing a Trace File into a Trace Table 125
Analyzing Trace Output with the Database Engine Tuning Advisor 128
Replaying Trace Data 128
Defining Server-Side Traces 131
Monitoring Running Traces 141
Stopping Server-Side Traces 143
Profiler Usage Scenarios 145
Analyzing Slow Stored Procedures or Queries 145
Deadlocks 146
Identifying Ad Hoc Queries 148
Identifying Performance Bottlenecks 148
Monitoring Auto-Update Statistics 150
Monitoring Application Progress 151
Summary 153
6 SQL Distributed Replay 155
What’s New for Distributed Replay 155
Overview of Distributed Replay 155
Distributed Replay Components 156
Distributed Replay Administrative Tool 157
Distributed Replay Controller 157
Distributed Replay Clients 158
Target Server 158
Configuring Distributed Replay 158
Controller Configuration File 159
Client Configuration File 159
Preprocess Configuration File 160
Replay Configuration File 161
Replay the Trace Data 163
Configure Permissions and Security 163
Capture the Workload 165
Preprocess the Trace File 166
Apply the Workload 167
Summary 169


Part III SQL Server Administration


7 SQL Server System and Database Administration 173
What’s New in SQL Server System and Database Administration 173
System Administrator Responsibilities 174
System Databases 174
The master Database 175
The resource Database 176
The model Database 176
The msdb Database 176
The distribution Database 176
The tempdb Database 177
Maintaining System Databases 177
System Tables 178
System Views 179
Compatibility Views 180
Catalog Views 182
Information Schema Views 184
Dynamic Management Views 186
System Stored Procedures 189
Useful System Stored Procedures 189
Summary 191
8 Installing SQL Server 2014 193
What’s New in Installing SQL Server 2014 193
Installation Requirements 193
Hardware Requirements 194
Software Requirements 195
Installation Walkthrough 198
Install Screens, Step-by-Step 198
Installing SQL Server Documentation 217
Installing SQL Server Using a Configuration File 219
Running an Automated or Manual Install 224
Installing SQL Server Using Sysprep 226
Preparing a SQL Server Sysprep Image 226
Completing a SQL Server Sysprep Image 229
Modifying a SQL Server Sysprep Image 231
Common Uses of SQL Server Sysprep Images 232
Installing Service Packs and Cumulative Updates 233
Applying a Service Pack or Cumulative Update During a New Installation 233
Summary 236
9 Upgrading to SQL Server 2014 237
What’s New in Upgrading SQL Server 237
The SQL Server 2014 Upgrade Matrix 237
Identifying Products and Features to be Upgraded 240
Using the SQL Server Upgrade Advisor (UA) 241
Getting Started with the UA 241
The Analysis Wizard 243
The Report Viewer 249
Destination: SQL Server 2014 250
Side-by-Side Upgrades 251
Upgrading In-Place 259
Upgrading the Database Engine 260
Installing Product Updates (Slipstreaming) During Upgrades 263
Upgrading Using a Configuration File 264
Upgrading from Pre-SQL Server 2005 Versions 266
Upgrading Other SQL Server Components 266
Upgrading Analysis Services 266
Upgrading SQL Server Analysis Services 266
Upgrading Reporting Services 266
Upgrading SSIS Packages 269
Migrating DTS Packages 271
Summary 271
10 Client Installation and Configuration 273
What’s New in Client Installation and Configuration 273
Client/Server Networking Considerations 274
Server Network Protocols 275
The Server Endpoint Layer 277
The Role of SQL Browser 280
Client Installation 281
Installing the Client Tools 281
Installing SNAC 282
Client Configuration 284
Client Configuration Using SSCM 284
Connection Encryption 287
Client Data Access Technologies 289
Provider Choices 290
Connecting Using the Various Providers and Drivers 291
General Networking Considerations and Troubleshooting 296
Summary 299
11 Database Backup and Restore 301
What’s New in Database Backup and Restore 301
Developing a Backup and Restore Plan 302
Types of Backups 303
Full Database Backups 304
Differential Database Backups 304
Partial Backups 305
Differential Partial Backups 305
File and Filegroup Backups 305
Copy-Only Backups 306
Transaction Log Backups 306
Recovery Models 306
Full Recovery 307
Bulk-Logged Recovery 308
Simple Recovery 309
Backup Devices 310
Disk Devices 310
Tape Devices 310
Network Shares 311
Media Sets and Families 311
Creating Backup Devices 311
Backing Up a Database 312
Creating Database Backups with SSMS 312
Creating Database Backups with T-SQL 315
Backing Up the Transaction Log 318
Creating Transaction Log Backups with SSMS 318
Creating Transaction Log Backups with T-SQL 319
Backup Scenarios 320
Full Database Backups Only 320
Full Database Backups with Transaction Log Backups 321
Differential Backups 322
Partial Backups 323
File/Filegroup Backups 325
Mirrored Backups 326
Copy-Only Backups 326
Compressed Backups 327
Encrypted Backups 328
System Database Backups 329
Restoring Databases and Transaction Logs 330
Restores with T-SQL 330
Restoring by Using SSMS 334
Restore Information 339
Restore Scenarios 342
Restoring to a Different Database 342
Restoring a Snapshot 344
Restoring a Transaction Log 344
Restoring to the Point of Failure 345
Restoring to a Point in Time 347
Online Restores 349
Restoring the System Databases 349
Additional Backup Considerations 351
Frequency of Backups 352
Using a Standby Server 352
Snapshot Backups 353
Considerations for Very Large Databases 354
Maintenance Plans 354
Summary 355
12 Database Mail 357
What’s New in Database Mail 357
Setting Up Database Mail 358
Creating Mail Profiles and Accounts 359
Using T-SQL to Update and Delete Mail Objects 362
Setting System-Wide Mail Settings 363
Testing Your Setup 364
Sending and Receiving with Database Mail 364
The Service Broker Architecture 364
Sending Email 365
Receiving Email 371
Using SQL Server Agent Mail 371
Job Mail Notifications 371
Creating an Operator 371
Enabling SQL Agent Mail 371
Creating the Job 372
Testing the Job-Completion Notification 373
Alert Mail Notifications 373
Creating an Alert 373
Testing the Alert Notification 374
Related Views and Procedures 375
Viewing the Mail Configuration Objects 375
Viewing Mail Message Data 376
Summary 377
13 SQL Server Agent 379
What’s New in Scheduling and Notification 380
Configuring the SQL Server Agent 380
Configuring SQL Server Agent Properties 380
Configuring the SQL Server Agent Startup Account 382
Configuring Email Notification 384
SQL Server Agent Proxy Account 385
Viewing the SQL Server Agent Error Log 387
SQL Server Agent Security 388
Managing Operators 389
Managing Jobs 391
Defining Job Properties 391
Defining Job Steps 392
Defining Multiple Job Steps 394
Defining Job Schedules 395
Defining Job Notifications 397
Viewing Job History 398
Managing Alerts 399
Defining Alert Properties 399
Defining Alert Responses 402
Scripting Jobs and Alerts 404
Multiserver Job Management 405
Creating a Master Server 406
Enlisting Target Servers 407
Creating Multiserver Jobs 407
Event Forwarding 407
Summary 408
14 SQL Server Policy-Based Management 409
What’s New in Policy-Based Management 409
Introduction to Policy-Based Management 410
Policy-Based Management Concepts 411
Facets 411
Conditions 414
Policies 415
Categories 415
Targets 415
Execution Modes 415
Central Management Servers 416
Implementing Policy-Based Management 418
Creating a Condition Based on a Facet 418
Creating a Policy 420
Creating a Category 422
Evaluating Policies 424
Importing and Exporting Policies 425
Sample Templates and Real-World Examples 426
Sample Policy Templates 426
Evaluating Recovery Models 427
Ensuring Object Naming Conventions 427
Checking Best Practices Compliance 427
Policy-Based Management Best Practices 427
Summary 428
15 Security and User Administration 429
What’s New in Security and User Administration 429
An Overview of SQL Server Security 430
Authentication Methods 433
Windows Authentication Mode 433
Mixed Authentication Mode 433
Setting the Authentication Mode 433
Managing Principals 434
Logins 434
SQL Server Security: Users 437
The dbo User 438
The guest User 439
The INFORMATION_SCHEMA User 439
The sys User 439
User/Schema Separation 440
Roles 441
Fixed Server Roles 442
Fixed Database Roles 443
The public Role 445
User-Defined Database Roles 446
User-Defined Server Roles 448
Application Roles 448
Managing Securables 449
Managing Permissions 450
Managing SQL Server Logins 452
Using SSMS to Manage Logins 452
Using T-SQL to Manage Logins 456
Managing SQL Server Users 457
Using SSMS to Manage Users 458
Using T-SQL to Manage Users 460
Managing Database Roles 461
Using SSMS to Manage Database Roles 461
Using T-SQL to Manage Database Roles 462
Managing Server Roles 462
Using SSMS to Manage Server Roles 463
Using T-SQL to Manage Server Roles 463
Managing SQL Server Permissions 464
Using SSMS to Manage Permissions 464
Using SSMS to Manage Permissions at the Server Level 465
Using SSMS to Manage Permissions at the Database Level 467
Using SSMS to Manage Permissions at the Object Level 470
Using T-SQL to Manage Permissions 472
The Execution Context 473
Explicit Context Switching 473
Implicit Context Switching 474
Summary 475
16 Data Encryption 477
What’s New in Data Encryption 478
An Overview of Data Encryption 478
SQL Server Key Management 480
Extensible Key Management 482
Column-Level Encryption 483
Encrypting Columns Using a Passphrase 484
Encrypting Columns Using a Certificate 486
Transparent Data Encryption 490
Implementing Transparent Data Encryption 491
Managing TDE in SSMS 493
Backing Up TDE Certificates and Keys 495
The Limitations of TDE 496
Column-Level Encryption Versus Transparent Data Encryption 496
Summary 498
17 Managing Linked Servers 499
What’s New in Managing Linked Servers 500
Linked Servers 500
Distributed Queries 501
Distributed Transactions 502
Adding, Dropping, and Configuring Linked Servers 503
sp_addlinkedserver 503
sp_linkedservers 510
sp_dropserver 512
sp_serveroption 512
Mapping Local Logins to Logins on Linked Servers 513
sp_addlinkedsrvlogin 514
sp_droplinkedsrvlogin 515
sp_helplinkedsrvlogin 516
Obtaining General Information About Linked Servers 517
Executing a Stored Procedure via a Linked Server 518
Setting Up Linked Servers Using SQL Server Management Studio 519
Summary 523
18 SQL Server Configuration Options 525
What’s New in Configuring, Tuning, and Optimizing SQL Server Options 525
SQL Server Instance Architecture 526
Configuration Options 527
Fixing an Incorrect Option Setting 535
Setting Configuration Options with SSMS 535
Obsolete Configuration Options 535
Configuration Options and Performance 536
access check cache bucket count 536
access check cache quota 536
ad hoc distributed queries 537
affinity I/O mask 537
affinity mask 539
Agent XP 540
backup checksum default 541
backup compression default 541
blocked process threshold 542
c2 audit mode 542
clr enabled 543
common criteria compliance enabled 543
contained database authentication 543
cost threshold for parallelism 544
cross db ownership chaining 545
cursor threshold 545
Database Mail XPs 546
default full-text language 546
default language 548
default trace enabled 550
disallow results from triggers 551
EKM provider enabled 551
filestream_access_level 551
fill factor 552
index create memory 552
in-doubt xact resolution 553
lightweight pooling 553
locks 554
max degree of parallelism 554
max server memory and min server memory 554
max text repl size 556
max worker threads 557
media retention 558
min memory per query 558
nested triggers 559
network packet size 559
Ole Automation Procedures 560
optimize for ad hoc workloads 560
PH_timeout 561
priority boost 561
query governor cost limit 562
query wait 562
recovery interval 563
remote access 564
remote admin connections 564
remote login timeout 564
remote proc trans 565
remote query timeout 565
scan for startup procs 565
show advanced options 566
user connections 566
user options 567
XP-Related Configuration Options 568
Summary 569
19 Working with and Deploying to Azure SQL Database 571
Setting Up Subscriptions, Servers, and Databases 571
Setting Up Your Windows Azure Subscription 572
Creating a Logical Database Server 574
Managing Your Server 576
Configuring Your Firewall 577
Using SQL Server Management Studio 578
Using Management Portal 579
Working with Databases 580
Understanding SQL Database Service Tiers 580
Managing Databases Using T-SQL 584
Migrating Data into SQL Database 586
Copying Databases 587
Exporting Databases 588
Backing Up and Restoring Databases 590
Using SQL Database Backup, Replication, and Recovery 590
Using Database Copies for Backup and Restore 592
Using BACPAC Files for Backup and Restore 593
Managing Logins, Users, and Roles 595
Understanding Roles 595
Managing Logins and Users 596
Considerations for SQL Database Client Applications 598
Connectivity Limitations 598
Connection String Differences 599
Understanding SQL Database Billing 599
Baseline Billing 599
Tracking Your Usage 601
Understanding SQL Database Limitations 603
Unsupported and Partially Supported Functionality 603
References 606
Summary 606


Part IV Database Administration


20 Creating and Managing Databases 609
What’s New in Creating and Managing Databases 610
Data Storage in SQL Server 610
Database Files 611
Primary Files 612
Secondary Files 612
Using Filegroups 613
Using Partitions 616
Transaction Log Files 616
Creating Databases 617
Using SSMS to Create a Database 618
Using T-SQL to Create Databases 621
Setting Database Options 622
The Database Options 623
Using T-SQL to Set Database Options 625
Retrieving Option Information 626
Managing Databases 629
Managing File Growth 629
Expanding Databases 630
Shrinking Databases 631
Moving Databases 636
Restoring a Database to a New Location 636
Using ALTER DATABASE 636
Detaching and Attaching Databases 637
Contained Databases 639
Creating a Contained Database 640
Connecting to a Contained Database 642
Summary 643
21 Creating and Managing Tables 645
What’s New in SQL Server 2014 645
Creating Tables 646
Using Object Explorer to Create Tables 646
Using Database Diagrams to Create Tables 647
Using T-SQL to Create Tables 648
Defining Columns 650
Data Types 651
Column Properties 657
Column Sets 663
Working with Sparse Columns 664
Sparse Columns: Good or Bad? 667
Defining Sparse Columns in SSMS 667
Defining Table Location 668
Defining Table Constraints 670
Modifying Tables 672
Using T-SQL to Modify Tables 672
Using Object Explorer and the Table Designer to Modify Tables 675
Using Database Diagrams to Modify Tables 678
Dropping Tables 680
Using Partitioned Tables 681
Creating a Partition Function 682
Creating a Partition Scheme 684
Creating a Partitioned Table 686
Adding and Dropping Table Partitions 689
Switching Table Partitions 693
Using FILESTREAM Storage 697
Enabling FILESTREAM Storage 698
Setting Up a Database for FILESTREAM Storage 701
Using FILESTREAM Storage for Data Columns 702
Using FileTables 705
FileTable Prerequisites 705
Creating FileTables 707
Copying Files to the FileTable 707
Creating Temporary Tables 709
Summary 710
22 Creating and Managing Indexes 711
What’s New in Creating and Managing Indexes 711
Types of Indexes 712
Clustered Indexes 712
Nonclustered Indexes 714
Creating Indexes 716
Creating Indexes with T-SQL 716
Creating Indexes with SSMS 720
Managing Indexes 722
Managing Indexes with T-SQL 723
Managing Indexes with SSMS 726
Dropping Indexes 727
Online Indexing Operations 727
Indexes on Views 729
Summary 730
23 Implementing Data Integrity 731
What’s New in Data Integrity 731
Types of Data Integrity 732
Domain Integrity 732
Entity Integrity 732
Referential Integrity 732
Enforcing Data Integrity 732
Implementing Declarative Data Integrity 732
Implementing Procedural Data Integrity 733
Using Constraints 733
The PRIMARY KEY Constraint 733
The UNIQUE Constraint 735
The FOREIGN KEY Referential Integrity Constraint 736
The CHECK Constraint 740
Creating Constraints 742
Managing Constraints 747
Rules 750
Defaults 751
Declarative Defaults 751
Bound Defaults 753
When a Default Is Applied 754
Restrictions on Defaults 755
Summary 756
24 Creating and Managing Views 757
What’s New in Creating and Managing Views 757
Definition of Views 757
Using Views 758
Simplifying Data Manipulation 759
Focusing on Specific Data 760
Abstracting Data 761
Controlling Access to Data 762
Creating Views 764
Creating Views Using T-SQL 765
ENCRYPTION 767
Creating Views Using the View Designer 769
Managing Views 772
Altering Views with T-SQL 772
Dropping Views with T-SQL 773
Managing Views with SSMS 773
Data Modifications and Views 773
Partitioned Views 774
Modifying Data Through a Partitioned View 778
Distributed Partitioned Views 779
Indexed Views 780
Creating Indexed Views 781
Indexed Views and Performance 783
To Expand or Not to Expand 786
Summary 787
25 Creating and Managing Stored Procedures 789
What’s New in Creating and Managing Stored Procedures 789
Advantages of Stored Procedures 789
Creating Stored Procedures 791
Creating Procedures in SSMS 792
Executing Stored Procedures 799
Executing Procedures in SSMS 800
Execution Context and the EXECUTE AS Clause 802
Using the WITH RESULT SETS Clause 804
Deferred Name Resolution 807
Identifying the Objects Referenced Within Stored Procedures 809
Viewing Stored Procedures 811
Modifying Stored Procedures 814
Viewing and Modifying Stored Procedures with SSMS 815
Using Input Parameters 816
Setting Default Values for Parameters 817
Passing Object Names as Parameters 820
Using Wildcards in Parameters 822
Using Table-Valued Parameters 823
Using Output Parameters 825
Returning Procedure Status 826
Debugging Stored Procedures Using SQL Server Management Studio 827
Startup Procedures 830
Natively Compiled Stored Procedures 834
T-SQL Stored Procedure Coding Guidelines 838
Summary 839
26 Creating and Managing User-Defined Functions 841
Why Use User-Defined Functions? 841
Types of User-Defined Functions 844
Scalar Functions 844
Table-Valued Functions 847
Creating and Managing User-Defined Functions 849
Creating User-Defined Functions 849
Viewing and Modifying User-Defined Functions 860
Managing User-Defined Function Permissions 868
Rewriting Stored Procedures as Functions 869
Summary 871
27 Creating and Managing Triggers 873
What’s New in Creating and Managing Triggers 874
Using DML Triggers 874
Creating DML Triggers 875
Using AFTER Triggers 877
Using inserted and deleted Tables 881
INSTEAD OF Triggers 885
Using DDL Triggers 893
Creating DDL Triggers 897
Managing DDL Triggers 901
Using Nested Triggers 903
Using Recursive Triggers 903
Summary 905
28 Transaction Management and the Transaction Log 907
What’s New in Transaction Management 907
What Is a Transaction? 907
How SQL Server Manages Transactions 908
Defining Transactions 909
AutoCommit Transactions 909
Explicit User-Defined Transactions 910
Implicit Transactions 916
Implicit Transactions Versus Explicit Transactions 918
Transactions and T-SQL Batches 919
Transactions and Stored Procedures 921
Transactions and Triggers 926
Triggers and Transaction Nesting 927
Triggers and Multistatement Transactions 930
Using Savepoints in Triggers 931
Transactions and Locking 933
READ_COMMITTED_SNAPSHOT Isolation 934
Coding Effective Transactions 934
Transaction Logging and the Recovery Process 935
The Checkpoint Process 939
Automatic Checkpoints 941
Indirect Checkpoints 942
Manual Checkpoints 944
The Recovery Process 945
Managing the Transaction Log 947
Long-Running Transactions 952
Distributed Transactions 954
Summary 955
29 Database Snapshots 957
What’s New with Database Snapshots 958
What Are Database Snapshots? 958
Limitations and Restrictions of Database Snapshots 962
Copy-on-Write Technology 964
When to Use Database Snapshots 965
Reverting to a Snapshot for Recovery Purposes 965
Safeguarding a Database Prior to Making Mass Changes 966
Providing a Testing (or Quality Assurance) Starting Point (Baseline) 967
Providing a Point-in-Time Reporting Database 967
Providing a Highly Available and Offloaded Reporting Database from a Database Mirror 968
Setup and Breakdown of a Database Snapshot 970
Creating a Database Snapshot 970
Removing a Database Snapshot 974
Reverting to a Database Snapshot for Recovery 975
Reverting a Source Database from a Database Snapshot 975
Database Snapshots Maintenance and Security Considerations 977
Security for Database Snapshots 977
Snapshot Sparse File Size Management 977
Number of Database Snapshots per Source Database 977
Summary 978
30 Database Maintenance 979
What’s New in Database Maintenance 980
The Maintenance Plan Wizard 980
Backing Up Databases 983
Checking Database Integrity 987
Shrinking Databases 988
Maintaining Indexes and Statistics 990
Scheduling a Maintenance Plan 993
Managing Maintenance Plans Without the Wizard 997
Executing a Maintenance Plan 1001
Maintenance Without a Maintenance Plan 1002
Database Maintenance Policies 1003
Summary 1003


Part V SQL Server Performance and Optimization


31 Understanding SQL Server Data Structures 1007
What’s New for Data Structures 1007
Understanding Data Structures 1008
Database Files and Filegroups 1008
Primary Data File 1010
Secondary Data Files 1010
The Log File 1011
File Management 1011
Using Filegroups 1012
FILESTREAM Filegroups 1015
Database Pages 1017
Page Types 1017
Data Pages 1018
Row-Overflow Pages 1024
LOB Data Pages 1025
Index Pages 1028
Space Allocation Structures 1029
Extents 1029
Global and Shared Global Allocation Map Pages 1030
Page Free Space Pages 1031
Index Allocation Map Pages 1031
Differential Changed Map Pages 1032
Bulk Changed Map Pages 1032
Data Compression 1033
Row-Level Compression 1033
Page-Level Compression 1035
The CI Record 1038
Implementing Page Compression 1038
Evaluating Page Compression 1039
Managing Data Compression with SSMS 1042
Understanding Table Structures 1043
Heap Tables 1045
Clustered Tables 1047
Understanding Index Structures 1048
Clustered Indexes 1049
Nonclustered Indexes 1052
Columnstore Indexes 1057
Data Modification and Performance 1062
Inserting Data 1062
Deleting Rows 1065
Updating Rows 1066
Summary 1068
32 Indexes and Performance 1069
What’s New for Indexes and Performance 1069
Index Utilization 1070
Index Selection 1072
Evaluating Index Usefulness 1073
Index Statistics 1076
The Statistics Histogram 1078
How the Statistics Histogram Is Used 1080
Index Densities 1081
Estimating Rows Using Index Statistics 1082
Generating and Maintaining Index and Column Statistics 1085
SQL Server Index Maintenance 1093
Setting the Fill Factor 1103
Reapplying the Fill Factor 1105
Disabling Indexes 1106
Managing Indexes with SSMS 1107
Index Design Guidelines 1108
Clustered Index Indications 1109
Nonclustered Index Indications 1111
Index Covering 1112
Included Columns 1114
Wide Indexes Versus Multiple Indexes 1115
Indexed Views 1116
Indexes on Computed Columns 1117
Filtered Indexes and Statistics 1119
Creating and Using Filtered Indexes 1120
Creating and Using Filtered Statistics 1122
Choosing Indexes: Query versus Update Performance 1124
Identifying Missing Indexes 1125
The Database Engine Tuning Advisor 1125
Missing Index Dynamic Management Objects 1126
Missing Index Feature Versus Database Engine Tuning Advisor 1128
Identifying Unused Indexes 1129
Summary 1131
33 In-Memory Optimization and the Buffer Pool Extension 1133
Overview of In-Memory OLTP 1134
In-Memory OLTP Concepts and Terminology 1136
In-Memory Optimization Requirements 1137
Limitations of In-Memory OLTP 1137
Using In-Memory OLTP 1138
Enabling a Database for In-Memory OLTP 1138
Creating Memory-Optimized Tables 1140
Memory-Optimized Tables Row Structure 1142
Indexes on Memory-Optimized Tables 1143
Garbage Collection 1151
Maintaining Statistics on Memory-Optimized Tables 1153
Memory-Optimized Index Design Guidelines 1154
Using Memory-Optimized Tables 1156
Interpreted T-SQL Support for In-Memory OLTP 1156
Native Compilation 1157
Natively Compiled Stored Procedures 1159
Memory-Optimized Table Variables 1162
Transactions and Memory-Optimized Tables 1162
Monitoring Transactions on Memory-Optimized Tables 1170
Logging, Checkpoint, and Recovery for In-Memory OLTP 1170
Transaction Logging 1171
Checkpoint 1171
Recovery 1174
Managing Memory for In-Memory OLTP 1175
Monitoring Memory Usage 1176
Managing Memory with the Resource Governor 1177
Backup and Recovery of Memory-Optimized Databases 1178
Migrating to In-Memory OLTP 1179
Using the AMR Tool 1180
Using the Table Memory Optimization Advisor to Migrate Disk-Based Tables 1181
Dynamic Management Views for In-Memory OLTP 1183
The Buffer Pool Extension 1185
Summary 1186
34 Understanding Query Optimization 1187
What’s New in Query Optimization 1188
What Is the Query Optimizer? 1188
Query Compilation and Optimization 1189
Compiling DML Statements 1189
Optimization Steps 1190
Query Analysis 1191
Identifying Search Arguments 1191
Identifying OR Clauses 1191
Identifying Join Clauses 1192
Row Estimation and Index Selection 1193
Evaluating SARG and Join Selectivity 1193
Estimating Access Path Cost 1199
Using Multiple Indexes 1206
Optimizing with Indexed Views 1213
Optimizing with Filtered Indexes 1216
Evaluating Cardinality Estimates 1218
Join Selection 1219
Join Processing Strategies 1219
Determining the Optimal Join Order 1224
Subquery Processing 1226
Execution Plan Selection 1228
Query Plan Caching 1231
Query Plan Reuse 1231
Query Plan Aging 1234
Recompiling Query Plans 1234
Monitoring the Plan Cache 1235
Other Query Processing Strategies 1243
Predicate Transitivity 1244
GROUP BY Optimization 1244
Queries with DISTINCT 1245
Queries with UNION 1245