Microsoft SQL Server 2014 Unleashed

Microsoft SQL Server 2014 Unleashed

Released June 2015
Publisher(s): Sams
ISBN: 9780134084473

Read it now on the O’Reilly learning platform with a 10-day free trial.

O’Reilly members get unlimited access to books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.

Start your free trial

Book description

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

    • Table of contents

    1. About This eBook
    2. Title Page
    3. Copyright Page
    4. Contents at a Glance
    5. Table of Contents
    6. About the Authors
    7. Dedications
    8. Acknowledgments
    9. We Want to Hear from You!
    10. Reader Services
    11. Introduction
      1. Who This Book Is For
      2. What This Book Covers
      3. Conventions Used in This Book
      4. Good Luck!
    12. Part I: Welcome to Microsoft SQL Server
      1. Chapter 1. SQL Server 2014 Overview
        1. SQL Server Components and Features
          1. The SQL Server Database Engine
          2. SQL Server 2014 Administration and Management Tools
          3. Replication
          4. Merge Replication
          5. SQL Server AlwaysOn Features
          6. SQL Server Service Broker
          7. Full-Text and Semantic Search
          8. SQL Server Integration Services (SSIS)
          9. SQL Server Analysis Services (SSAS)
          10. SQL Server Reporting Services (SSRS)
          11. Master Data Services
          12. Data Quality Services
        2. SQL Server 2014 Editions
          1. SQL Server 2014 Standard Edition
          2. SQL Server 2014 Enterprise Edition
          3. Differences Between the Enterprise and Standard Editions of SQL Server
          4. Other SQL Server 2014 Editions
        3. SQL Server Licensing
          1. Web Edition Licensing
          2. Developer Edition Licensing
          3. Express Edition Licensing
          4. Choosing a Licensing Model
          5. Mixing Licensing Models
          6. Licensing SQL Server of High Availability
          7. Licensing SQL Server in a Virtual Environment
        4. Summary
      2. Chapter 2. What’s New in SQL Server 2014
        1. New SQL Server 2014 Features
          1. Memory-Optimized Tables/In-Memory OLTP
          2. New Cardinality Estimation Logic
          3. Delayed Durability for Transactions
          4. Buffer Pool Extension
          5. SQL Server Data Tools for Business Intelligence
        2. SQL Server 2014 Enhancements
          1. Resource Governor Enhancements
          2. Security Enhancements
          3. Backup and Restore Enhancements
          4. Indexing Enhancements
          5. Monitoring Enhancements
          6. SQL Server AlwaysOn and Availability Groups Enhancements
          7. New Transact-SQL Enhancements
        3. Deprecated and Discontinued Features
        4. Summary
    13. Part II: SQL Server Tools and Utilities
      1. Chapter 3. SQL Server Management Studio
        1. What’s New in SSMS
        2. The Integrated Environment
          1. Window Management
          2. Integrated Help
        3. Administration Tools
          1. Registered Servers
          2. Object Explorer
          3. Activity Monitor
          4. Log File Viewer
          5. SQL Server Utility
        4. Development Tools
          1. The Query Editor
          2. Managing Projects in SSMS
          3. Integrating SSMS with Source Control
          4. Using SSMS Templates
          5. Using SSMS Snippets
          6. T-SQL Debugging
          7. Multiserver Queries
        5. Summary
      2. Chapter 4. SQL Server Command-Line Utilities
        1. What’s New in SQL Server Command-Line Utilities
        2. The sqlcmd Command-Line Utility
          1. Executing the sqlcmd Utility
          2. Using Scripting Variables with sqlcmd
        3. The dta Command-Line Utility
        4. The tablediff Command-Line Utility
        5. The bcp Command-Line Utility
        6. The sqldiag Command-Line Utility
        7. The sqlservr Command-Line Utility
        8. The sqlLocalDB Command-Line Utility
        9. Summary
      3. Chapter 5. SQL Server Profiler
        1. What’s New with SQL Server Profiler
        2. SQL Server Profiler Architecture
        3. Creating Traces
          1. Events
          2. Data Columns
          3. Filters
        4. Executing Traces and Working with Trace Output
        5. Saving and Exporting Traces
          1. Saving Trace Output to a File
          2. Saving Trace Output to a Table
          3. Saving the Profiler GUI Output
          4. Importing Trace Files
          5. Importing a Trace File into a Trace Table
          6. Analyzing Trace Output with the Database Engine Tuning Advisor
        6. Replaying Trace Data
        7. Defining Server-Side Traces
          1. Monitoring Running Traces
          2. Stopping Server-Side Traces
        8. Profiler Usage Scenarios
          1. Analyzing Slow Stored Procedures or Queries
          2. Deadlocks
          3. Identifying Ad Hoc Queries
          4. Identifying Performance Bottlenecks
          5. Monitoring Auto-Update Statistics
          6. Monitoring Application Progress
        9. Summary
      4. Chapter 6. SQL Distributed Replay
        1. What’s New for Distributed Replay
        2. Overview of Distributed Replay
        3. Distributed Replay Components
          1. Distributed Replay Administrative Tool
          2. Distributed Replay Controller
          3. Distributed Replay Clients
          4. Target Server
        4. Configuring Distributed Replay
          1. Controller Configuration File
          2. Client Configuration File
          3. Preprocess Configuration File
          4. Replay Configuration File
        5. Replay the Trace Data
          1. Configure Permissions and Security
          2. Capture the Workload
          3. Preprocess the Trace File
          4. Apply the Workload
        6. Summary
    14. Part III: SQL Server Administration
      1. Chapter 7. SQL Server System and Database Administration
        1. What’s New in SQL Server System and Database Administration
        2. System Administrator Responsibilities
        3. System Databases
          1. The master Database
          2. The resource Database
          3. The model Database
          4. The msdb Database
          5. The distribution Database
          6. The tempdb Database
          7. Maintaining System Databases
        4. System Tables
        5. System Views
          1. Compatibility Views
          2. Catalog Views
          3. Information Schema Views
          4. Dynamic Management Views
        6. System Stored Procedures
          1. Useful System Stored Procedures
        7. Summary
      2. Chapter 8. Installing SQL Server 2014
        1. What’s New in Installing SQL Server 2014
          1. Installation Requirements
          2. Hardware Requirements
          3. Software Requirements
        2. Installation Walkthrough
          1. Install Screens, Step-by-Step
          2. Installing SQL Server Documentation
        3. Installing SQL Server Using a Configuration File
          1. Running an Automated or Manual Install
        4. Installing SQL Server Using Sysprep
          1. Preparing a SQL Server Sysprep Image
          2. Completing a SQL Server Sysprep Image
          3. Modifying a SQL Server Sysprep Image
          4. Common Uses of SQL Server Sysprep Images
        5. Installing Service Packs and Cumulative Updates
          1. Applying a Service Pack or Cumulative Update During a New Installation
        6. Summary
      3. Chapter 9. Upgrading to SQL Server 2014
        1. What’s New in Upgrading SQL Server
          1. The SQL Server 2014 Upgrade Matrix
        2. Identifying Products and Features to be Upgraded
        3. Using the SQL Server Upgrade Advisor (UA)
          1. Getting Started with the UA
          2. The Analysis Wizard
          3. The Report Viewer
        4. Destination: SQL Server 2014
          1. Side-by-Side Upgrades
          2. Upgrading In-Place
          3. Upgrading the Database Engine
        5. Installing Product Updates (Slipstreaming) During Upgrades
        6. Upgrading Using a Configuration File
          1. Upgrading from Pre-SQL Server 2005 Versions
        7. Upgrading Other SQL Server Components
          1. Upgrading Analysis Services
          2. Upgrading SQL Server Analysis Services
          3. Upgrading Reporting Services
          4. Upgrading SSIS Packages
          5. Migrating DTS Packages
        8. Summary
      4. Chapter 10. Client Installation and Configuration
        1. What’s New in Client Installation and Configuration
          1. Client/Server Networking Considerations
          2. Server Network Protocols
          3. The Server Endpoint Layer
          4. The Role of SQL Browser
        2. Client Installation
          1. Installing the Client Tools
          2. Installing SNAC
        3. Client Configuration
          1. Client Configuration Using SSCM
          2. Connection Encryption
        4. Client Data Access Technologies
          1. Provider Choices
          2. Connecting Using the Various Providers and Drivers
          3. General Networking Considerations and Troubleshooting
        5. Summary
      5. Chapter 11. Database Backup and Restore
        1. What’s New in Database Backup and Restore
        2. Developing a Backup and Restore Plan
        3. Types of Backups
          1. Full Database Backups
          2. Differential Database Backups
          3. Partial Backups
          4. Differential Partial Backups
          5. File and Filegroup Backups
          6. Copy-Only Backups
          7. Transaction Log Backups
        4. Recovery Models
          1. Full Recovery
          2. Bulk-Logged Recovery
          3. Simple Recovery
        5. Backup Devices
          1. Disk Devices
          2. Tape Devices
          3. Network Shares
          4. Media Sets and Families
          5. Creating Backup Devices
        6. Backing Up a Database
          1. Creating Database Backups with SSMS
          2. Creating Database Backups with T-SQL
        7. Backing Up the Transaction Log
          1. Creating Transaction Log Backups with SSMS
          2. Creating Transaction Log Backups with T-SQL
        8. Backup Scenarios
          1. Full Database Backups Only
          2. Full Database Backups with Transaction Log Backups
          3. Differential Backups
          4. Partial Backups
          5. File/Filegroup Backups
          6. Mirrored Backups
          7. Copy-Only Backups
          8. Compressed Backups
          9. Encrypted Backups
          10. System Database Backups
        9. Restoring Databases and Transaction Logs
          1. Restores with T-SQL
          2. Restoring by Using SSMS
          3. Restore Information
        10. Restore Scenarios
          1. Restoring to a Different Database
          2. Restoring a Snapshot
          3. Restoring a Transaction Log
          4. Restoring to the Point of Failure
          5. Restoring to a Point in Time
          6. Online Restores
          7. Restoring the System Databases
        11. Additional Backup Considerations
          1. Frequency of Backups
          2. Using a Standby Server
          3. Snapshot Backups
          4. Considerations for Very Large Databases
          5. Maintenance Plans
        12. Summary
      6. Chapter 12. Database Mail
        1. What’s New in Database Mail
        2. Setting Up Database Mail
          1. Creating Mail Profiles and Accounts
          2. Using T-SQL to Update and Delete Mail Objects
          3. Setting System-Wide Mail Settings
          4. Testing Your Setup
        3. Sending and Receiving with Database Mail
          1. The Service Broker Architecture
          2. Sending Email
          3. Receiving Email
        4. Using SQL Server Agent Mail
          1. Job Mail Notifications
          2. Creating an Operator
          3. Enabling SQL Agent Mail
          4. Creating the Job
          5. Testing the Job-Completion Notification
          6. Alert Mail Notifications
          7. Creating an Alert
          8. Testing the Alert Notification
        5. Related Views and Procedures
          1. Viewing the Mail Configuration Objects
          2. Viewing Mail Message Data
        6. Summary
      7. Chapter 13. SQL Server Agent
        1. What’s New in Scheduling and Notification
        2. Configuring the SQL Server Agent
          1. Configuring SQL Server Agent Properties
          2. Configuring the SQL Server Agent Startup Account
          3. Configuring Email Notification
          4. SQL Server Agent Proxy Account
        3. Viewing the SQL Server Agent Error Log
        4. SQL Server Agent Security
        5. Managing Operators
        6. Managing Jobs
          1. Defining Job Properties
          2. Defining Job Steps
          3. Defining Multiple Job Steps
          4. Defining Job Schedules
          5. Defining Job Notifications
          6. Viewing Job History
        7. Managing Alerts
          1. Defining Alert Properties
          2. Defining Alert Responses
        8. Scripting Jobs and Alerts
        9. Multiserver Job Management
          1. Creating a Master Server
          2. Enlisting Target Servers
          3. Creating Multiserver Jobs
        10. Event Forwarding
        11. Summary
      8. Chapter 14. SQL Server Policy-Based Management
        1. What’s New in Policy-Based Management
        2. Introduction to Policy-Based Management
        3. Policy-Based Management Concepts
          1. Facets
          2. Conditions
          3. Policies
          4. Categories
          5. Targets
          6. Execution Modes
          7. Central Management Servers
        4. Implementing Policy-Based Management
          1. Creating a Condition Based on a Facet
          2. Creating a Policy
          3. Creating a Category
          4. Evaluating Policies
          5. Importing and Exporting Policies
        5. Sample Templates and Real-World Examples
          1. Sample Policy Templates
          2. Evaluating Recovery Models
          3. Ensuring Object Naming Conventions
          4. Checking Best Practices Compliance
        6. Policy-Based Management Best Practices
        7. Summary
      9. Chapter 15. Security and User Administration
        1. What’s New in Security and User Administration
        2. An Overview of SQL Server Security
        3. Authentication Methods
          1. Windows Authentication Mode
          2. Mixed Authentication Mode
          3. Setting the Authentication Mode
        4. Managing Principals
          1. Logins
          2. SQL Server Security: Users
          3. The dbo User
          4. The guest User
          5. The INFORMATION_SCHEMA User
          6. The sys User
          7. User/Schema Separation
          8. Roles
          9. Fixed Server Roles
          10. Fixed Database Roles
          11. The public Role
          12. User-Defined Database Roles
          13. User-Defined Server Roles
          14. Application Roles
        5. Managing Securables
        6. Managing Permissions
        7. Managing SQL Server Logins
          1. Using SSMS to Manage Logins
          2. Using T-SQL to Manage Logins
        8. Managing SQL Server Users
          1. Using SSMS to Manage Users
          2. Using T-SQL to Manage Users
        9. Managing Database Roles
          1. Using SSMS to Manage Database Roles
          2. Using T-SQL to Manage Database Roles
        10. Managing Server Roles
          1. Using SSMS to Manage Server Roles
          2. Using T-SQL to Manage Server Roles
        11. Managing SQL Server Permissions
          1. Using SSMS to Manage Permissions
          2. Using SSMS to Manage Permissions at the Server Level
          3. Using SSMS to Manage Permissions at the Database Level
          4. Using SSMS to Manage Permissions at the Object Level
          5. Using T-SQL to Manage Permissions
        12. The Execution Context
          1. Explicit Context Switching
          2. Implicit Context Switching
        13. Summary
      10. Chapter 16. Data Encryption
        1. What’s New in Data Encryption
        2. An Overview of Data Encryption
        3. SQL Server Key Management
          1. Extensible Key Management
        4. Column-Level Encryption
          1. Encrypting Columns Using a Passphrase
          2. Encrypting Columns Using a Certificate
        5. Transparent Data Encryption
          1. Implementing Transparent Data Encryption
          2. Managing TDE in SSMS
          3. Backing Up TDE Certificates and Keys
          4. The Limitations of TDE
        6. Column-Level Encryption Versus Transparent Data Encryption
        7. Summary
      11. Chapter 17. Managing Linked Servers
        1. What’s New in Managing Linked Servers
        2. Linked Servers
          1. Distributed Queries
          2. Distributed Transactions
        3. Adding, Dropping, and Configuring Linked Servers
          1. sp_addlinkedserver
          2. sp_linkedservers
          3. sp_dropserver
          4. sp_serveroption
        4. Mapping Local Logins to Logins on Linked Servers
          1. sp_addlinkedsrvlogin
          2. sp_droplinkedsrvlogin
          3. sp_helplinkedsrvlogin
        5. Obtaining General Information About Linked Servers
        6. Executing a Stored Procedure via a Linked Server
        7. Setting Up Linked Servers Using SQL Server Management Studio
        8. Summary
      12. Chapter 18. SQL Server Configuration Options
        1. What’s New in Configuring, Tuning, and Optimizing SQL Server Options
        2. SQL Server Instance Architecture
        3. Configuration Options
        4. Fixing an Incorrect Option Setting
        5. Setting Configuration Options with SSMS
        6. Obsolete Configuration Options
        7. Configuration Options and Performance
          1. access check cache bucket count
          2. access check cache quota
          3. ad hoc distributed queries
          4. affinity I/O mask
          5. affinity mask
          6. Agent XP
          7. backup checksum default
          8. backup compression default
          9. blocked process threshold
          10. c2 audit mode
          11. clr enabled
          12. common criteria compliance enabled
          13. contained database authentication
          14. cost threshold for parallelism
          15. cross db ownership chaining
          16. cursor threshold
          17. Database Mail XPs
          18. default full-text language
          19. default language
          20. default trace enabled
          21. disallow results from triggers
          22. EKM provider enabled
          23. filestream_access_level
          24. fill factor
          25. index create memory
          26. in-doubt xact resolution
          27. lightweight pooling
          28. locks
          29. max degree of parallelism
          30. max server memory and min server memory
          31. max text repl size
          32. max worker threads
          33. media retention
          34. min memory per query
          35. nested triggers
          36. network packet size
          37. Ole Automation Procedures
          38. optimize for ad hoc workloads
          39. PH_timeout
          40. priority boost
          41. query governor cost limit
          42. query wait
          43. recovery interval
          44. remote access
          45. remote admin connections
          46. remote login timeout
          47. remote proc trans
          48. remote query timeout
          49. scan for startup procs
          50. show advanced options
          51. user connections
          52. user options
          53. XP-Related Configuration Options
        8. Summary
      13. Chapter 19. Working with and Deploying to Azure SQL Database
        1. Setting Up Subscriptions, Servers, and Databases
          1. Setting Up Your Windows Azure Subscription
          2. Creating a Logical Database Server
        2. Managing Your Server
          1. Configuring Your Firewall
          2. Using SQL Server Management Studio
          3. Using Management Portal
        3. Working with Databases
          1. Understanding SQL Database Service Tiers
          2. Managing Databases Using T-SQL
          3. Migrating Data into SQL Database
          4. Copying Databases
          5. Exporting Databases
        4. Backing Up and Restoring Databases
          1. Using SQL Database Backup, Replication, and Recovery
          2. Using Database Copies for Backup and Restore
          3. Using BACPAC Files for Backup and Restore
        5. Managing Logins, Users, and Roles
          1. Understanding Roles
          2. Managing Logins and Users
        6. Considerations for SQL Database Client Applications
          1. Connectivity Limitations
          2. Connection String Differences
        7. Understanding SQL Database Billing
          1. Baseline Billing
          2. Tracking Your Usage
        8. Understanding SQL Database Limitations
          1. Unsupported and Partially Supported Functionality
          2. References
        9. Summary
    15. Part IV: Database Administration
      1. Chapter 20. Creating and Managing Databases
        1. What’s New in Creating and Managing Databases
        2. Data Storage in SQL Server
        3. Database Files
          1. Primary Files
          2. Secondary Files
          3. Using Filegroups
          4. Using Partitions
          5. Transaction Log Files
        4. Creating Databases
          1. Using SSMS to Create a Database
          2. Using T-SQL to Create Databases
        5. Setting Database Options
          1. The Database Options
          2. Using T-SQL to Set Database Options
          3. Retrieving Option Information
        6. Managing Databases
          1. Managing File Growth
          2. Expanding Databases
          3. Shrinking Databases
          4. Moving Databases
          5. Restoring a Database to a New Location
          6. Using ALTER DATABASE
          7. Detaching and Attaching Databases
        7. Contained Databases
          1. Creating a Contained Database
          2. Connecting to a Contained Database
        8. Summary
      2. Chapter 21. Creating and Managing Tables
        1. What’s New in SQL Server 2014
        2. Creating Tables
          1. Using Object Explorer to Create Tables
          2. Using Database Diagrams to Create Tables
          3. Using T-SQL to Create Tables
        3. Defining Columns
          1. Data Types
          2. Column Properties
          3. Column Sets
          4. Working with Sparse Columns
          5. Sparse Columns: Good or Bad?
          6. Defining Sparse Columns in SSMS
        4. Defining Table Location
        5. Defining Table Constraints
        6. Modifying Tables
          1. Using T-SQL to Modify Tables
          2. Using Object Explorer and the Table Designer to Modify Tables
          3. Using Database Diagrams to Modify Tables
        7. Dropping Tables
        8. Using Partitioned Tables
          1. Creating a Partition Function
          2. Creating a Partition Scheme
          3. Creating a Partitioned Table
          4. Adding and Dropping Table Partitions
          5. Switching Table Partitions
        9. Using FILESTREAM Storage
          1. Enabling FILESTREAM Storage
          2. Setting Up a Database for FILESTREAM Storage
          3. Using FILESTREAM Storage for Data Columns
        10. Using FileTables
          1. FileTable Prerequisites
          2. Creating FileTables
          3. Copying Files to the FileTable
        11. Creating Temporary Tables
        12. Summary
      3. Chapter 22. Creating and Managing Indexes
        1. What’s New in Creating and Managing Indexes
        2. Types of Indexes
          1. Clustered Indexes
          2. Nonclustered Indexes
        3. Creating Indexes
          1. Creating Indexes with T-SQL
          2. Creating Indexes with SSMS
        4. Managing Indexes
          1. Managing Indexes with T-SQL
          2. Managing Indexes with SSMS
        5. Dropping Indexes
        6. Online Indexing Operations
        7. Indexes on Views
        8. Summary
      4. Chapter 23. Implementing Data Integrity
        1. What’s New in Data Integrity
        2. Types of Data Integrity
          1. Domain Integrity
          2. Entity Integrity
          3. Referential Integrity
        3. Enforcing Data Integrity
          1. Implementing Declarative Data Integrity
          2. Implementing Procedural Data Integrity
        4. Using Constraints
          1. The PRIMARY KEY Constraint
          2. The UNIQUE Constraint
          3. The FOREIGN KEY Referential Integrity Constraint
          4. The CHECK Constraint
          5. Creating Constraints
          6. Managing Constraints
        5. Rules
        6. Defaults
          1. Declarative Defaults
          2. Bound Defaults
        7. When a Default Is Applied
          1. Restrictions on Defaults
        8. Summary
      5. Chapter 24. Creating and Managing Views
        1. What’s New in Creating and Managing Views
        2. Definition of Views
        3. Using Views
          1. Simplifying Data Manipulation
          2. Focusing on Specific Data
          3. Abstracting Data
          4. Controlling Access to Data
        4. Creating Views
          1. Creating Views Using T-SQL
          2. ENCRYPTION
          3. Creating Views Using the View Designer
        5. Managing Views
          1. Altering Views with T-SQL
          2. Dropping Views with T-SQL
          3. Managing Views with SSMS
        6. Data Modifications and Views
        7. Partitioned Views
          1. Modifying Data Through a Partitioned View
          2. Distributed Partitioned Views
        8. Indexed Views
          1. Creating Indexed Views
          2. Indexed Views and Performance
          3. To Expand or Not to Expand
        9. Summary
      6. Chapter 25. Creating and Managing Stored Procedures
        1. What’s New in Creating and Managing Stored Procedures
        2. Advantages of Stored Procedures
        3. Creating Stored Procedures
          1. Creating Procedures in SSMS
        4. Executing Stored Procedures
          1. Executing Procedures in SSMS
          2. Execution Context and the EXECUTE AS Clause
          3. Using the WITH RESULT SETS Clause
        5. Deferred Name Resolution
          1. Identifying the Objects Referenced Within Stored Procedures
        6. Viewing Stored Procedures
        7. Modifying Stored Procedures
          1. Viewing and Modifying Stored Procedures with SSMS
        8. Using Input Parameters
          1. Setting Default Values for Parameters
          2. Passing Object Names as Parameters
          3. Using Wildcards in Parameters
          4. Using Table-Valued Parameters
        9. Using Output Parameters
        10. Returning Procedure Status
        11. Debugging Stored Procedures Using SQL Server Management Studio
        12. Startup Procedures
        13. Natively Compiled Stored Procedures
        14. T-SQL Stored Procedure Coding Guidelines
        15. Summary
      7. Chapter 26. Creating and Managing User-Defined Functions
        1. Why Use User-Defined Functions?
        2. Types of User-Defined Functions
          1. Scalar Functions
          2. Table-Valued Functions
        3. Creating and Managing User-Defined Functions
          1. Creating User-Defined Functions
          2. Viewing and Modifying User-Defined Functions
          3. Managing User-Defined Function Permissions
        4. Rewriting Stored Procedures as Functions
        5. Summary
      8. Chapter 27. Creating and Managing Triggers
        1. What’s New in Creating and Managing Triggers
        2. Using DML Triggers
          1. Creating DML Triggers
          2. Using AFTER Triggers
          3. Using inserted and deleted Tables
          4. INSTEAD OF Triggers
        3. Using DDL Triggers
          1. Creating DDL Triggers
          2. Managing DDL Triggers
        4. Using Nested Triggers
        5. Using Recursive Triggers
        6. Summary
      9. Chapter 28. Transaction Management and the Transaction Log
        1. What’s New in Transaction Management
        2. What Is a Transaction?
        3. How SQL Server Manages Transactions
        4. Defining Transactions
          1. AutoCommit Transactions
          2. Explicit User-Defined Transactions
          3. Implicit Transactions
          4. Implicit Transactions Versus Explicit Transactions
        5. Transactions and T-SQL Batches
        6. Transactions and Stored Procedures
        7. Transactions and Triggers
          1. Triggers and Transaction Nesting
          2. Triggers and Multistatement Transactions
          3. Using Savepoints in Triggers
        8. Transactions and Locking
          1. READ_COMMITTED_SNAPSHOT Isolation
        9. Coding Effective Transactions
        10. Transaction Logging and the Recovery Process
          1. The Checkpoint Process
          2. Automatic Checkpoints
          3. Indirect Checkpoints
          4. Manual Checkpoints
          5. The Recovery Process
          6. Managing the Transaction Log
        11. Long-Running Transactions
        12. Distributed Transactions
        13. Summary
      10. Chapter 29. Database Snapshots
        1. What’s New with Database Snapshots
        2. What Are Database Snapshots?
        3. Limitations and Restrictions of Database Snapshots
        4. Copy-on-Write Technology
        5. When to Use Database Snapshots
          1. Reverting to a Snapshot for Recovery Purposes
          2. Safeguarding a Database Prior to Making Mass Changes
          3. Providing a Testing (or Quality Assurance) Starting Point (Baseline)
          4. Providing a Point-in-Time Reporting Database
          5. Providing a Highly Available and Offloaded Reporting Database from a Database Mirror
        6. Setup and Breakdown of a Database Snapshot
          1. Creating a Database Snapshot
          2. Removing a Database Snapshot
        7. Reverting to a Database Snapshot for Recovery
          1. Reverting a Source Database from a Database Snapshot
        8. Database Snapshots Maintenance and Security Considerations
          1. Security for Database Snapshots
          2. Snapshot Sparse File Size Management
          3. Number of Database Snapshots per Source Database
        9. Summary
      11. Chapter 30. Database Maintenance
        1. What’s New in Database Maintenance
        2. The Maintenance Plan Wizard
          1. Backing Up Databases
          2. Checking Database Integrity
          3. Shrinking Databases
          4. Maintaining Indexes and Statistics
          5. Scheduling a Maintenance Plan
        3. Managing Maintenance Plans Without the Wizard
        4. Executing a Maintenance Plan
        5. Maintenance Without a Maintenance Plan
        6. Database Maintenance Policies
        7. Summary
    16. Part V: SQL Server Performance and Optimization
      1. Chapter 31. Understanding SQL Server Data Structures
        1. What’s New for Data Structures
        2. Understanding Data Structures
        3. Database Files and Filegroups
          1. Primary Data File
          2. Secondary Data Files
          3. The Log File
          4. File Management
          5. Using Filegroups
          6. FILESTREAM Filegroups
        4. Database Pages
          1. Page Types
          2. Data Pages
          3. Row-Overflow Pages
          4. LOB Data Pages
          5. Index Pages
        5. Space Allocation Structures
          1. Extents
          2. Global and Shared Global Allocation Map Pages
          3. Page Free Space Pages
          4. Index Allocation Map Pages
          5. Differential Changed Map Pages
          6. Bulk Changed Map Pages
        6. Data Compression
          1. Row-Level Compression
          2. Page-Level Compression
          3. The CI Record
          4. Implementing Page Compression
          5. Evaluating Page Compression
          6. Managing Data Compression with SSMS
        7. Understanding Table Structures
          1. Heap Tables
          2. Clustered Tables
        8. Understanding Index Structures
          1. Clustered Indexes
          2. Nonclustered Indexes
          3. Columnstore Indexes
        9. Data Modification and Performance
          1. Inserting Data
          2. Deleting Rows
          3. Updating Rows
        10. Summary
      2. Chapter 32. Indexes and Performance
        1. What’s New for Indexes and Performance
        2. Index Utilization
        3. Index Selection
        4. Evaluating Index Usefulness
        5. Index Statistics
          1. The Statistics Histogram
          2. How the Statistics Histogram Is Used
          3. Index Densities
          4. Estimating Rows Using Index Statistics
          5. Generating and Maintaining Index and Column Statistics
        6. SQL Server Index Maintenance
          1. Setting the Fill Factor
          2. Reapplying the Fill Factor
          3. Disabling Indexes
          4. Managing Indexes with SSMS
        7. Index Design Guidelines
          1. Clustered Index Indications
          2. Nonclustered Index Indications
          3. Index Covering
          4. Included Columns
          5. Wide Indexes Versus Multiple Indexes
        8. Indexed Views
        9. Indexes on Computed Columns
        10. Filtered Indexes and Statistics
          1. Creating and Using Filtered Indexes
          2. Creating and Using Filtered Statistics
        11. Choosing Indexes: Query versus Update Performance
        12. Identifying Missing Indexes
          1. The Database Engine Tuning Advisor
          2. Missing Index Dynamic Management Objects
          3. Missing Index Feature Versus Database Engine Tuning Advisor
        13. Identifying Unused Indexes
        14. Summary
      3. Chapter 33. In-Memory Optimization and the Buffer Pool Extension
        1. Overview of In-Memory OLTP
          1. In-Memory OLTP Concepts and Terminology
        2. In-Memory Optimization Requirements
        3. Limitations of In-Memory OLTP
        4. Using In-Memory OLTP
          1. Enabling a Database for In-Memory OLTP
          2. Creating Memory-Optimized Tables
          3. Memory-Optimized Tables Row Structure
          4. Indexes on Memory-Optimized Tables
          5. Garbage Collection
          6. Maintaining Statistics on Memory-Optimized Tables
          7. Memory-Optimized Index Design Guidelines
        5. Using Memory-Optimized Tables
          1. Interpreted T-SQL Support for In-Memory OLTP
          2. Native Compilation
          3. Natively Compiled Stored Procedures
          4. Memory-Optimized Table Variables
          5. Transactions and Memory-Optimized Tables
          6. Monitoring Transactions on Memory-Optimized Tables
        6. Logging, Checkpoint, and Recovery for In-Memory OLTP
          1. Transaction Logging
          2. Checkpoint
          3. Recovery
        7. Managing Memory for In-Memory OLTP
          1. Monitoring Memory Usage
          2. Managing Memory with the Resource Governor
        8. Backup and Recovery of Memory-Optimized Databases
        9. Migrating to In-Memory OLTP
          1. Using the AMR Tool
          2. Using the Table Memory Optimization Advisor to Migrate Disk-Based Tables
        10. Dynamic Management Views for In-Memory OLTP
        11. The Buffer Pool Extension
        12. Summary
      4. Chapter 34. Understanding Query Optimization
        1. What’s New in Query Optimization
        2. What Is the Query Optimizer?
        3. Query Compilation and Optimization
          1. Compiling DML Statements
          2. Optimization Steps
        4. Query Analysis
          1. Identifying Search Arguments
          2. Identifying OR Clauses
          3. Identifying Join Clauses
        5. Row Estimation and Index Selection
          1. Evaluating SARG and Join Selectivity
          2. Estimating Access Path Cost
          3. Using Multiple Indexes
          4. Optimizing with Indexed Views
          5. Optimizing with Filtered Indexes
          6. Evaluating Cardinality Estimates
        6. Join Selection
          1. Join Processing Strategies
          2. Determining the Optimal Join Order
          3. Subquery Processing
        7. Execution Plan Selection
        8. Query Plan Caching
          1. Query Plan Reuse
          2. Query Plan Aging
          3. Recompiling Query Plans
          4. Monitoring the Plan Cache
        9. Other Query Processing Strategies
          1. Predicate Transitivity
          2. GROUP BY Optimization
          3. Queries with DISTINCT
          4. Queries with UNION
          5. Queries Using Columnstore Indexes
        10. Parallel Query Processing
          1. Parallel Query Configuration Options
          2. Identifying Parallel Queries
        11. Common Query Optimization Problems
          1. Out-of-Date or Insufficient Statistics
          2. Poor Index Design
          3. Search Argument Problems
          4. Large Complex Queries
          5. Triggers
          6. The New Cardinality Estimator Changes
        12. Summary
      5. Chapter 35. Managing the Query Optimizer
        1. What’s New in Managing the Query Optimizer
        2. Should You Override the Query Optimizer?
        3. Using Optimizer Hints
          1. Forcing Index Selection with Table Hints
          2. Forcing Join Strategies with Join Hints
          3. Specifying Query Processing Hints
        4. Using Forced Parameterization
          1. Guidelines for Using Forced Parameterization
        5. Using the USE PLAN Query Hint
        6. Using Plan Guides
          1. Creating Plan Guides
          2. Managing Plan Guides
          3. Validating Plan Guides
          4. The sys.plan_guides Catalog View
          5. Plan Guide Best Practices
          6. Verifying That a Plan Guide Is Being Applied
          7. Creating and Managing Plan Guides in SSMS
        7. Limiting Query Plan Execution with the Query Governor
        8. Summary
      6. Chapter 36. Query Analysis
        1. What’s New in Query Analysis
        2. Query Analysis in SSMS
          1. Execution Plan ToolTips
          2. Logical and Physical Operator Icons
          3. Analyzing Stored Procedures
          4. Saving and Viewing Graphical Execution Plans
          5. Displaying Execution Plan XML
          6. Missing Index Hints
        3. SSMS Client Statistics
        4. Using the SET SHOWPLAN Options
          1. SHOWPLAN_TEXT
          2. SHOWPLAN_ALL
          3. SHOWPLAN_XML
        5. Using sys.dm_exec_query_plan
        6. Query Statistics
          1. STATISTICS IO
          2. STATISTICS TIME
          3. Using datediff() to Measure Runtime
          4. STATISTICS PROFILE
          5. STATISTICS XML
        7. Query Analysis with SQL Server Profiler
        8. Summary
      7. Chapter 37. Locking and Performance
        1. What’s New in Locking and Performance
        2. The Need for Locking
        3. Transaction Isolation Levels in SQL Server
          1. Read Uncommitted Isolation
          2. Read Committed Isolation
          3. Read Committed Snapshot Isolation
          4. Repeatable Read Isolation
          5. Serializable Read Isolation
          6. Snapshot Isolation
        4. The Lock Manager
        5. Monitoring Lock Activity in SQL Server
          1. Querying the sys.dm_tran_locks View
          2. Viewing Locking Activity with SQL Server Profiler
          3. Monitoring Locks with Performance Monitor
        6. SQL Server Lock Types
          1. Shared Locks
          2. Update Locks
          3. Exclusive Locks
          4. Intent Locks
          5. Schema Locks
          6. Bulk Update Locks
        7. SQL Server Lock Granularity
          1. Serialization and Key-Range Locking
          2. Using Application Locks
          3. Index Locking
          4. Row-Level Versus Page-Level Locking
          5. Lock Escalation
        8. Lock Compatibility
        9. Locking Contention and Deadlocks
          1. Identifying Locking Contention
          2. Setting the Lock Timeout Interval
          3. Minimizing Locking Contention
          4. Deadlocks
        10. Table Hints for Locking
          1. Transaction Isolation-Level Hints
          2. Lock Granularity Hints
          3. Lock Type Hints
        11. Optimistic Locking
          1. Optimistic Locking Using the rowversion Data Type
          2. Optimistic Locking with Snapshot Isolation
        12. Summary
      8. Chapter 38. Database Design and Performance
        1. What’s New in Database Design and Performance
        2. Basic Tenets of Designing for Performance
        3. Logical Database Design Issues
          1. Normalization Conditions
          2. Normalization Forms
          3. Benefits of Normalization
          4. Drawbacks of Normalization
        4. Denormalizing a Database
          1. Denormalization Guidelines
          2. Essential Denormalization Techniques
        5. Database Filegroups and Performance
        6. RAID Technology
          1. RAID Level 0
          2. RAID Level 1
          3. RAID Level 10
          4. RAID Level 5
        7. SQL Server and SAN Technology
          1. What Is a SAN?
          2. SAN Considerations for SQL Server
        8. SQL Server and VM Technology
          1. VM Host Recommendations
          2. VM Guest Recommendations
          3. SQL Server in a VM
        9. Summary
      9. Chapter 39. Monitoring SQL Server Performance
        1. What’s New in Monitoring SQL Server Performance
        2. Performance Monitoring Tools
          1. The Data Collector and the MDW
          2. SQL Server Utility
          3. SQL Server Extended Events
          4. Windows Performance Monitor
        3. Summary
      10. Chapter 40. SQL Server Database Engine Tuning Advisor
        1. What’s New in SQL Server Database Engine Tuning Advisor
        2. SQL Server Instance Architecture
        3. Database Engine Tuning Advisor
          1. The Database Engine Tuning Advisor GUI
          2. The Database Engine Tuning Advisor Command Line
        4. Summary
      11. Chapter 41. Managing Workloads with the Resource Governor
        1. What’s New for Resource Governor
        2. Overview of Resource Governor
        3. Resource Governor Components
          1. Classification
          2. Resource Pools
          3. Workload Groups
        4. Configuring Resource Governor
          1. Enabling Resource Governor
          2. Defining Resource Pools
          3. Defining Workload Groups
          4. Creating a Classifier Function
        5. Monitoring Resource Usage
        6. Modifying Your Resource Governor Configuration
          1. Deleting Workload Groups
          2. Deleting Resource Pools
          3. Modifying a Classifier Function
        7. Summary
    17. Part VI: SQL Server High Availability
      1. Chapter 42. SQL Server High Availability Fundamentals
        1. What’s New in High Availability
        2. What Is High Availability?
        3. The Fundamentals of HA
          1. Hardware Factors
          2. Backup Considerations
          3. Operating System Upgrades
          4. Vendor Agreements Followed
          5. Training Kept Up-to-Date
          6. Quality Assurance Done Well
          7. Standards/Procedures Followed
          8. Server Instance Isolation
        4. Building Solutions with One or More HA Options
          1. Failover Cluster Services (FCS)
          2. SQL Clustering
          3. AlwaysOn Failover Clustering Instance (FCI)
          4. AlwaysOn Availability Groups
          5. Data Replication
          6. Change Data Capture
          7. Log Shipping
          8. Database Mirroring
          9. Combining Failover with Scale-Out Options
        5. Other HA Techniques That Yield Great Results
        6. High Availability from the Windows Server Family Side
          1. Microsoft Virtual Machines and Hyper-V
        7. Summary
      2. Chapter 43. Data Replication
        1. What’s New in Data Replication
        2. What Is Replication?
        3. The Publisher, Distributor, and Subscriber Magazine Metaphor
          1. Publications and Articles
          2. Filtering Articles
        4. Replication Scenarios
          1. The Central Publisher Replication Model
          2. The Central Publisher with Remote Distributor Replication Model
          3. The Publishing Subscriber Replication Model
          4. The Central Subscriber Replication Model
          5. The Multiple Publishers with Multiple Subscribers Replication Model
          6. The Updating Subscribers Replication Model
          7. The Peer-to-Peer Replication Model
        5. Subscriptions
          1. Anonymous Subscriptions (Pull Subscriptions)
          2. The Distribution Database
        6. Replication Agents
          1. The Snapshot Agent
          2. The Log Reader Agent
          3. The Distribution Agent
          4. The Merge Agent
          5. Other Specialized Agents
        7. Planning for SQL Server Data Replication
          1. Autonomy, Timing, and Latency of Data
          2. Methods of Data Distribution
        8. SQL Server Replication Types
          1. Snapshot Replication
          2. Transactional Replication
          3. Merge Replication
        9. Basing the Replication Design on User Requirements
          1. Data Characteristics
        10. Setting Up Replication
          1. Creating a Distributor and Enabling Publishing
          2. Creating a Publication
          3. Horizontal and Vertical Filtering
          4. Creating Subscriptions
        11. Scripting Replication
        12. Monitoring Replication
          1. Replication Monitoring SQL Statements
          2. Monitoring Replication within SQL Server Management Studio
          3. Troubleshooting Replication Failures
          4. Peer-to-Peer Replication
          5. The Performance Monitor
          6. Replication in Heterogeneous Environments
          7. Backup and Recovery in a Replication Configuration
          8. Some Thoughts on Performance
          9. Log Shipping
          10. Change Data Capture
          11. The Change Data Capture Tables
          12. Enabling CDC for a Database
          13. Enabling CDC for a Table
          14. Querying the CDC Tables
          15. CDC and DDL Changes to Source Tables
          16. CDC and AlwaysOn Availability Groups
        13. Change Tracking
          1. Implementing Change Tracking
          2. Identifying Tracked Changes
          3. Identifying Changed Columns
          4. Change Tracking Overhead
        14. Summary
      3. Chapter 44. SQL Server Failover Clustering
        1. What’s New in SQL Server AlwaysOn Failover Clustering
        2. How Microsoft SQL Server Failover Clustering Works
          1. Understanding WSFC
          2. Extending WSFC with NLB
          3. How WSFC Sets the Stage for SQL Server Clustering
        3. Installing SQL Server Failover Clustering
          1. Configuring SQL Server Database Disks
          2. Installing Network Interfaces
          3. Installing WSFC
          4. Cluster Events
          5. Installing SQL Server within WSFC
          6. Fail Over to Another Node
          7. The Client Connection Impact of a Failover
          8. Potential Problems to Watch Out for with SQL Server Failover Clustering
        4. Summary
      4. Chapter 45. SQL Server AlwaysOn and Availability Groups
        1. What’s New in SQL Server AlwaysOn and Availability Groups
        2. SQL Server AlwaysOn and Availability Groups
          1. Windows Failover Cluster Services
          2. AlwaysOn Failover Clustering Instances
          3. AlwaysOn Availability Groups
          4. Combining Failover with Scale-Out Options
          5. Building a Multinode AlwaysOn Configuration
          6. Adding Replicas
          7. Dashboard and Monitoring
        3. Summary
    18. Part VII: SQL Server Business Intelligence Features
      1. Chapter 46. SQL Server 2014 Analysis Services
        1. What’s New in SSAS
        2. Understanding SSAS and OLAP
        3. Understanding the SSAS Environment Wizards
          1. OLAP Versus OLTP
        4. An Analytics Design Methodology
          1. An Analytics Mini-Methodology
        5. An OLAP Requirements Example: CompSales International
          1. CompSales International Requirements
          2. OLAP Cube Creation
          3. Using SQL Server Visual Studio BI
          4. Creating an OLAP Database
          5. Defining Dimensions and Hierarchies
          6. Creating the Other Dimensions
          7. Creating the Cube
          8. Building and Deploying the Cube
          9. Populating the Cube with Data
          10. Aggregating Data Within the Cube
          11. Browsing Data in the Cube
          12. Delivering Data to Users
          13. ADO MD
          14. Query Analysis and Optimization
        6. Generating a Relational Database
          1. Limitations of a Relational Database
          2. Cube Perspectives
          3. Data Mining
          4. Security and Roles
          5. Tabular Models and SSAS
          6. OLAP Performance
        7. Summary
      2. Chapter 47. SQL Server Integration Services
        1. What’s New with SSIS
        2. SSIS Basics
        3. SSIS Architecture and Concepts
        4. SSIS Tools and Utilities
        5. A Data Transformation Requirement
        6. Running the Import and Export Wizard
        7. The SSIS Designer
        8. The Package Execution Utility
          1. The dtexec Utility
          2. Running Packages
          3. Running Package Examples
          4. The dtutil Utility
          5. dtutil Examples
        9. Change Data Capture and SSIS
        10. Using bcp
          1. Fundamentals of Exporting and Importing Data
          2. File Data Types
          3. Format Files
          4. Using Views
        11. Logged and Nonlogged Operations
          1. Batches
          2. Parallel Loading
          3. Supplying Hints to bcp
        12. Summary
      3. Chapter 48. SQL Server 2014 Reporting Services
        1. What’s New in SSRS 2014
          1. Discontinued Functionality and Breaking Changes
        2. Reporting Services Architecture
          1. HTTP Architecture
        3. Installing and Configuring SSRS
          1. The Installation Sequence
          2. SSRS Configuration Using RSCM
        4. Developing Reports
          1. Tools of the Trade
          2. Report Basics
          3. Overview of the Report Development Process
          4. Data Planning and Preparation
          5. Using Shared Data Sources
          6. Using Datasets
          7. Using Shared Datasets
          8. Developing Reports Using SSDT
          9. Working with the Tablix
          10. Understanding Expressions
          11. Report Design Fundamentals
          12. Designing Reports Using Report Builder
          13. Report Builder and Report Model Security
          14. Enabling Report Builder
        5. Management and Security
          1. Securing Reports
          2. Subscriptions
          3. Report Execution Options
        6. Performance and Monitoring
          1. SSRS Trace Log
          2. Execution Log
          3. Windows Event Log
          4. Performance Counters
        7. Summary
      4. Chapter 49. Data Quality Services
        1. Data Quality Services
        2. What’s New in DQS
        3. Master Data Management
          1. Data Quality Services
        4. Summary
      5. Chapter 50. Master Data Services
        1. Master Data Services
        2. Data Quality Services
        3. What’s New in MDS
        4. Master Data Management
          1. Master Data Services Terms and Concepts
          2. Master Data Services
        5. Summary
      6. Chapter 51. Parallel Data Warehouse
        1. What’s New in Parallel Data Warehouse
        2. Understanding MPP and PDW
          1. MPP Architecture
          2. The PDW
          3. Data on a PDW
          4. PDW and Big Data (Hadoop)
          5. xVelocity Columnstore Indexes
          6. Columnstore Indexes
        3. Summary
    19. Index
    20. Code Snippets

    Product information

    • Title: Microsoft SQL Server 2014 Unleashed
    • Author(s):
    • Release date: June 2015
    • Publisher(s): Sams
    • ISBN: 9780134084473