Introduction to SQL Server Management Studio

• Identifying the Elements of SQL Server Management Studio
• Objects in a SQL Server Database
• Database Diagrams
• Components of a SQL Server Database Table
• Native SQL Server Data Types
• Executing a Query in SQL Server Management Studio
• Introduction to SQLCMD

Introduction to SQL and Transact-SQL

• ANSI SQL vs. T-SQL (Transact-SQL)
• Categories of SQL Statements
• T-SQL Scripts
• Using Batches
• Adding Comments to T-SQL Code
• Working with Variables
• Conditional Statements

Using the SELECT Statement to Retrieve Data

• Examine the Basic Syntax of the SELECT Statement
• Retrieving Columns of Data from a Table
• Using Aliases for Column Names
• Using Aliases for Table Names

Retrieving Specific Rows of Data Using the WHERE Clause

• How to Use the WHERE Clause
• Overview of Operators
• Filter Data by Using Comparison Operators
• Filter Data by Using String Comparisons
• Filter Data by Using Logical Operators
• Filter Data Using a Range of Values
• Filter Data Using a List of Values
• Working with NULL Values
• Introducing Native SQL Server Functions
• Functions Relating to NULL Values

Formatting and Sorting Result Sets

• Sorting Data Using the ORDER BY Clause
• Eliminating Duplicate Rows
• Using String Literals
• Using Expressions

Querying Data from Multiple Tables by Using Joins

• Introduction to Joins
• Using Inner Joins
• Using Outer Joins
• Using Cross Joins
• Joining More than Two Tables
• Joining a Table to Itself
• Using Non-Equi Joins
• How to Join a Table to a User-Defined Function

Inserting Data into Tables

• Inserting a Single Row into a Table
• Inserting Multiple Rows into a Table
• Inserting Values into Identity Columns
• Differentiating Various INSERT Statements
• Using the OUTPUT Clause with the INSERT Statement

Deleting Data from Tables

• Deleting Rows from a Table
• Truncating a Table
• Deleting Rows Based on Data in Other Tables
• Using the OUTPUT Clause with the DELETE Statement

Updating Data in Tables

• Updating Rows in a Table
• Updating Rows Based on Data in Other Tables
• Using the OUTPUT Clause with the UPDATE Statement

Transactions and Locking

• What Is a Transaction?
• How SQL Server Modifies Data in Tables
• Managing Transactions
• Nested Transactions
• Transaction Isolation Levels
• SQL Server Locking Architecture
• Managing Locks

Working with SQL Server Date and Time Data

• Data Type Precedence
• Implicit Data Type Conversions
• Querying and Modifying Date and Time Data
• Using the New SQL Server 2008 Date and Time Data Types

Querying XML Data

• Introduction to XML
• How SQL Server Implements XML
• Generating XML Based Reports with FOR XML
• Querying XML by Using OpenXML
• Introduction to XQuery
• Querying XML by Using XQuery
• Querying Relational Data Combined with XML Data
• XML Indexes
• XML Schemas

Summarizing Data Using Aggregate Functions

• Using Aggregate Functions Native to SQL Server
• Using Aggregate Functions with NULL Values
• Grouping Summarized Data with GROUP BY
• Filtering Grouped Data Using the HAVING Clause
• Using the ROLLUP and CUBE Operators
• Using the COMPUTE Clause
• How to Implement Custom Aggregate Functions

Ranking Grouped Data

• Ranking Data Using Ranking Functions
• Using ROW_NUMBER
• Using RANK
• Using DENSE_RANK
• Using NTILE
• Summary of Ranking Functions Based on Their Functionality

Combining and Limiting Result Sets

• Combining Result Sets Using the UNION Operator
• Limiting Result Sets Using the EXCEPT and
• INTERSECT Operators
• Identifying the Order of Precedence of UNION,
• EXCEPT, and INTERSECT
• Limiting Result Sets Using the TOP Operator
• Limiting Result Sets Using the TABLESAMPLE Operator

Working with Subqueries

• Introduction to Subqueries
• Using Subqueries as Expressions and Derived Tables
• Using the ANY, ALL, and SOME Operators
• Scalar versus Tabular Subqueries
• Using Correlated Subqueries
• Using the EXISTS Clause with Correlated Subqueries
• Using the APPLY Operator
• Subqueries versus Joins
• Subqueries versus Temporary Tables

Performance Considerations for Writing

• Queries
• How SQL Server Processes T-SQL Queries
• How the Query Optimizer Processes Search Arguments
• Writing Efficient Search Arguments

Crosstab Queries

• Using the PIVOT Operator
• Using the UNPIVOT Operator

Common Table Expressions

• Introduction to Common Table Expressions
• Using Common Table Expressions
• Recursive Queries Using Common Table Expressions
• Techniques for Querying Hierarchical Data

Implementing Views

• Overview of Views
• Creating and Modifying a View
• Considerations When Creating Views
• Examining the Impact of Using SELECT * in Views
• Restrictions for Modifying Data by Using Views
• Using Views to Optimise Performance
• Indexed Views
• Partitioned Views

Implementing Stored Procedures

• Overview of Stored Procedures
• How Stored Procedures Are Executed by SQL Server
• Creating and Using a Simple Stored Procedure
• Parameterising Stored Procedures

Error Handling

• Using @@ERROR
• Using RAISERROR
• Using TRY...CATCH

Implementing User Defined Functions

• Overview of User Defined Functions (UDFs)
• Creating and Modifying UDFs
• Restrictions When Creating UDFs
• Implementing Different Types of UDFs
• Performance Consideration for Using User-Defined Functions
• Controlling Execution Context

Implementing Triggers

• Overview of Triggers
• How Triggers Work
• AFTER Triggers
• INSTEAD OF Triggers
• Overview of DDL Triggers

Appendix 1. Querying Metadata

• Different Categories of Data
• Grouping Concepts Related to Different Categories of Data
• Understanding Metadata
• Querying Metadata by Using SQL Server Views
• Querying Metadata by Using SQL Server Commands

Appendix 2. Distributed Queries

• Overview of Distributed Queries
• Writing Ad Hoc Distributed Queries
• Creating a Linked Server
• Creating a Distributed Query Using a Linked Server