Microsoft SQL Server 2012 A Beginners Guide 5/E

Home >
Browse >
Microsoft SQL Server 2012 A Beginners Guide 5/E

Microsoft SQL Server 2012 A Beginners Guide 5/E

Authors: Dusan Petkovic

Published: March 2012

eISBN: 9780071761598 0071761594 | ISBN: 9780071761604

A Beginner’s Guide

About the Author

About the Technical Editor

Contents at a Glance

Contents

Acknowledgments

Introduction

Goals of the Book

SQL Server 2012 New Features Described in the Book

Organization of the Book

Changes from the Previous Edition

Differences Between SQL and Transact-SQL Syntax

Working with the Sample Databases

Part I: Basic Concepts and Installation

Chapter 1: Relational Database Systems: An Introduction

Database Systems: An Overview

Variety of User Interfaces

Physical Data Independence

Logical Data Independence

Query Optimization

Data Integrity

Concurrency Control

Backup and Recovery

Database Security

Relational Database Systems

Working with the Book’s Sample Database

SQL: A Relational Database Language

Database Design

Normal Forms

First Normal Form

Second Normal Form

Third Normal Form

Entity-Relationship Model

Syntax Conventions

Summary

Exercises

Chapter 2: Planning the Installation and Installing SQL Server

SQL Server Editions

Planning Phase

General Recommendations

Which SQL Server Components Should Be Installed?

Where Will the Root Directory Be Stored?

Should Multiple Instances of the Database Engine Be Used?

Which Authentication Mode for the Database Engine Should Be Used?

Planning the Installation

Hardware and Software Requirements

Security Documentation

Online Release Notes

Setup Documentation

System Configuration Checker

Installing SQL Server

Summary

Chapter 3: SQL Server Management Studio

Introduction to SQL Server Management Studio

Connecting to a Server

Registered Servers

Object Explorer

Organizing and Navigating SQL Server Management Studio’s Panes

Using SQL Server Management Studio with the Database Engine

Administering Database Servers

Registering Servers

Connecting to a Server

Creating a New Server Group

Managing Multiple Servers

Starting and Stopping Servers

Managing Databases Using Object Explorer

Creating Databases Without Using Transact-SQL

Modifying Databases Without Using Transact-SQL

Managing Tables Without Using Transact-SQL

Authoring Activities Using SQL Server Management Studio

Query Editor

Solution Explorer

SQL Server Debugging

Summary

Exercises

Part II: Transact-SQL Language

Chapter 4: SQL Components

SQL’s Basic Objects

Literal Values

Delimiters

Comments

Identifiers

Reserved Keywords

Data Types

Numeric Data Types

Character Data Types

Temporal Data Types

Miscellaneous Data Types

Binary and BIT Data Types

Large Object Data Types

UNIQUEIDENTIFIER Data Type

SQL_VARIANT Data Type

HIERARCHYID Data Type

TIMESTAMP Data Type

Storage Options

FILESTREAM Storage

Sparse Columns

Transact-SQL Functions

Aggregate Functions

Scalar Functions

Numeric Functions

Date Functions

System Functions

Metadata Functions

Scalar Operators

Global Variables

NULL Values

Summary

Exercises

Chapter 5: Data Definition Language

Creating Database Objects

Creation of a Database

Attaching and Detaching Databases

CREATE TABLE: A Basic Form

CREATE TABLE and Declarative Integrity Constraints

The UNIQUE Clause

The PRIMARY KEY Clause

The CHECK Clause

The FOREIGN KEY Clause

Referential Integrity

Possible Problems with Referential Integrity

The ON DELETE and ON UPDATE Options

Creating Other Database Objects

Integrity Constraints and Domains

Alias Data Types

CLR Data Types

Modifying Database Objects

Altering a Database

Modifying File or Filegroup Properties

Setting Database Options

Storing FILESTREAM Data

Contained Databases

Altering a Table

Adding or Dropping a New Column

Modifying Column Properties

Adding or Removing Integrity Constraints

Enabling or Disabling Constraints

Renaming Tables and Other Database Objects

Removing Database Objects

Summary

Exercises

Chapter 6: Queries

SELECT Statement: Its Clauses and Functions

WHERE Clause

