Microsoft SQL Server 2008 Step by Step

0.0

This book does not come with a CD This hands-on, step-by-step guide to learning the fundamentals of SQL Server 2008 programming teaches how to build database solutions that solve real-world business problems. Discover how to: Install and work with core components and tools Create tables and index structures Manipulate and retrieve data Secure, manage, back up, and recover databases Apply tuning plus optimization techniques to generate high-performing database applications Tap business intelligence tools—Reporting, Analysis, and Integration Services Table of Contents Acknowledgments Introduction Part I Getting Started with Microsoft SQL Server 2008 1 Overview of Microsoft SQL Server Database Engine Storage Engine Security Subsystem Programming Interfaces Service Broker SQL Server Agent Replication High Availability The Relational Engine in SQL Server 2008 Business Intelligence Integration Services Reporting Services Analysis Services Chapter 1 Quick Reference 2 Installing and Confi guring SQL Server 2008 Editions of SQL Server 2008 Infrastructure Requirements Service Accounts Collation Sequences Authentication Modes SQL Server Instances Upgrading to SQL Server 2008 In-Place Upgrade Side-by-Side Upgrade Installing SQL Server 2008 Install Sample Databases Chapter 2 Quick Reference 3 Using the Tools in SQL Server 2008 SQL Server Documentation Management Tools in SQL Server 2008 SQL Server Confi guration Manager SQL Server Management Studio Database Mail Performance Management Tools Profi ler Database Engine Tuning Advisor Performance Studio Business Intelligence Tools Business Intelligence Development Studio Chapter 3 Quick Reference Part II Designing Databases 4 Creating Databases SQL Server System Databases SQL Server Database Structure Database Files Filegroups Creating a Database Moving Databases Detaching a Database Attaching a Database Chapter 4 Quick Reference 5 Designing Tables Naming Objects Schemas Data Types Numeric Data Character Data Date and Time Data Binary Data XML FILESTREAM Data Spatial Data Type HierarchyID Data Type Column Properties Creating Tables Computed Columns Sparse Columns Constraints Primary Keys Unique Constraints Check Constraints Default Constraints Foreign Keys Database Diagrams Chapter 5 Quick Reference 6 Indexes Index Structure Balanced Trees (B-trees) Index levels Clustered Indexes Nonclustered Indexes Index Maintenance Included Columns Covering Indexes Filtered Indexes Online Index Creation Index Management and Maintenance Index Fragmentation Fill Factor Defragmenting an Index Disabling an index XML Indexes Spatial Indexes Chapter 6 Quick Reference 7 Partitioning Partition Functions Partition Schemes Filegroups Partitioning Tables and Indexes Partial Backup and Restore Creating a Partitioned Index Managing Partitions SPLIT and MERGE Operators Altering a Partition Scheme Index Alignment SWITCH Operator Chapter 7 Quick Reference Part III Retrieving and Manipulating Data 8 Data Retrieval General SELECT Statement Sorting Results Filtering Data Retrieving from More Than One Table Retrieving Unique Results Chapter 8 Quick Reference 9 Advanced Data Retrieval General SELECT Statement Aggregating Data Aggregating Multiple Permutations Filtering Aggregates Running Aggregates Calculating Pivot Tables Ranking Data Aggregating Result Sets Common Table Expressions Querying XML Data Chapter 9 Quick Reference 10 Data Manipulation Inserting Data INSERT SELECT INTO Updating Data Deleting Data TRUNCATE MERGE Statement OUTPUT Clause Transaction Handling Tracking Changes Change Tracking Change Data Capture Chapter 10 Quick Reference Part IV Designing Advanced Database Objects 11 Views Creating a View Query Substitution Modifying Data Through a View Creating an Indexed View Query Substitution Chapter 11 Quick Reference 12 Stored Procedures Creating Stored Procedures Commenting Code Variables, Parameters, and Return Codes Variables Parameters Return Codes Executing Stored Procedures Control Flow Constructs Error Handling Dynamic Execution Cursors CLR Procedures Building an Administrative Procedure Chapter 12 Quick Reference 13 Functions System Functions Creating a Function Retrieving Data from a Function Chapter 13 Quick Reference 14 Triggers DML Triggers DDL Triggers Chapter 14 Quick Reference 15 Database Snapshots Creating a Database Snapshot Copy-On-Write Technology Reverting Data Using a Database Snapshot Chapter 15 Quick Reference 16 Service Broker Service Broker Architecture Messaging Overview Service Broker Components Application Interaction Message Types and Contracts Message Types Contracts Queues and Services Queues Services Conversations Sending and Receiving Messages Sending Messages Receiving Messages Queue Activation Prioritization Chapter 16 Quick Reference 17 Full-Text Indexing Full-Text Catalogs Full-Text Indexes Querying Full-Text Data FREETEXT CONTAINS Part V Database Management 18 Security Confi guring the Attack Surface Endpoints Endpoint Types and Payloads Endpoint Access TCP Endpoints Principals, Securables, and Permissions Principals Impersonation Securables Permissions Ownership Chains CLR Security Data Encryption Master Keys Hash Algorithms Symmetric Keys Certifi cates and Asymmetric Keys Transparent Data Encryption Encryption Key Management Chapter 18 Quick Reference 19 Policy-Based Management Overview of Policy-Based Management Facets Conditions Policy Targets Policies Policy Categories Policy Compliance Chapter 19 Quick Reference 20 Data Recovery Database Backups Backup Types Page Corruption Recovery Models Database Restores Restoring a Full Backup Restoring a Differential Backup Restoring a Transaction Log Backup Chapter 20 Quick Reference 21 SQL Server Agent Creating Jobs Jobs Steps Job Schedules Operators Creating Maintenance Plans Creating Alerts Chapter 21 Quick Reference 22 Dynamic Management Views Overview of DMVs Retrieving Object Metadata Database Diagnostics Object Size Indexes Query Execution Statistics Chapter 22 Quick Reference Part VI High Availability Overview 23 High Availability Failover Clustering Failover Cluster Instance Components Network Confi guration Disk Confi guration Security Confi guration Health Checks Cluster Failover Database Mirroring Database Mirroring Roles Database Mirroring Endpoints Operating Modes Caching Transparent Client Redirect Corrupt Pages Database Snapshots Initializing Database Mirroring Log Shipping Log Shipping Components Log Shipping Initialization Replication Replication Components Replication Roles Replication Agents Replication Methods Chapter 23 Quick Reference Part VII Business Intelligence 24 SQL Server Integration Services BIDS Overview Tasks Transforms Building a Package Connections Control Flow Data Flow Data Conversion Exception Handling Confi guration Deployment Chapter 24 Quick Reference 25 SQL Server Reporting Services Confi guring Reporting Services Reporting Services Web Site Creating Reports Building a Report Formatting Computations Interactive Elements Parameters Deploy Reports Report Subscriptions Linked Reports Report Caching and Snapshots Chapter 25 Quick Reference 26 SQL Server Analysis Services Data Warehousing Overview Online Analytic Processing (OLAP) Dimensional Model Cubes Dimensions, Measures, and Calculations Hierarchies KPIs, Partitions, Perspectives, and Translations Key Performance Indicators (KPIs) Partitions Perspectives Translations Data Mining Algorithms Mining Models and Mining Structures Data Mining Demystifi ed Chapter 26 Quick Reference Index