Contents

Contents at a Glance

Foreword

About the Authors

About the Technical Reviewer

Acknowledgments

Introduction

Chapter 1: Getting Started

Installing SQL Server Express Edition

Installing the Sample Databases

Installing Books Online

Using Books Online

Using SQL Server Management Studio

Launching SQL Server Management Studio

Running Queries

Exploring Database Concepts

What Is SQL Server?

Service vs. Application

Database As Container

Data Is Stored in Tables

Data Types

Normalization

Understanding Indexes

Database Schemas

Summary

Chapter 2: Writing Simple SELECT Queries

Using the SELECT Statement

Selecting a Literal Value

Retrieving from a Table

Generating a Select-List

Mixing Literals and Column Names

Filtering Data

Adding a WHERE Clause

Using WHERE Clauses with Alternate Operators

Using BETWEEN

Using NOT BETWEEN

Filtering On Date and Time

Pattern Matching with LIKE

Restricting the Characters in Pattern Matches

Combining Wildcards

Using WHERE Clauses with Two Predicates

Using WHERE Clauses with Three or More Predicates

Using NOT with Parentheses

Using the IN Operator

Working with Nothing

Performing a Full-Text Search

Using CONTAINS

Using Multiple Terms with CONTAINS

Searching Multiple Columns

Using FREETEXT

Sorting Data

Thinking About Performance

Taking Advantage of Indexes

Viewing Execution Plans

Summary

Chapter 3: Using Functions and Expressions

Expressions Using Operators

Concatenating Strings

Concatenating Strings and NULL

CONCAT

ISNULL and COALESCE

Concatenating Other Data Types to Strings

Using Mathematical Operators

Data Type Precedence

Using Functions

Using String Functions

Nesting Functions

Using Date Functions

Using Mathematical Functions

System Functions

Using Functions in the WHERE and ORDER BY Clauses

The TOP Keyword

Ranking Functions

Using ROW_NUMBER

Using RANK and DENSE_RANK

Using NTILE

Thinking About Performance

Using Functions in the WHERE Clause

Summary

Chapter 4: Querying Multiple Tables

Writing INNER JOINS

Joining Two Tables

Avoiding an Incorrect Join Condition

Joining on a Different Column Name

Joining on More Than One Column

Joining Three or More Tables

Writing Outer Joins

Using LEFT OUTER JOIN

Using RIGHT OUTER JOIN

Using OUTER JOIN to Find Rows with No Match

Adding a Table to the Right Side of a Left Join

Adding a Table to the Left Side of a Left Join

FULL OUTER JOIN

CROSS JOIN

Self-Joins

Writing Subqueries

Using a Subquery in an IN List

Using a Subquery and NOT IN

Using a Subquery Containing NULL with NOT IN

Writing UNION Queries

Exploring Derived Tables and Common Table Expressions

Using Derived Tables

Using Common Table Expressions

Using a CTE to Solve a Complicated Join Problem

Thinking About Performance

Summary

Chapter 5: Grouping and Summarizing Data

Aggregate Functions

The GROUP BY Clause

Grouping on Columns

Grouping on Expressions

The ORDER BY Clause

The WHERE Clause

The HAVING Clause

DISTINCT

Using DISTINCT vs. GROUP BY

DISTINCT Within an Aggregate Expression

Aggregate Queries with More Than One Table

Isolating Aggregate Query Logic

Using a Correlated Subquery in the WHERE Clause

Inline Correlated Subqueries

Using Derived Tables

Common Table Expressions

Using Derived Tables and CTEs to Display Details

The OVER Clause

GROUPING SETS

CUBE and ROLLUP

Thinking About Performance

Summary

Chapter 6: Manipulating Data

Inserting New Rows

Adding One Row with Literal Values

Avoiding Common Insert Errors

Inserting Multiple Rows with One Statement

Inserting Rows from Another Table

Inserting Missing Rows