Boolean Operators

IN and BETWEEN Operators

Queries Involving NULL Values

GROUP BY Clause

Aggregate Functions

Convenient Aggregate Functions

Statistical Aggregate Functions

User-Defined Aggregate Functions

HAVING Clause

ORDER BY Clause

Using ORDER BY to Support Paging

SELECT Statement and IDENTITY Property

CREATE SEQUENCE Statement

Set Operators

UNION Set Operator

INTERSECT and EXCEPT Set Operators

CASE Expressions

Subqueries

Subqueries and Comparison Operators

Subqueries and the IN Operator

Subqueries and ANY and ALL Operators

Temporary Tables

Join Operator

Two Syntax Forms to Implement Joins

Natural Join

Joining More Than Two Tables

Cartesian Product

Outer Join

Further Forms of Join Operations

Theta Join

Self-Join, or Joining a Table with Itself

Semi-Join

Correlated Subqueries

Subqueries and the EXISTS Function

Should You Use Joins or Subqueries?

Subquery Advantages

Join Advantages

Table Expressions

Derived Tables

Common Table Expressions

CTEs and Nonrecursive Queries

CTEs and Recursive Queries

Summary

Exercises

Chapter 7: Modification of a Table’s Contents

INSERT Statement

Inserting a Single Row

Inserting Multiple Rows

Table Value Constructors and INSERT

UPDATE Statement

DELETE Statement

Other T-SQL Modification Statements and Clauses

TRUNCATE TABLE Statement

MERGE Statement

The OUTPUT Clause

Summary

Exercises

Chapter 8: Stored Procedures and User-Defined Functions

Procedural Extensions

Block of Statements

IF Statement

WHILE Statement

Local Variables

Miscellaneous Procedural Statements

Exception Handling with TRY, CATCH, and THROW

Stored Procedures

Creation and Execution of Stored Procedures

The EXECUTE Statement with RESULT SETS Clause

Changing the Structure of Stored Procedures

Stored Procedures and CLR

User-Defined Functions

Creation and Execution of User-Defined Functions

Invoking User-Defined Functions

Table-Valued Functions

Table-Valued Parameters

Changing the Structure of UDFs

User-Defined Functions and CLR

Summary

Exercises

Chapter 9: System Catalog

Introduction to the System Catalog

General Interfaces

Catalog Views

Querying Catalog Views

Dynamic Management Views and Functions

Information Schema

Information_schema.tables

Information_schema.columns

Proprietary Interfaces

System Stored Procedures

System Functions

Property Functions

Summary

Exercises

Chapter 10: Indices

Introduction

Clustered Indices

Nonclustered Indices

Transact-SQL and Indices

Creating Indices

Obtaining Index Fragmentation Information

Editing Index Information

Altering Indices

Rebuilding an Index

Reorganizing Leaf Index Pages

Disabling an Index

Removing and Renaming Indices

Guidelines for Creating and Using Indices

Indices and Conditions in the WHERE Clause

Indices and the Join Operator

Covering Index

Special Types of Indices

Virtual Computed Columns

Persistent Computed Columns

Summary

Exercises

Chapter 11: Views

DDL Statements and Views

Creating a View

Altering and Removing Views

Editing Information Concerning Views

DML Statements and Views

View Retrieval

INSERT Statement and a View

UPDATE Statement and a View

DELETE Statement and a View

Indexed Views

Creating an Indexed View

Modifying the Structure of an Indexed View

Editing Information Concerning Indexed Views

Benefits of Indexed Views

Summary

Exercises

Chapter 12: Security System of the Database Engine

Authentication

Implementing an Authentication Mode

Encrypting Data

Symmetric Keys

Asymmetric Keys

Editing User Keys

SQL Server Extensible Key Management

Methods of Data Encryption

Setting Up the Database Engine Security

Managing Security Using Management Studio

Managing Security Using Transact-SQL Statements

Schemas

User-Schema Separation

DDL Schema-Related Statements

Create Schema

Alter Schema

Drop Schema

Database Security

Managing Database Security Using Management Studio

Managing Database Security Using Transact-SQL Statements

Default Database Schemas

Roles

Fixed Server Roles

Managing Fixed Server Roles

