skip to content
Microsoft SQL Server 2014 unleashed Preview this item
ClosePreview this item

Microsoft SQL Server 2014 unleashed

Author: Ray Rankins; Paul Bertucci; Chris Gallelli; Alex Silverstein
Publisher: Indianapolis, Indiana : Sams, [2015] ©2015
Edition/Format:   eBook : Document : EnglishView all editions and formats

(not yet rated) 0 with reviews - Be the first.

More like this


Find a copy online

Links to this item

Find a copy in the library

&AllPage.SpinnerRetrieving; Finding libraries that hold this item...


Genre/Form: Electronic books
Material Type: Document, Internet resource
Document Type: Internet Resource, Computer File
All Authors / Contributors: Ray Rankins; Paul Bertucci; Chris Gallelli; Alex Silverstein
ISBN: 9780134084473 0134084470 0672337290 9780672337291
OCLC Number: 911517888
Description: 1 online resource (1 volume) : illustrations
Contents: Introduction 1Who This Book Is For 2What This Book Covers 2Conventions Used in This Book 4Good Luck! 5 Part I Welcome to Microsoft SQL Server 1 SQL Server 2014 Overview 9SQL Server Components and Features 9The SQL Server Database Engine 10SQL Server 2014 Administration and Management Tools 12Replication 15Merge Replication 16SQL Server AlwaysOn Features 17SQL Server Service Broker 18Full-Text and Semantic Search 18SQL Server Integration Services (SSIS) 20SQL Server Analysis Services (SSAS) 21SQL Server Reporting Services (SSRS) 23Master Data Services 23Data Quality Services 24SQL Server 2014 Editions 24SQL Server 2014 Standard Edition 25SQL Server 2014 Enterprise Edition 26Differences Between the Enterprise and Standard Editions of SQL Server 26Other SQL Server 2014 Editions 28SQL Server Licensing 30Web Edition Licensing 31Developer Edition Licensing 32Express Edition Licensing 32Choosing a Licensing Model 32Mixing Licensing Models 32Licensing SQL Server of High Availability 32Licensing SQL Server in a Virtual Environment 34Summary 352 What's New in SQL Server 2014 37New SQL Server 2014 Features 37Memory-Optimized Tables/In-Memory OLTP 38New Cardinality Estimation Logic 38Delayed Durability for Transactions 38Buffer Pool Extension 38SQL Server Data Tools for Business Intelligence 39SQL Server 2014 Enhancements 39Resource Governor Enhancements 39Security Enhancements 39Backup and Restore Enhancements 40Indexing Enhancements 40Monitoring Enhancements 41SQL Server AlwaysOn and Availability Groups Enhancements 42New Transact-SQL Enhancements 42Deprecated and Discontinued Features 42Summary 45 Part II SQL Server Tools and Utilities 3 SQL Server Management Studio 49What's New in SSMS 50The Integrated Environment 50Window Management 50Integrated Help 53Administration Tools 56Registered Servers 56Object Explorer 58Activity Monitor 60Log File Viewer 62SQL Server Utility 64Development Tools 69The Query Editor 69Managing Projects in SSMS 77Integrating SSMS with Source Control 78Using SSMS Templates 80Using SSMS Snippets 84T-SQL Debugging 85Multiserver Queries 86Summary 874 SQL Server Command-Line Utilities 89What's New in SQL Server Command-Line Utilities 90The sqlcmd Command-Line Utility 91Executing the sqlcmd Utility 93Using Scripting Variables with sqlcmd 95The dta Command-Line Utility 96The tablediff Command-Line Utility 99The bcp Command-Line Utility 102The sqldiag Command-Line Utility 103The sqlservr Command-Line Utility 105The sqlLocalDB Command-Line Utility 106Summary 1085 SQL Server Profiler 111What's New with SQL Server Profiler 111SQL Server Profiler Architecture 112Creating Traces 113Events 115Data Columns 117Filters 120Executing Traces and Working with Trace Output 122Saving and Exporting Traces 123Saving Trace Output to a File 123Saving Trace Output to a Table 124Saving the Profiler GUI Output 124Importing Trace Files 125Importing a Trace File into a Trace Table 125Analyzing Trace Output with the Database Engine Tuning Advisor 128Replaying Trace Data 128Defining Server-Side Traces 131Monitoring Running Traces 141Stopping Server-Side Traces 143Profiler Usage Scenarios 145Analyzing Slow Stored Procedures or Queries 145Deadlocks 146Identifying Ad Hoc Queries 148Identifying Performance Bottlenecks 148Monitoring Auto-Update Statistics 150Monitoring Application Progress 151Summary 1536 SQL Distributed Replay 155What's New for Distributed Replay 155Overview of Distributed Replay 155Distributed Replay Components 156Distributed Replay Administrative Tool 157Distributed Replay Controller 157Distributed Replay Clients 158Target Server 158Configuring Distributed Replay 158Controller Configuration File 159Client Configuration File 159Preprocess Configuration File 160Replay Configuration File 161Replay the Trace Data 163Configure Permissions and Security 163Capture the Workload 165Preprocess the Trace File 166Apply the Workload 167Summary 169 Part III SQL Server Administration 7 SQL Server System and Database Administration 173What's New in SQL Server System and Database Administration 173System Administrator Responsibilities 174System Databases 174The master Database 175The resource Database 176The model Database 176The msdb Database 176The distribution Database 176The tempdb Database 177Maintaining System Databases 177System Tables 178System Views 179Compatibility Views 180Catalog Views 182Information Schema Views 184Dynamic Management Views 186System Stored Procedures 189Useful System Stored Procedures 189Summary 1918 Installing SQL Server 2014 193What's New in Installing SQL Server 2014 193Installation Requirements 193Hardware Requirements 194Software Requirements 195Installation Walkthrough 198Install Screens, Step-by-Step 198Installing SQL Server Documentation 217Installing SQL Server Using a Configuration File 219Running an Automated or Manual Install 224Installing SQL Server Using Sysprep 226Preparing a SQL Server Sysprep Image 226Completing a SQL Server Sysprep Image 229Modifying a SQL Server Sysprep Image 231Common Uses of SQL Server Sysprep Images 232Installing Service Packs and Cumulative Updates 233Applying a Service Pack or Cumulative Update During a New Installation 233Summary 2369 Upgrading to SQL Server 2014 237What's New in Upgrading SQL Server 237The SQL Server 2014 Upgrade Matrix 237Identifying Products and Features to be Upgraded 240Using the SQL Server Upgrade Advisor (UA) 241Getting Started with the UA 241The Analysis Wizard 243The Report Viewer 249Destination: SQL Server 2014 250Side-by-Side Upgrades 251Upgrading In-Place 259Upgrading the Database Engine 260Installing Product Updates (Slipstreaming) During Upgrades 263Upgrading Using a Configuration File 264Upgrading from Pre-SQL Server 2005 Versions 266Upgrading Other SQL Server Components 266Upgrading Analysis Services 266Upgrading SQL Server Analysis Services 266Upgrading Reporting Services 266Upgrading SSIS Packages 269Migrating DTS Packages 271Summary 27110 Client Installation and Configuration 273What's New in Client Installation and Configuration 273Client/Server Networking Considerations 274Server Network Protocols 275The Server Endpoint Layer 277The Role of SQL Browser 280Client Installation 281Installing the Client Tools 281Installing SNAC 282Client Configuration 284Client Configuration Using SSCM 284Connection Encryption 287Client Data Access Technologies 289Provider Choices 290Connecting Using the Various Providers and Drivers 291General Networking Considerations and Troubleshooting 296Summary 29911 Database Backup and Restore 301What's New in Database Backup and Restore 301Developing a Backup and Restore Plan 302Types of Backups 303Full Database Backups 304Differential Database Backups 304Partial Backups 305Differential Partial Backups 305File and Filegroup Backups 305Copy-Only Backups 306Transaction Log Backups 306Recovery Models 306Full Recovery 307Bulk-Logged Recovery 308Simple Recovery 309Backup Devices 310Disk Devices 310Tape Devices 310Network Shares 311Media Sets and Families 311Creating Backup Devices 311Backing Up a Database 312Creating Database Backups with SSMS 312Creating Database Backups with T-SQL 315Backing Up the Transaction Log 318Creating Transaction Log Backups with SSMS 318Creating Transaction Log Backups with T-SQL 319Backup Scenarios 320Full Database Backups Only 320Full Database Backups with Transaction Log Backups 321Differential Backups 322Partial Backups 323File/Filegroup Backups 325Mirrored Backups 326Copy-Only Backups 326Compressed Backups 327Encrypted Backups 328System Database Backups 329Restoring Databases and Transaction Logs 330Restores with T-SQL 330Restoring by Using SSMS 334Restore Information 339Restore Scenarios 342Restoring to a Different Database 342Restoring a Snapshot 344Restoring a Transaction Log 344Restoring to the Point of Failure 345Restoring to a Point in Time 347Online Restores 349Restoring the System Databases 349Additional Backup Considerations 351Frequency of Backups 352Using a Standby Server 352Snapshot Backups 353Considerations for Very Large Databases 354Maintenance Plans 354Summary 35512 Database Mail 357What's New in Database Mail 357Setting Up Database Mail 358Creating Mail Profiles and Accounts 359Using T-SQL to Update and Delete Mail Objects 362Setting System-Wide Mail Settings 363Testing Your Setup 364Sending and Receiving with Database Mail 364The Service Broker Architecture 364Sending Email 365Receiving Email 371Using SQL Server Agent Mail 371Job Mail Notifications 371Creating an Operator 371Enabling SQL Agent Mail 371Creating the Job 372Testing the Job-Completion Notification 373Alert Mail Notifications 373Creating an Alert 373Testing the Alert Notification 374Related Views and Procedures 375Viewing the Mail Configuration Objects 375Viewing Mail Message Data 376Summary 37713 SQL Server Agent 379What's New in Scheduling and Notification 380Configuring the SQL Server Agent 380Configuring SQL Server Agent Properties 380Configuring the SQL Server Agent Startup Account 382Configuring Email Notification 384SQL Server Agent Proxy Account 385Viewing the SQL Server Agent Error Log 387SQL Server Agent Security 388Managing Operators 389Managing Jobs 391Defining Job Properties 391Defining Job Steps 392Defining Multiple Job Steps 394Defining Job Schedules 395Defining Job Notifications 397Viewing Job History 398Managing Alerts 399Defining Alert Properties 399Defining Alert Responses 402Scripting Jobs and Alerts 404Multiserver Job Management 405Creating a Master Server 406Enlisting Target Servers 407Creating Multiserver Jobs 407Event Forwarding 407Summary 40814 SQL Server Policy-Based Management 409What's New in Policy-Based Management 409Introduction to Policy-Based Management 410Policy-Based Management Concepts 411Facets 411Conditions 414Policies 415Categories 415Targets 415Execution Modes 415Central Management Servers 416Implementing Policy-Based Management 418Creating a Condition Based on a Facet 418Creating a Policy 420Creating a Category 422Evaluating Policies 424Importing and Exporting Policies 425Sample Templates and Real-World Examples 426Sample Policy Templates 426Evaluating Recovery Models 427Ensuring Object Naming Conventions 427Checking Best Practices Compliance 427Policy-Based Management Best Practices 427Summary 42815 Security and User Administration 429What's New in Security and User Administration 429An Overview of SQL Server Security 430Authentication Methods 433Windows Authentication Mode 433Mixed Authentication Mode 433Setting the Authentication Mode 433Managing Principals 434Logins 434SQL Server Security: Users 437The dbo User 438The guest User 439The INFORMATION_SCHEMA User 439The sys User 439User/Schema Separation 440Roles 441Fixed Server Roles 442Fixed Database Roles 443The public Role 445User-Defined Database Roles 446User-Defined Server Roles 448Application Roles 448Managing Securables 449Managing Permissions 450Managing SQL Server Logins 452Using SSMS to Manage Logins 452Using T-SQL to Manage Logins 456Managing SQL Server Users 457Using SSMS to Manage Users 458Using T-SQL to Manage Users 460Managing Database Roles 461Using SSMS to Manage Database Roles 461Using T-SQL to Manage Database Roles 462Managing Server Roles 462Using SSMS to Manage Server Roles 463Using T-SQL to Manage Server Roles 463Managing SQL Server Permissions 464Using SSMS to Manage Permissions 464Using SSMS to Manage Permissions at the Server Level 465Using SSMS to Manage Permissions at the Database Level 467Using SSMS to Manage Permissions at the Object Level 470Using T-SQL to Manage Permissions 472The Execution Context 473Explicit Context Switching 473Implicit Context Switching 474Summary 47516 Data Encryption 477What's New in Data Encryption 478An Overview of Data Encryption 478SQL Server Key Management 480Extensible Key Management 482Column-Level Encryption 483Encrypting Columns Using a Passphrase 484Encrypting Columns Using a Certificate 486Transparent Data Encryption 490Implementing Transparent Data Encryption 491Managing TDE in SSMS 493Backing Up TDE Certificates and Keys 495The Limitations of TDE 496Column-Level Encryption Versus Transparent Data Encryption 496Summary 49817 Managing Linked Servers 499What's New in Managing Linked Servers 500Linked Servers 500Distributed Queries 501Distributed Transactions 502Adding, Dropping, and Configuring Linked Servers 503sp_addlinkedserver 503sp_linkedservers 510sp_dropserver 512sp_serveroption 512Mapping Local Logins to Logins on Linked Servers 513sp_addlinkedsrvlogin 514sp_droplinkedsrvlogin 515sp_helplinkedsrvlogin 516Obtaining General Information About Linked Servers 517Executing a Stored Procedure via a Linked Server 518Setting Up Linked Servers Using SQL Server Management Studio 519Summary 52318 SQL Server Configuration Options 525What's New in Configuring, Tuning, and Optimizing SQL Server Options 525SQL Server Instance Architecture 526Configuration Options 527Fixing an Incorrect Option Setting 535Setting Configuration Options with SSMS 535Obsolete Configuration Options 535Configuration Options and Performance 536access check cache bucket count 536access check cache quota 536ad hoc distributed queries 537affinity I/O mask 537affinity mask 539Agent XP 540backup checksum default 541backup compression default 541blocked process threshold 542c2 audit mode 542clr enabled 543common criteria compliance enabled 543contained database authentication 543cost threshold for parallelism 544cross db ownership chaining 545cursor threshold 545Database Mail XPs 546default full-text language 546default language 548default trace enabled 550disallow results from triggers 551EKM provider enabled 551filestream_access_level 551fill factor 552index create memory 552in-doubt xact resolution 553lightweight pooling 553locks 554max degree of parallelism 554max server memory and min server memory 554max text repl size 556max worker threads 557media retention 558min memory per query 558nested triggers 559network packet size 559Ole Automation Procedures 560optimize for ad hoc workloads 560PH_timeout 561priority boost 561query governor cost limit 562query wait 562recovery interval 563remote access 564remote admin connections 564remote login timeout 564remote proc trans 565remote query timeout 565scan for startup procs 565show advanced options 566user connections 566user options 567XP-Related Configuration Options 568Summary 56919 Working with and Deploying to Azure SQL Database 571Setting Up Subscriptions, Servers, and Databases 571Setting Up Your Windows Azure Subscription 572Creating a Logical Database Server 574Managing Your Server 576Configuring Your Firewall 577Using SQL Server Management Studio 578Using Management Portal 579Working with Databases 580Understanding SQL Database Service Tiers 580Managing Databases Using T-SQL 584Migrating Data into SQL Database 586Copying Databases 587Exporting Databases 588Backing Up and Restoring Databases 590Using SQL Database Backup, Replication, and Recovery 590Using Database Copies for Backup and Restore 592Using BACPAC Files for Backup and Restore 593Managing Logins, Users, and Roles 595Understanding Roles 595Managing Logins and Users 596Considerations for SQL Database Client Applications 598Connectivity Limitations 598Connection String Differences 599Understanding SQL Database Billing 599Baseline Billing 599Tracking Your Usage 601Understanding SQL Database Limitations 603Unsupported and Partially Supported Functionality 603References 606Summary 606 Part IV Database Administration 20 Creating and Managing Databases 609What's New in Creating and Managing Databases 610Data Storage in SQL Server 610Database Files 611Primary Files 612Secondary Files 612Using Filegroups 613Using Partitions 616Transaction Log Files 616Creating Databases 617Using SSMS to Create a Database 618Using T-SQL to Create Databases 621Setting Database Options 622The Database Options 623Using T-SQL to Set Database Options 625Retrieving Option Information 626Managing Databases 629Managing File Growth 629Expanding Databases 630Shrinking Databases 631Moving Databases 636Restoring a Database to a New Location 636Using ALTER DATABASE 636Detaching and Attaching Databases 637Contained Databases 639Creating a Contained Database 640Connecting to a Contained Database 642Summary 64321 Creating and Managing Tables 645What's New in SQL Server 2014 645Creating Tables 646Using Object Explorer to Create Tables 646Using Database Diagrams to Create Tables 647Using T-SQL to Create Tables 648Defining Columns 650Data Types 651Column Properties 657Column Sets 663Working with Sparse Columns 664Sparse Columns: Good or Bad? 667Defining Sparse Columns in SSMS 667Defining Table Location 668Defining Table Constraints 670Modifying Tables 672Using T-SQL to Modify Tables 672Using Object Explorer and the Table Designer to Modify Tables 675Using Database Diagrams to Modify Tables 678Dropping Tables 680Using Partitioned Tables 681Creating a Partition Function 682Creating a Partition Scheme 684Creating a Partitioned Table 686Adding and Dropping Table Partitions 689Switching Table Partitions 693Using FILESTREAM Storage 697Enabling FILESTREAM Storage 698Setting Up a Database for FILESTREAM Storage 701Using FILESTREAM Storage for Data Columns 702Using FileTables 705FileTable Prerequisites 705Creating FileTables 707Copying Files to the FileTable 707Creating Temporary Tables 709Summary 71022 Creating and Managing Indexes 711What's New in Creating and Managing Indexes 711Types of Indexes 712Clustered Indexes 712Nonclustered Indexes 714Creating Indexes 716Creating Indexes with T-SQL 716Creating Indexes with SSMS 720Managing Indexes 722Managing Indexes with T-SQL 723Managing Indexes with SSMS 726Dropping Indexes 727Online Indexing Operations 727Indexes on Views 729Summary 73023 Implementing Data Integrity 731What's New in Data Integrity 731Types of Data Integrity 732Domain Integrity 732Entity Integrity 732Referential Integrity 732Enforcing Data Integrity 732Implementing Declarative Data Integrity 732Implementing Procedural Data Integrity 733Using Constraints 733The PRIMARY KEY Constraint 733The UNIQUE Constraint 735The FOREIGN KEY Referential Integrity Constraint 736The CHECK Constraint 740Creating Constraints 742Managing Constraints 747Rules 750Defaults 751Declarative Defaults 751Bound Defaults 753When a Default Is Applied 754Restrictions on Defaults 755Summary 75624 Creating and Managing Views 757What's New in Creating and Managing Views 757Definition of Views 757Using Views 758Simplifying Data Manipulation 759Focusing on Specific Data 760Abstracting Data 761Controlling Access to Data 762Creating Views 764Creating Views Using T-SQL 765ENCRYPTION 767Creating Views Using the View Designer 769Managing Views 772Altering Views with T-SQL 772Dropping Views with T-SQL 773Managing Views with SSMS 773Data Modifications and Views 773Partitioned Views 774Modifying Data Through a Partitioned View 778Distributed Partitioned Views 779Indexed Views 780Creating Indexed Views 781Indexed Views and Performance 783To Expand or Not to Expand 786Summary 78725 Creating and Managing Stored Procedures 789What's New in Creating and Managing Stored Procedures 789Advantages of Stored Procedures 789Creating Stored Procedures 791Creating Procedures in SSMS 792Executing Stored Procedures 799Executing Procedures in SSMS 800Execution Context and the EXECUTE AS Clause 802Using the WITH RESULT SETS Clause 804Deferred Name Resolution 807Identifying the Objects Referenced Within Stored Procedures 809Viewing Stored Procedures 811Modifying Stored Procedures 814Viewing and Modifying Stored Procedures with SSMS 815Using Input Parameters 816Setting Default Values for Parameters 817Passing Object Names as Parameters 820Using Wildcards in Parameters 822Using Table-Valued Parameters 823Using Output Parameters 825Returning Procedure Status 826Debugging Stored Procedures Using SQL Server Management Studio 827Startup Procedures 830Natively Compiled Stored Procedures 834T-SQL Stored Procedure Coding Guidelines 838Summary 83926 Creating and Managing User-Defined Functions 841Why Use User-Defined Functions? 841Types of User-Defined Functions 844Scalar Functions 844Table-Valued Functions 847Creating and Managing User-Defined Functions 849Creating User-Defined Functions 849Viewing and Modifying User-Defined Functions 860Managing User-Defined Function Permissions 868Rewriting Stored Procedures as Functions 869Summary 87127 Creating and Managing Triggers 873What's New in Creating and Managing Triggers 874Using DML Triggers 874Creating DML Triggers 875Using AFTER Triggers 877Using inserted and deleted Tables 881INSTEAD OF Triggers 885Using DDL Triggers 893Creating DDL Triggers 897Managing DDL Triggers 901Using Nested Triggers 903Using Recursive Triggers 903Summary 90528 Transaction Management and the Transaction Log 907What's New in Transaction Management 907What Is a Transaction? 907How SQL Server Manages Transactions 908Defining Transactions 909AutoCommit Transactions 909Explicit User-Defined Transactions 910Implicit Transactions 916Implicit Transactions Versus Explicit Transactions 918Transactions and T-SQL Batches 919Transactions and Stored Procedures 921Transactions and Triggers 926Triggers and Transaction Nesting 927Triggers and Multistatement Transactions 930Using Savepoints in Triggers 931Transactions and Locking 933READ_COMMITTED_SNAPSHOT Isolation 934Coding Effective Transactions 934Transaction Logging and the Recovery Process 935The Checkpoint Process 939Automatic Checkpoints 941Indirect Checkpoints 942Manual Checkpoints 944The Recovery Process 945Managing the Transaction Log 947Long-Running Transactions 952Distributed Transactions 954Summary 95529 Database Snapshots 957What's New with Database Snapshots 958What Are Database Snapshots? 958Limitations and Restrictions of Database Snapshots 962Copy-on-Write Technology 964When to Use Database Snapshots 965Reverting to a Snapshot for Recovery Purposes 965Safeguarding a Database Prior to Making Mass Changes 966Providing a Testing (or Quality Assurance) Starting Point (Baseline) 967Providing a Point-in-Time Reporting Database 967Providing a Highly Available and Offloaded Reporting Database from a Database Mirror 968Setup and Breakdown of a Database Snapshot 970Creating a Database Snapshot 970Removing a Database Snapshot 974Reverting to a Database Snapshot for Recovery 975Reverting a Source Database from a Database Snapshot 975Database Snapshots Maintenance and Security Considerations 977Security for Database Snapshots 977Snapshot Sparse File Size Management 977Number of Database Snapshots per Source Database 977Summary 97830 Database Maintenance 979What's New in Database Maintenance 980The Maintenance Plan Wizard 980Backing Up Databases 983Checking Database Integrity 987Shrinking Databases 988Maintaining Indexes and Statistics 990Scheduling a Maintenance Plan 993Managing Maintenance Plans Without the Wizard 997Executing a Maintenance Plan 1001Maintenance Without a Maintenance Plan 1002Database Maintenance Policies 1003Summary 1003 Part V SQL Server Performance and Optimization 31 Understanding SQL Server Data Structures 1007What's New for Data Structures 1007Understanding Data Structures 1008Database Files and Filegroups 1008Primary Data File 1010Secondary Data Files 1010The Log File 1011File Management 1011Using Filegroups 1012FILESTREAM Filegroups 1015Database Pages 1017Page Types 1017Data Pages 1018Row-Overflow Pages 1024LOB Data Pages 1025Index Pages 1028Space Allocation Structures 1029Extents 1029Global and Shared Global Allocation Map Pages 1030Page Free Space Pages 1031Index Allocation Map Pages 1031Differential Changed Map Pages 1032Bulk Changed Map Pages 1032Data Compression 1033Row-Level Compression 1033Page-Level Compression 1035The CI Record 1038Implementing Page Compression 1038Evaluating Page Compression 1039Managing Data Compression with SSMS 1042Understanding Table Structures 1043Heap Tables 1045Clustered Tables 1047Understanding Index Structures 1048Clustered Indexes 1049Nonclustered Indexes 1052Columnstore Indexes 1057Data Modification and Performance 1062Inserting Data 1062Deleting Rows 1065Updating Rows 1066Summary 106832 Indexes and Performance 1069What's New for Indexes and Performance 1069Index Utilization 1070Index Selection 1072Evaluating Index Usefulness 1073Index Statistics 1076The Statistics Histogram 1078How the Statistics Histogram Is Used 1080Index Densities 1081Estimating Rows Using Index Statistics 1082Generating and Maintaining Index and Column Statistics 1085SQL Server Index Maintenance 1093Setting the Fill Factor 1103Reapplying the Fill Factor 1105Disabling Indexes 1106Managing Indexes with SSMS 1107Index Design Guidelines 1108Clustered Index Indications 1109Nonclustered Index Indications 1111Index Covering 1112Included Columns 1114Wide Indexes Versus Multiple Indexes 1115Indexed Views 1116Indexes on Computed Columns 1117Filtered Indexes and Statistics 1119Creating and Using Filtered Indexes 1120Creating and Using Filtered Statistics 1122Choosing Indexes: Query versus Update Performance 1124Identifying Missing Indexes 1125The Database Engine Tuning Advisor 1125Missing Index Dynamic Management Objects 1126Missing Index Feature Versus Database Engine Tuning Advisor 1128Identifying Unused Indexes 1129Summary 113133 In-Memory Optimization and the Buffer Pool Extension 1133Overview of In-Memory OLTP 1134In-Memory OLTP Concepts and Terminology 1136In-Memory Optimization Requirements 1137Limitations of In-Memory OLTP 1137Using In-Memory OLTP 1138Enabling a Database for In-Memory OLTP 1138Creating Memory-Optimized Tables 1140Memory-Optimized Tables Row Structure 1142Indexes on Memory-Optimized Tables 1143Garbage Collection 1151Maintaining Statistics on Memory-Optimized Tables 1153Memory-Optimized Index Design Guidelines 1154Using Memory-Optimized Tables 1156Interpreted T-SQL Support for In-Memory OLTP 1156Native Compilation 1157Natively Compiled Stored Procedures 1159Memory-Optimized Table Variables 1162Transactions and Memory-Optimized Tables 1162Monitoring Transactions on Memory-Optimized Tables 1170Logging, Checkpoint, and Recovery for In-Memory OLTP 1170Transaction Logging 1171Checkpoint 1171Recovery 1174Managing Memory for In-Memory OLTP 1175Monitoring Memory Usage 1176Managing Memory with the Resource Governor 1177Backup and Recovery of Memory-Optimized Databases 1178Migrating to In-Memory OLTP 1179Using the AMR Tool 1180Using the Table Memory Optimization Advisor to Migrate Disk-Based Tables 1181Dynamic Management Views for In-Memory OLTP 1183The Buffer Pool Extension 1185Summary 118634 Understanding Query Optimization 1187What's New in Query Optimization 1188What Is the Query Optimizer? 1188Query Compilation and Optimization 1189Compiling DML Statements 1189Optimization Steps 1190Query Analysis 1191Identifying Search Arguments 1191Identifying OR Clauses 1191Identifying Join Clauses 1192Row Estimation and Index Selection 1193Evaluating SARG and Join Selectivity 1193Estimating Access Path Cost 1199Using Multiple Indexes 1206Optimizing with Indexed Views 1213Optimizing with Filtered Indexes 1216Evaluating Cardinality Estimates 1218Join Selection 1219Join Processing Strategies 1219Determining the Optimal Join Order 1224Subquery Processing 1226Execution Plan Selection 1228Query Plan Caching 1231Query Plan Reuse 1231Query Plan Aging 1234Recompiling Query Plans 1234Monitoring the Plan Cache 1235Other Query Processing Strategies 1243Predicate Transitivity 1244GROUP BY Optimization 1244Queries with DISTINCT 1245Queries with UNION 1245
Responsibility: Ray Rankins, Paul T. Bertucci, Chris Gallelli, Alex T. Silverstein.


