Contents

About the Authors

About the Technical Reviewer

Acknowledgments

Foreword

 Chapter 1: Getting Started

Installing SQL Server Express Edition

Installing Books Online

Using Books Online

Using SQL Server Management Studio

Launching SQL Server Management Studio

Installing the Sample Databases

Get Started with SSMS

Summary

 Chapter 2: Exploring Database Concepts

What Is SQL Server?

Databases in the Cloud

Service vs Application

Database as Container

Data Is Stored in Tables

Data Types

Normalization

Understanding Indexes

Database Schemas

Summary

 Chapter 3: 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 BETWEEN with NOT

Filtering on Date and Time

Using WHERE Clauses with Two Predicates

Using the IN Operator

Working with NULL

Sorting Data

Thinking About Performance

Taking Advantage of Indexes

Viewing Execution Plans

Summary

Answers to the Exercises

Solutions to Exercise 3-1: Using the SELECT Statement

Solutions to Exercise 3-2: Filtering Data

Solutions to Exercise 3-3: Using WHERE Clauses with Two Predicates

Solutions to Exercise 3-4: Working with NULL

Solutions to Exercise 3-5: Sorting Data

 Chapter 4: Using Built-In 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

Using String Functions

RTRIM and LTRIM

LEFT and RIGHT

LEN and DATALENGTH

CHARINDEX

SUBSTRING

CHOOSE

REVERSE

UPPER and LOWER

REPLACE

Nesting Functions

Using Date and Time Functions

GETDATE and SYSDATETIME

DATEADD

DATEDIFF

DATENAME and DATEPART

DAY, MONTH, and YEAR

CONVERT

FORMAT

DATEFROMPARTS

EOMONTH

Using Mathematical Functions

ABS

POWER

SQUARE and SQRT

ROUND

RAND

Logical Functions and Expressions

The CASE Expression

IIF

COALESCE

Administrative Functions

Using Functions in the WHERE and ORDER BY Clauses

The TOP Keyword

Thinking About Performance

Summary

Answers to the Exercises

Solutions to Exercise 4-1: Expressions Using Operators

Solutions to Exercise 4-2: Using Mathematical Operators

Solution to Exercise 4-3: Using Functions

Solution to Exercise 4-4: Using Date and Time Functions

Solution to Exercise 4-5: Using Mathematical Functions

Solution to Exercise 4-6: Using Logical and System Functions

Solution to Exercise 4-7: Using Functions in the WHERE and ORDER BY Clauses

 Chapter 5: Joining Tables

Using INNER JOIN

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

Using OUTER JOIN

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 Main Table of a LEFT JOIN

FULL OUTER JOIN

CROSS JOIN

Self-Joins

Thinking About Performance

Merge Join

Nested Loop

Hash Match

Summary

Answers to the Exercises

Solutions to Exercise 5-1: Using INNER JOIN

Solutions to Exercise 5-2: Using OUTER JOIN

 Chapter 6: Building on Subqueries, Common Table Expressions, and Unions

Writing Subqueries

Using a Subquery in an IN List

Using a Subquery and NOT IN

Using a Subquery Containing NULL with NOT IN

Using EXISTS

Using CROSS APPLY and OUTER APPLY

Writing UNION Queries

Using EXCEPT and INTERSECT

Using Derived Tables and Common Table Expressions

Using Derived Tables

Using Common Table Expressions

Using a Common Table Expression to Solve a Complicated Join Problem

Thinking About Performance

Summary

Answers to the Exercises

Solutions to Exercise 6-1: Using Subqueries

Solutions to Exercise 6-2: Using Derived Tables and Common Table Expressions

 Chapter 7: 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 Keyword

Using DISTINCT vs GROUP BY

DISTINCT Within an Aggregate Expression

Aggregate Queries with More Than One Table

Aggregate Functions and NULL

Thinking About Performance

Summary

Answers to the Exercises

Solutions to Exercise 7-1: Aggregate Functions

Solutions to Exercise 7-2: The GROUP BY Clause

Solutions to Exercise 7-3: The HAVING Clause

Solutions to Exercise 7-4: DISTINCT Keyword

Solutions to Exercise 7-5: Aggregate Queries with More Than One Table

 Chapter 8: Discovering Windowing Functions

What Is a Windowing Function?

Ranking Functions

Defining the Window

Dividing the Window into Partitions

Using NTILE

Summarizing Results with Window Aggregates

Defining the Window with Framing

Calculating Running Totals

Understanding the Difference Between ROWS and RANGE

Using Window Analytic Functions

LAG and LEAD

FIRST_VALUE and LAST_VALUE

PERCENT_RANK and CUME_DIST

PERCENTILE_CONT and PERCENTILE_DISC

Applying Windowing Functions