Fixed Database Roles

public Role

Assigning a User to a Fixed Database Role

Application Roles

Managing Application Roles Using Management Studio

Managing Application Roles Using T-SQL

Activating Application Roles

User-Defined Server Roles

User-Defined Database Roles

Managing User-Defined Database Roles Using Management Studio

Managing User-Defined Database Roles Using T-SQL

Authorization

GRANT Statement

DENY Statement

REVOKE Statement

Managing Permissions Using Management Studio

Managing Authorization and Authentication of Contained Databases

Change Tracking

Data Security and Views

Summary

Exercises

Chapter 13: Concurrency Control

Concurrency Models

Transactions

Properties of Transactions

Transact-SQL Statements and Transactions

Transaction Log

Locking

Lock Modes

Lock Granularity

Lock Escalation

Affecting Locks

Locking Hints

LOCK_TIMEOUT Option

Displaying Lock Information

Deadlock

Isolation Levels

Concurrency Problems

The Database Engine and Isolation Levels

Read Uncommitted

Read Committed

Repeatable Read

Serializable

Setting and Editing Isolation Levels

Row Versioning

READ COMMITTED SNAPSHOT Isolation Level

SNAPSHOT Isolation Level

Read Committed Snapshot vs. Snapshot

Summary

Exercises

Chapter 14: Triggers

Introduction

Creating a DML Trigger

Modifying a Trigger’s Structure

Using deleted and inserted Virtual Tables

Application Areas for DML Triggers

AFTER Triggers

Creating an Audit Trail

Implementing Business Rules

Enforcing Integrity Constraints

INSTEAD OF Triggers

First and Last Triggers

DDL Triggers and Their Application Areas

Database-Level Triggers

Server-Level Triggers

Triggers and CLR

Summary

Exercises

Part III: SQL Server: System Administration

Chapter 15: System Environment of the Database Engine

System Databases

master Database

model Database

tempdb Database

msdb Database

Disk Storage

Properties of Data Pages

Page Header

Space Reserved for Data

Row Offset Table

Types of Data Pages

In-Row Data Pages

Row-Overflow Data

Parallel Processing of Tasks

Utilities and the DBCC Command

bcp Utility

sqlcmd Utility

sqlservr Utility

DBCC Command

Validation Commands

Policy-Based Management

Key Terms and Concepts

Using Policy-Based Management

Summary

Exercises

Chapter 16: Backup, Recovery, and System Availability

Reasons for Data Loss

Introduction to Backup Methods

Full Database Backup

Differential Backup

Transaction Log Backup

File or Filegroup Backup

Performing Database Backup

Backing Up Using Transact-SQL Statements

Types of Backup Devices

BACKUP DATABASE Statement

BACKUP LOG Statement

Backing Up Using Management Studio

Scheduling Backups with Management Studio

Determining Which Databases to Back Up

Backing Up the master Database

Backing Up Production Databases

Performing Database Recovery

Automatic Recovery

Manual Recovery

Is My Backup Set Ready for Recovery?

Restoring Databases and Logs Using Transact-SQL Statements

Restoring Databases and Logs Using Management Studio

Recovering to a Mark

Restoring the master Database

Restoring Other System Databases

Recovery Models

Full Recovery Model

Bulk-Logged Recovery Model

Simple Recovery Model

Changing and Editing a Recovery Model

System Availability

Using a Standby Server

Using RAID Technology

RAID 0 (Disk Striping)

RAID 1 (Mirroring)

RAID 5 (Parity)

Database Mirroring

Failover Clustering

Log Shipping

High-Availability and Disaster Recovery (HADR)

Availability Groups, Replicas, and Modes

Configuration of HADR

Maintenance Plan Wizard

Summary

Exercises

Chapter 17: Automating System Administration Tasks

Starting SQL Server Agent

Creating Jobs and Operators

Creating a Job and Its Steps

Creating a Job Schedule

Notifying Operators About the Job Status

Viewing the Job History Log

Alerts

Error Messages

SQL Server Agent Error Log

Windows Application Log

Defining Alerts to Handle Errors

Creating Alerts on System Errors

Creating Alerts on Error Severity Levels

Creating Alerts on User-Defined Errors

Summary