Creating and Populating a Table in One Statement

Inserting Rows into Tables with Default Column Values

Inserting Rows into Tables with Automatically Populating Columns

Deleting Rows

Using DELETE

Deleting from a Table Using a Join or a Subquery

Truncating

Updating Existing Rows

Using the UPDATE Statement

Updating Data with Expressions and Columns

Updating with a Join

Updating with Aggregate Functions

Using Transactions

Writing an Explicit Transaction

Rolling Back a Transaction

Locking Tables

Thinking About Performance

Database Cleanup

Summary

Chapter 7: Understanding T-SQL Programming Logic

Variables

Declaring and Initializing a Variable

Using Expressions and Functions with Variables

Using Variables in WHERE and HAVING Clauses

The IF… ELSE Construct

Using IF

Using ELSE

Using Multiple Conditions

Nesting IF…ELSE

Using IF EXISTS

WHILE

Using a WHILE Loop

Using ROWCOUNT

Nesting WHILE Loops

Exiting a Loop Early

Using CONTINUE

Error Handling

Using @@ERROR

Using GOTO

Using TRY … CATCH

Viewing Untrappable Errors

Using RAISERROR

Using TRY…CATCH with Transactions

Using THROW Instead of RAISERROR

Temporary Tables and Table Variables

Creating Local Temp Tables

Creating Global Temp Tables

Creating Table Variables

Using a Temp Table or Table Variable

Using a Temp Table or Table Variable as an Array

Using a Cursor

Thinking About Performance

Summary

Chapter 8: Working with XML

Converting XML into Data Using OPENXML

Retrieving Data as XML Using the FOR XML Clause

FOR XML RAW

FOR XML AUTO

FOR XML EXPLICIT

FOR XML PATH

XML Data Type

XML Methods

Query Method

The value() Method

The exist() Method

The modify() Method

Node Method

Summary

Chapter 9: Moving Logic to the Database

Tables

Adding Check Constraints to a Table

Adding UNIQUE Constraints

Adding a Primary Key to a Table

Creating Foreign Keys

Creating Foreign Keys with Delete and Update Rules

Defining Automatically Populated Columns

Views

Creating Views

Avoiding Common Problems with Views

Manipulating Data with Views

User-Defined Functions

Creating User-Defined Scalar Functions

Using Table-Valued User-Defined Functions

Stored Procedures

Using Default Values with Parameters

Using the OUTPUT Parameter

Saving the Results of a Stored Proc in a Table

Using a Logic in Stored Procedures

CLR Integration

User-Defined Data Types

Triggers

Thinking About Performance

Database Cleanup

Summary

Chapter 10: Working with Data Types

Large-Value String Data Types (MAX)

Large-Value Binary Data Types

Creating VARBINARY(MAX) Data

Using FILESTREAM

FileTables

Enhanced Date and Time

Using DATE, TIME, and DATETIME2

Using DATETIMEOFFSET

HIERARCHYID

Viewing HIERARCHYID

Creating a Hierarchy

Using Stored Procedures to Manage Hierarchical Data

Spatial Data Types

Using GEOMETRY

Using GEOGRAPHY

Viewing the Spatial Results Tab

Circular Arcs

Sparse Columns

Summary

Chapter 11: Writing Advanced Queries

Advanced CTE Queries

Using Multiple CTEs

Calling a CTE Multiple Times

Joining a CTE to Another CTE

Using the Alternate CTE Syntax

Writing a Recursive Query

The OUTPUT Clause

Using OUTPUT to View Data

Saving OUTPUT Data to a Table

The MERGE Statement

GROUPING SETS

Pivoted Queries

Pivoting Data with CASE

Using the PIVOT Function

Numbers Table

Database Cleanup

Summary

Chapter 12: Where to Go Next?

Online Resources

Conferences

User Groups

Vendors

Books

Classes

SQL Server Books Online

Practice, Practice, and More Practice

Teach Someone Else

Index