User-contributed reviews
Retrieving GoodReads reviews...
Retrieving DOGObooks reviews...


Be the first.

Similar Items

Related Subjects:(2)

Confirm this request

You may have already requested this item. Please select Ok if you would like to proceed with this request anyway.

Linked Data

Primary Entity

<> # Microsoft SQL Server 2014 unleashed
    a schema:CreativeWork, schema:Book, schema:MediaObject ;
    library:oclcnum "911517888" ;
    library:placeOfPublication <> ;
    schema:about <> ; # SQL server.
    schema:about <> ; # Database management
    schema:author <> ; # Chris Gallelli
    schema:author <> ; # Ray Rankins
    schema:author <> ; # Paul Bertucci
    schema:author <> ; # Alex Silverstein
    schema:bookFormat schema:EBook ;
    schema:datePublished "2015" ;
    schema:exampleOfWork <> ;
    schema:genre "Electronic books"@en ;
    schema:inLanguage "en" ;
    schema:name "Microsoft SQL Server 2014 unleashed"@en ;
    schema:productID "911517888" ;
    schema:url <> ;
    schema:url <> ;
    schema:url <> ;
    schema:url <> ;
    schema:workExample <> ;
    schema:workExample <> ;
    wdrs:describedby <> ;

Related Entities

<> # Paul Bertucci
    a schema:Person ;
    schema:familyName "Bertucci" ;
    schema:givenName "Paul" ;
    schema:name "Paul Bertucci" ;

<> # Chris Gallelli
    a schema:Person ;
    schema:familyName "Gallelli" ;
    schema:givenName "Chris" ;
    schema:name "Chris Gallelli" ;

<> # Ray Rankins
    a schema:Person ;
    schema:familyName "Rankins" ;
    schema:givenName "Ray" ;
    schema:name "Ray Rankins" ;

<> # Alex Silverstein
    a schema:Person ;
    schema:familyName "Silverstein" ;
    schema:givenName "Alex" ;
    schema:name "Alex Silverstein" ;

<> # Database management
    a schema:Intangible ;
    schema:name "Database management"@en ;

    a schema:ProductModel ;
    schema:isbn "0134084470" ;
    schema:isbn "9780134084473" ;

    a schema:ProductModel ;
    schema:isbn "0672337290" ;
    schema:isbn "9780672337291" ;

Content-negotiable representations

Close Window

Please sign in to WorldCat 

Don't have an account? You can easily create a free account.