Exercises

Chapter 18: Data Replication

Distributed Data and Methods for Distributing

SQL Server Replication: An Overview

Publishers, Distributors, and Subscribers

Publications and Articles

The Distribution Database

Agents

Snapshot Agent

Log Reader Agent

Distribution Agent

Merge Agent

Replication Types

Transactional Replication

Peer-to-Peer Transactional Replication

Snapshot Replication

Merge Replication

Replication Models

Central Publisher with Distributor

Central Publisher with a Remote Distributor

Central Subscriber with Multiple Publishers

Multiple Publishers with Multiple Subscribers

Managing Replication

Configuring the Distribution and Publication Servers

Setting Up Publications

Configuring Subscription Servers

Summary

Exercises

Chapter 19: Query Optimizer

Phases of Query Processing

How Query Optimization Works

Query Analysis

Index Selection

Selectivity of an Expression with the Indexed Column

Index Statistics

Column Statistics

Join Order Selection

Join Processing Techniques

Nested Loop

Merge Join

Hash Join

Plan Caching

Influencing Execution Plans

Displaying Information Concerning the Plan Cache

Tools for Editing the Optimizer Strategy

SET Statement

Textual Execution Plan

XML Execution Plan

Other Options of the SET Statement

Management Studio and Graphical Execution Plans

Examples of Execution Plans

Dynamic Management Views and Query Optimizer

sys.dm_exec_query_plan

sys.dm_exec_query_stats

sys.dm_exec_sql_text and sys.dm_exec_text_query_plan

sys.dm_exec_procedure_stats

sys.dm_exec_cached_plans

Optimization Hints

Why Use Optimization Hints

Types of Optimization Hints

Table Hints

Join Hints

Query Hints

Plan Guides

Summary

Chapter 20: Performance Tuning

Factors That Affect Performance

Database Applications and Performance

Application-Code Efficiency

Physical Design

The Database Engine and Performance

Query Optimizer

Locks

System Resources and Performance

Disk I/O

Memory

Monitoring Performance

Performance Monitor: An Overview

Monitoring the CPU

Monitoring the CPU Using Counters

Monitoring the CPU Using Views

Monitoring Memory

Monitoring Memory Using Counters

Monitoring Memory Using Dynamic Management Views

Monitoring Memory Using the DBCCMEMORYSTATUS Command

Monitoring the Disk System

Monitoring the Disk System Using Counters

Monitoring the Disk System Using DMVs

Monitoring the Network Interface

Monitoring the Network Interface Using Counter

Monitoring the Network Interface Using a DMV

Monitoring the Network Interface Using a System Procedure

Choosing the Right Tool for Monitoring

SQL Server Profiler

Database Engine Tuning Advisor

Providing Information for the Database Engine Tuning Advisor

Working with the Database Engine Tuning Advisor

Other Performance Tools of SQL Server

Performance Data Collector

Creating the MDW

Setting Up Data Collection

Viewing Reports

Resource Governor

Creation of Workload and Resource Groups

Monitoring Configuration of Resource Governor

Summary

Exercises

Part IV: SQL Server and Business Intelligence

Chapter 21: Business Intelligence: An Introduction

Online Transaction Processing vs. Business Intelligence

Online Transaction Processing

Business Intelligence Systems

Data Warehouses and Data Marts

Data Warehouse Design

Cubes and Their Architectures

Aggregation

How Much to Aggregate?

Physical Storage of a Cube

Data Access

Summary

Exercises

Chapter 22: SQL Server Analysis Services

SSAS Terminology

Developing a Multidimensional Cube Using BIDS

Create a BI Project

Identify Data Sources

Specify Data Source Views

Create a Cube

Design Storage Aggregation

Process the Cube

Browse the Cube

Retrieving and Delivering Data

Querying Data Using PowerPivot for Excel

Working with PowerPivot for Excel

Querying Data Using Multidimensional Expressions

Security of SQL Server Analysis Services

Summary

Exercises

Chapter 23: Business Intelligence and Transact-SQL

Window Construct

Partitioning

Ordering and Framing

Extensions of GROUP BY

CUBE Operator

ROLLUP Operator

Grouping Functions

GROUPING Function

GROUPING_ID Function

