Beginning SQL Server 2008 for Developers - From Novice to Professional

Contents at a Glance

Contents

About the Author

About the Technical Reviewer

Acknowledgments

Introduction

Who This Book Is For

How This Book Is Structured

Prerequisites

Downloading the Code

Contacting the Author

SQL Server 2008 Overview and Installation

Why SQL Server 2008?

Evolution of SQL Server

Hardware Requirements

The Example

Installation

Security

Summary

SQL Server Management Studio

A Quick Overview of SSMS

Examining SSMS’s Options

Query Editor

Summary

Database Design and Creation

Defining a Database

Prebuilt Databases Within SQL Server

Choosing the Database System Type

Gathering the Data

Determining the Information to Store in the Database

External and Ignored Information

Building Relationships

Normalization

Creating the Sample Database

Summary

Security and Compliance

Logins

Server Logins and Database Users

Roles

Schemas

Before You Can Proceed with Your Solution

Declarative Management Framework

Summary

Defining Tables

What Is a Table?

SQL Server Data Types

Columns Are More Than Simple Data Repositories

Image and Large Text Storage in SQL Server

Creating a Table in SQL Server Management Studio

Creating a Table Through the Query Editor

Creating a Table: Using a Template

Creating and Altering a Template

The ALTER TABLE Statement

Defining the Remaining Tables

Setting a Primary Key

Creating a Relationship

Building a Relationship via T-SQL

Summary

Creating Indexes and Database Diagramming

What Is an Index?

Determining What Makes a Good Index

Determining What Makes a Bad Index

Reviewing Your Indexes for Performance

Creating an Index

Dropping an Index

Altering an Index in Query Editor

When an Index Does Not Exist

Diagramming the Database

Summary

Database Backups, Recovery, and Maintenance

Transaction Logs

Backup Strategies

When Problems May Occur

Taking a Database Offline

Backing Up the Data

Restoring a Database

Detaching and Attaching a Database

Producing SQL Script for the Database

Maintaining Your Database

Creating a Database Maintenance Plan

Setting Up Database Mail

Modifying a Maintenance Plan

Summary

Working with the Data

The T- SQL INSERT Command Syntax

INSERT SQL Command

DBCC CHECKIDENT

Column Constraints

Inserting Several Records at Once

Retrieving Data

Using SQL Server Management Studio to Retrieve Data

The SELECT Statement

Naming the Columns

The First Searches

Varying the Output Display

Limiting the Search: the Use of WHERE

String Functions

Order! Order!

The LIKE Operator

Creating Data: SELECT INTO

Who Can Add, Delete, and Select Data

Updating Data

Transactions

Deleting Data

Truncating a Table

Dropping a Table

Summary

Building a View

Why a View?

Using Views for Security

Encrypting View Definitions

Creating a View: SQL Server Management Studio

Creating a View Using a View

CREATE VIEW Syntax

Creating a View: a Query Editor Pane

Creating a View: SCHEMABINDING

Indexing a View

Summary

Stored Procedures and Functions

What Is a Stored Procedure?

CREATE PROCEDURE Syntax

Returning a Set of Records

Creating a Stored Procedure: Management Studio

Different Methods of Executing

Using RETURN

Controlling the Flow

Bringing It All Together

User- Defined Functions

Summary

T-SQL Essentials

Using More Than One Table

Variables

Temporary Tables

Aggregations

Grouping Data

HAVING

Distinct Values

Functions

RAISERROR

Error Handling

@@ ERROR

TRY. . . CATCH

Summary

Advanced T-SQL

Subqueries

The APPLY Operator

Common Table Expressions

Pivoting Data

Ranking Functions

PowerShell Within SQL Server

Summary

Triggers

What Is a Trigger?

The DML Trigger

CREATE TRIGGER Syntax for DML Triggers

Why Not Use a Constraint?

Deleted and Inserted Logical Tables

Creating a DML FOR Trigger

Checking Specific Columns

DDL Triggers

Summary

SQL Server 2008 Reporting Services

Reporting Services Architecture

Configuring Reporting Services

Building Your First Report Using Report Designer

Summary

Index