Removing Duplicates

Solving an Islands Problem

Thinking About Performance

Indexing

The Trouble with Window Aggregates

Framing

Summary

Answers to the Exercises

Solutions to Exercise 8-1: Ranking Functions

Solutions to Exercise 8-2: Summarizing Results with Window Aggregates

Solutions to Exercise 8-3: Understanding the Difference Between ROWS and RANGE

Solutions to Exercise 8-4: Using Window Analytic Functions

 Chapter 9: Advanced WHERE Clauses

Pattern Matching

Using LIKE

Restricting the Characters in Pattern Matches

Searching for Wildcards

Combining Wildcards

Using PATINDEX

Using WHERE Clauses with Three or More Predicates

Using NOT with Parentheses

Performing a Full-Text Search

Using CONTAINS

Using Multiple Terms with CONTAINS

Searching Multiple Columns

Using FREETEXT

Thinking About Performance

Summary

Answers to the Exercises

Solutions to Exercise 9-1: Using LIKE

Solution to Exercise 9-2: Using WHERE Clauses with Three or More Predicates

Solution to Exercise 9-3: Performing a Full-Text Search

 Chapter 10: 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 EXISTS

Truncating

Updating Existing Rows

Using the UPDATE Statement

Updating Data with Expressions and Columns

Updating with a Join

Thinking About Performance

Database Cleanup

Summary

Answers to the Exercises

Solution to Exercise 10-1: Inserting New Rows

Solution to Exercise 10-2: Deleting Rows

Solution to Exercise 10-3: Updating Existing Rows

 Chapter 11: Writing Advanced Queries

Advanced CTE Queries

Alternate CTE Syntax

Using Multiple CTEs

Referencing a CTE Multiple Times

Joining a CTE to Another CTE

Writing a Recursive Query

Data Manipulation with CTEs

Isolating Aggregate Query Logic

Correlated Subqueries in the SELECT list

Using Derived Tables

Common Table Expressions

Using CROSS APPLY and OUTER APPLY

The OUTPUT Clause

Using OUTPUT to View Data

Saving OUTPUT Data to a Table

The MERGE Statement

GROUPING SETS

CUBE and ROLLUP

Pivoted Queries

Pivoting Data with CASE

Using the PIVOT Function

Using the UNPIVOT Function

Paging

Summary

 Chapter 12: 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 with a Query

WHILE

Using a WHILE Loop

Nesting WHILE Loops

Exiting a Loop Early

Using CONTINUE

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 Like an Array

Using a Cursor

Thinking About Performance

Summary

Answers to the Exercises

Solutions to Exercise 12-1: Variables

Solutions to Exercise 12-2: The IF ... ELSE Construct

Solutions to Exercise 12-3: WHILE

Solutions to Exercise 12-4: Temporary Tables and Table Variables

 Chapter 13: Managing Transactions

ACID Properties

Writing an Explicit Transaction

Rolling Back a Transaction

Using the XACT_ABORT Setting

Error Handling

Using TRY ... CATCH

Viewing Untrappable Errors

Using RAISERROR

Using TRY ... CATCH with Transactions

Using THROW Instead of RAISERROR

Thinking About Performance

Summary

Answers to the Exercises

Solutions to Exercise 13-1: Writing an Explicit Transaction

Solutions to Exercise 13-2: Error Handling

 Chapter 14: Implementing Logic in 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 Procedure in a Table

Using a Logic in Stored Procedures

User-Defined Data Types

Table Types

Triggers

CLR Integration

Thinking About Performance

Database Cleanup

Summary

Answers to the Exercises

Solutions to Exercise 14-1: Tables

Solution to Exercise 14-2: Views

Solution to Exercise 14-3: User-Defined Functions

Solution to Exercise 14-4: Stored Procedures

 Chapter 15: Working with XML

The Parts of XML

Converting XML Using OPENXML

Retrieving Data as XML Using the FOR XML Clause

FOR XML RAW

FOR XML AUTO

FOR XML EXPLICIT

FOR XML PATH

The XML Data Type

XML Methods

The QUERY Method

The VALUE Method

The EXIST Method

The MODIFY Method

The NODES Method

Namespaces

Splitting a String

Summary

 Chapter 16: Expanding on Data Type Concepts

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 DATETIME 2

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

Thinking About Performance

Summary

 Chapter 17: Running SQL Server in the Cloud

Procuring a Microsoft Azure Account

The Azure Dashboard

Windows Azure Virtual Machines

Azure SQL Database

Throttling

Database Size Limitations

Pricing

Summary

 Chapter 18: Where to Go Next?

Online Resources

Conferences

User Groups

Vendors

Books

Classes

SQL Server Documentation

Practice, Practice, and More Practice

Teach Someone Else

Index