Grouping Sets

OLAP Query Functions

Ranking Functions

Statistical Aggregate Functions

Standard and Nonstandard Analytic Functions

TOP Clause

OFFSET/FETCH

NTILE Function

Pivoting Data

PIVOT Operator

UNPIVOT Operator

Summary

Exercises

Chapter 24: SQL Server Reporting Services

Introduction to Data Reports

SQL Server Reporting Services Architecture

Reporting Services Windows Service

The Report Catalog

Report Manager

Configuration of SQL Server Reporting Services

Creating Reports

Creating Reports with the Report Server Project Wizard

Planning Data Sources and Datasets

Selecting a Data Source

Designing a Query

Choosing the Report Type

Designing the Data in the Table

Specifying the Report Layout

Choosing the Report Style

Choosing the Deployment Location and Completing the Wizard

Previewing the Result Set

Deploying the Report

Creating Parameterized Reports

Managing Reports

On-Demand Reports

Report Subscription

Standard Subscriptions

Data-Driven Subscriptions

Report Delivery Options

Cached Reports

Execution Snapshots

Summary

Exercises

Chapter 25: Optimizing Techniques for Relational Online Analytical Processing

Data Partitioning

How the Database Engine Partitions Data

Steps for Creating Partitioned Tables

Set Partition Goals

Determine the Partition Key and Number of Partitions

Create a Filegroup for Each Partition

Create the Partition Function and Partition Scheme

Create Partitioned Indices

Partitioning Techniques for Increasing System Performance

Table Collocation

Partition-Aware Seek Operation

Parallel Execution of Queries

Guidelines for Partitioning Tables and Indices

Star Join Optimization

Columnstore Index

Managing Columnstore Index

Creating a Columnstore Index Using Transact-SQL

Creating a Columnstore Index Using SSMS

Advantages and Limitations of Columnstore Indices

Benefits of Columnstore Indices

Limitations of Columnstore Index

Summary

Part V: Beyond Relational Data

Chapter 26: SQL Server and XML

XML: Basic Concepts

Requirements of a Well-Formed XML Document

XML Elements

XML Attributes

XML Namespaces

XML and World Wide Web

XML-Related Languages

Schema Languages

Document Type Definition

XML Schema

Storing XML Documents in SQL Server

Storing XML Documents Using the XML Data Type

Indexing an XML Column

Typed vs. Untyped XML

Typed XML Columns, Variables, and Parameters

Storing XML Documents Using Decomposition

Presenting Data

Presenting XML Documents as Relational Data

Presenting Relational Data as XML Documents

RAW Mode

AUTO Mode

EXPLICIT Mode

PATH Mode

Directives

Querying Data

Summary

Chapter 27: Spatial Data

Introduction

Models for Representing Spatial Data

GEOMETRY Data Type

GEOGRAPHY Data Type

GEOMETRY vs. GEOGRAPHY

External Data Formats

Working with Spatial Data Types

Working with the GEOMETRY Data Type

Working with the GEOGRAPHY Data Type

Working with Spatial Indices

Displaying Information Concerning Spatial Data

New Spatial Data Features in SQL Server 2012

New Subtypes of Circular Arcs

Circular String

Compound Curve

Curve Polygons

New Spatial Indices

The Geometry Auto Grid Index

New System Stored Procedures Concerning Spatial Data

Summary

Chapter 28: SQL Server Full-Text Search

Introduction

Tokens, Word Breakers, and Stop Lists

Word Breakers and IFilters

Stop Lists

Operations on Tokens

Extended Operations on Words

Matching Options

Proximity Operations

Relevance Score

How SQL Server FTS Works

Indexing Full-Text Data

Indexing Full-Text Data Using Transact-SQL

Create a Unique Index

Enable a Database for Full-Text Indexing

Create a Full-Text Catalog

Create a Full-Text Index

Index Full-Text Data Using SQL Server Management Studio

Querying Full-Text Data

FREETEXT Predicate

CONTAINS Predicate

FREETEXTTABLE Function

CONTAINSTABLE Function

Troubleshooting Full-Text Data

New Features in SQL Server 2012 FTS

Customizing a Proximity Search

Searching Extended Properties

Summary

Index