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
• Creating and Using SQL Server Solutions
• Executing a Query in SQL Server Management Studio

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

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

Introduction to Transactions

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

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

Working with SQL Server Date and Time Data

• Data Type Precedence
• Implicit Data Type Conversions
• Querying and Modifying Date and Time Data

Querying XML Data

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

Querying Full Text Indexes

• Overview of Full Text Indexes
• Full Text Indexing and the Querying Process
• How SQL Server Implements Full Text Indexes
• Overview of Full Text Search
• Using the CONTAINS Predicate
• Using the FREETEXT Predicate
• Using the Full Text Functions
• Differences Between Full Text Functions and Predicates
• Combining Full Text Search and Transact-SQL Predicates

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

Distributed Queries

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

Introduction to 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

Introduction to Stored Procedures

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

Error Handling

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

Introduction to User Defined Functions

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

Introduction to Triggers

• Overview of Triggers
• How Triggers Work