Microsoft SQL Server 2014 Unleashed: Micro SQL Serve 2014 Unlea

by Ray Rankins, Paul Bertucci, Chris Gallelli, Alex T. Silverstein

Overview

The industry’s most complete, useful, and up-to-date guide to SQL Server 2014.

You’ll find start-to-finish coverage of SQL Server’s core database server and management capabilities: all the real-world information, tips, guidelines, and examples you’ll need to install, monitor, maintain, and optimize the
most complex database environments. The provided examples and sample code provide plenty of hands-on opportunities to learn more about SQL Server and create your own viable solutions.

Four leading SQL Server experts present deep practical insights for administering SQL Server, analyzing and optimizing queries, implementing data warehouses, ensuring high availability, tuning performance, and much more. You will benefit from their behind-the-scenes look into SQL Server, showing what goes on behind the various wizards and GUI-based tools. You’ll learn how to use the underlying SQL commands to fully unlock the power and capabilities of SQL Server.

Writing for all intermediate-to-advanced-level SQL Server professionals, the authors draw on immense production experience with SQL Server. Throughout, they focus on successfully applying SQL Server 2014’s most powerful capabilities and its newest tools and features.

Detailed information on how to…

• Understand SQL Server 2014’s new features and each edition’s capabilities and licensing
• Install, upgrade to, and configure SQL Server 2014 for better performance and easier management
• Streamline and automate key administration tasks with Smart Admin
• Leverage powerful new backup/restore options: flexible backup to URL, Managed Backup to Windows Azure, and encrypted backups
• Strengthen security with new features for enforcing “least privilege”
• Improve performance with updateable columnstore indexes, Delayed Durability, and other enhancements
• Execute queries and business logic more efficiently with memoryoptimized tables, buffer pool extension, and natively-compiled stored procedures
• Control workloads and Disk I/O with the Resource Governor
• Deploy AlwaysOn Availability Groups and Failover Cluster Instances to achieve enterprise-class availability and disaster recovery
• Apply new Business Intelligence improvements in Master Data Services, data quality, and Parallel Data Warehouse

Product Details

ISBN-13:	9780134084459
Publisher:	Pearson Education
Publication date:	05/15/2015
Series:	Unleashed
Sold by:	Barnes & Noble
Format:	eBook
Pages:	1992
File size:	175 MB
Note:	This product may take a few minutes to download.

About the Author

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 [e-mail].

 

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 [e-mail] or [e-mail].

 

Chris Gallelli is the president of CGAL Consulting Services, Inc. His company focuses on consulting services in the areas of database administration, database tuning, advanced stored procedure development, Integration Services, Powershell development and database programming using Microsoft Visual Studio. Chris has more than 20 years of experience with SQL Server or Sybase and more than 25 years in the field of information technology. He has a bachelor’s degree in electrical engineering and a master’s degree in business administration from Union College. Chris currently lives near Albany, New York, with his lovely wife, Laura, and two beautiful daughters, Rachael and Kayla. Other Sams Publishing books that he has coauthored include Microsoft SQL Server 2000 Unleashed, Microsoft SQL Server 2005 Unleashed, Microsoft SQL Server 2008 R2, and Microsoft SQL Server 2012 Unleashed. Chris can be reached at [e-mail].

 

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 [e-mail].

Table of Contents

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