Learn your way! Get started

SQL Server 2005

with experts Andy Baron, Don Kiely

Course at a glance

Included in these subscriptions:

Dev & IT Pro Video
Dev & IT Pro Power Pack

	Release date	5/18/2006
	Level	Intermediate
	Runtime	33h 40m
	Closed captioning	N/A
	Transcript	N/A
	eBooks / courseware	Included
	Hands-on labs	Included
	Sample code	Included
	Exams	Included
	Platform	Internet Explorer Only

Enterprise Solutions

Need reporting, custom learning tracks, or SCORM? Learn More

Course description

In this course, you will learn about the features that are available in SQL Server; how to design and create a database; and how to build basic queries using Transact-SQL, the language of SQL Server. You will learn how to build effective views, stored procedures, triggers, and user-defined functions, using Transact-SQL. Learn about the new enhancements to the Transact-SQL programming language including improved support for error handling and hierarchical queries, and programmers can now use .NET languages like C# and Visual Basic to build database objects. You will learn how to take advantage of the new, user-friendly management console that integrates both authoring and administrative tasks. You will learn how to take advantage of SQL Server tools for analyzing and tuning your databases. You will also learn about integration services, implementing security, and the new Business Intelligence (BI) suite.

Prerequisites

To get the most out of the Microsoft SQL Server 2005 course, you should have a solid understanding of relational databases. No particular programming experience is required, but the course is taught from a developer's perspective.

Meet the experts

Andy Baron is a nationally recognized industry expert specializing in Visual Basic, Visual C#, ASP.NET, ADO.NET, SQL Server, and SQL Server Business Intelligence. He is an experienced consultant, author, and editor for several industry publications and speaker at industry conferences. Andy has also been repeatedly selected as a Microsoft MVP for his contributions to Microsoft's online support.

Don Kiely is a featured instructor on many of our SQL Server and Visual Studio courses. He is a nationally recognized author, instructor, and consultant specializing in Microsoft technologies. Don has many years of teaching experience, is the author or co-author of several programming books, and has spoken at many industry conferences and user groups. In addition, Don is a consultant for a variety of companies that develop distributed applications for public and private organizations.

Course outline

Module 1

SQL Editions/Components (29:18)

Introduction (02:48)
SQL Server Editions (07:06)
SQL Server Comparisons (00:13)
Scalability/Performance (02:03)
High Availability Features (01:11)
Manageability Features (01:20)
Security Features (00:10)
Programmability Features (00:13)
Integration/Interoperability (01:05)
Business Intelligence Features (05:17)
Server Components (02:44)
SQL Server Mgmt. Studio (01:21)
Business Intelligence M.S. (01:06)
Other Management Tools (02:16)
Summary (00:18)

Using Management Studio (37:01)

Introduction (00:30)
Starting Config. Manager (01:50)
Performance Tools (00:54)
B.I. Development Studio (00:40)
Management Studio (03:38)
SQL 2005 Schemas (04:10)
Northwind Database (02:08)
Running a T-SQL Script (07:15)
Overview of Database Security (04:27)
Other Server Objects (01:01)
Replication and Management (01:35)
Overview of Table Objects (04:04)
Overview of Views (02:11)
Overview of Programmability (02:10)
Summary (00:22)

Working w/ Tables and Queries (32:18)

Introduction (00:51)
Working with Tables (04:12)
Template Explorer (02:31)
Help / Books Online (02:50)
Working with Queries (01:27)
New in T-SQL: CTE (03:50)
New in T-SQL: Calculate Rank (02:50)
New in T-SQL: TOP(N) (04:53)
New in T-SQL: PIVOT (03:56)
XML in SQL Server 2005 (02:26)
APPLY and FOR XML Path (02:08)
Summary (00:20)

Module 2

Additional New Features (36:35)

Introduction (00:54)
Try/Catch Error Handling (01:53)
EXECUTE AS (02:00)
Snapshot Isolation Level (02:12)
SQLCLR (04:04)
SQL Management Objects (00:53)
Replication Mgmt. Objects (01:04)
B.I. Development Studio (01:27)
Integration Services (SSIS) (02:02)
Reporting Services (01:41)
Analysis Services (SSAS) (02:57)
Access the Samples / Tutorials (02:17)
AdventureWorksDW Sample (01:36)
SSIS Sample (05:40)
SSAS Sample (05:16)
Summary (00:30)

Preparing and Upgrading (26:26)

Introduction (00:51)
Hardware/Software Req. (03:41)
Internet/Network Software Req. (01:53)
Setting Up Service Accounts (00:31)
Domain User Account (01:00)
Local System Account (01:04)
Local Service Account (00:41)
Network Service Account (00:37)
Multiple Instances (00:30)
Multiple Instance Scenarios (02:10)
Using Named Instances (00:47)
What Each Instance Gets (02:31)
Upgrading an Earlier Version (00:24)
Upgrade Considerations (03:12)
Upgrade Advisor (01:25)
Upgrade Advisor Demo (04:36)
Summary (00:24)

Installation (16:38)

Introduction (00:50)
Installing SQL Server 2005 (00:21)
Installing Prerequisites (01:04)
System Configuration Checks (01:53)
Registration Information (00:40)
Components to Install (00:32)
Feature Selection (02:27)
Instance Name (00:36)
Service Account (01:19)
Authentication Mode (01:25)
Collation Settings (00:43)
Error & Usage Report Setting (01:26)
Install Summary Screen (00:46)
Performing the Installation (01:10)
View the Summary Log (00:52)
Summary (00:27)

Configuration (34:02)

Introduction (00:49)
Configuration Manager (04:39)
Surface Area Config. Tool (04:31)
Use SSMS to Configure (01:34)
Server Configuration (00:51)
Save Scripts for any Changes (02:09)
General Settings (00:51)
Memory Options (02:32)
Server Processors (02:34)
Security Options (03:05)
Client Connections (03:46)
Database Settings (02:55)
Advanced Settings (02:08)
Permissions (00:41)
Summary (00:50)

Module 3

Database Design Principles (34:10)

Introduction (00:54)
Origins of Relational Design (02:53)
Rel./Attrib./Tuple/Candidate (02:55)
Primary/Surrogate/Foreign (05:36)
Surrogate Keys (03:01)
Data Normalization (00:56)
Data Normalization Demo (02:20)
The Create Database Script (04:21)
First Normal Form (01:14)
First Normal Form Examples (09:36)
Summary (00:19)

Second/Third Normal Form (24:45)

Introduction (00:30)
Second Normal Form (02:11)
Second Normal Form Ex. (09:39)
Third Normal Form (02:11)
Third Normal Form Examples (07:58)
Beyond Codd's Normal Forms (01:16)
Summary (00:57)

Relational/Referential Integrity (29:51)

Introduction (00:48)
One-to-Many Relationships (00:56)
Create a New Diagram (01:44)
Additional Tables to Diagram (04:12)
Many-to-Many Relationships (05:42)
One-to-One Relationships (06:39)
Enforcing Referential Integrity (01:23)
Cascading Updates/Deletes (02:02)
New Cascading Actions (03:38)
Enforcing Data Integrity (00:55)
Summary (01:47)

Module 4

Implementing Data Storage (30:06)

Introduction (01:03)
Pages (03:48)
Extents (01:41)
Files (03:41)
Using Files and Filegroups (00:37)
Data Integrity (03:13)
Creating a Database (03:23)
Modifying Database Options (09:09)
Filegroups (00:24)
View Script to Create DB (02:39)
Summary (00:24)

Rules for Identifiers (13:35)

Introduction (00:33)
Object Name Requirements (04:36)
Schemas Explained (05:38)
Create a Table/DataType (01:45)
Summary (01:01)

Data Types Explained (45:32)

Introduction (00:25)
Character-based Data Types (04:06)
Numeric Data Types (06:04)
Date Data Types (03:11)
Binary Data Types (01:30)
Identifier Data Types (07:19)
sql_variant Data Type (01:48)
Variable-only Data Type (02:07)
xml Data Type (04:58)
User-Defined Types (02:23)
View Column Properties (02:39)
Computed Columns (02:59)
Set the Identity Specification (00:49)
View the SQL Scripts Created (02:42)
Summary (02:26)

Module 5

Constraints/Indexes/Diagrams (38:38)

Introduction (01:28)
Constraints Explained (04:39)
Create a Check Constraint (08:03)
Test the Constraint (02:01)
Triggers/Uses for Triggers (03:21)
Indexes Explained (04:37)
Create a New Index (05:30)
View Index Script (01:23)
Create a Database Diagram (01:03)
Diagram Right-Click Options (04:30)
Summary (01:59)

Understanding T-SQL (35:29)

Introduction (00:42)
Understanding Transact-SQL (02:18)
Schemas and Naming (04:06)
The SELECT Statement (04:00)
Selecting All Columns (01:48)
Concatenating Columns (01:24)
Aliasing Column Names (03:07)
DISTINCT to Limit Values (01:39)
The WHERE Clause (01:03)
T-SQL Comparison Operators (00:44)
The LIKE Operator (04:13)
The BETWEEN Operator (01:12)
Using IS NULL to Test for Nulls (02:06)
AND, OR, and NOT (02:08)
Operator Precedence (01:48)
Using the IN Operator (00:56)
Using a Subquery (01:28)
Summary (00:41)

Sorting and Grouping (18:28)

Introduction (00:48)
Sorting on a Single Field (00:47)
Sorting in Descending Order (00:17)
Sorting by Multiple Columns (01:18)
Sorting using Expressions (01:15)
Aggregate Functions (02:21)
Using COUNT (02:58)
Using GROUP BY (01:45)
ORDER BY with GROUP BY (00:40)
HAVING with GROUP BY (02:07)
Using TOP (03:33)
Summary (00:33)

Module 6

Joining Tables (28:23)

Introduction (01:09)
Cross Joins (Cartesian Prod.) (01:38)
Joining in the WHERE Clause (01:45)
Join Notation (00:57)
Using Table Aliases (00:52)
Inner Joins (01:17)
Join w/ WHERE & ORDER BY (01:26)
Multiple Inner Join (01:25)
Aggregates & Grouping w/ Joins (02:18)
Inner vs Outer Joins (01:47)
Left Outer Joins (02:17)
Right Outer Joins (01:03)
Full Join (01:02)
Combining Inner & Outer Joins (04:31)
Self Joins (04:05)
Summary (00:44)

Inserting Data (30:40)

Introduction (02:25)
Inserting Data (07:08)
Set a Database Context (00:27)
Inserting a Single Value (01:08)
Suppress Rows Affected (00:53)
Using SET NOCOUNT ON (02:24)
Retrieving Identity Values (04:14)
Dealing with Delimiters (03:56)
Inserting Multiple Rows (01:20)
Insert Using a SELECT clause (02:38)
Insert Using SELECT INTO (02:56)
Summary (01:05)

Advanced Inserting Data (20:23)

Introduction (01:42)
Temporary Tables (02:53)
Create a Temp Table (01:16)
Using CREATE TABLE (01:00)
Global Temp Tables (01:13)
Inserting with Output (07:17)
Using BULK INSERT (03:58)
Summary (01:00)

Updating and Deleting Data (21:03)

Introduction (00:56)
Updating a Single Row (02:25)
Updating Multiple Rows & Col. (01:12)
Updating from Another Table (01:05)
Updating with TOP (02:06)
Updating with UPDATE.WRITE (08:18)
Deleting a Single Row (00:49)
Deleting Multiple Rows (00:10)
Delete All Rows (00:21)
Using TRUNCATE TABLE (01:20)
Summary (02:17)

Module 7

Transaction Isolation (35:40)

Introduction (01:08)
Isolation Levels Explained (00:24)
READ COMMITTED (03:06)
READ UNCOMMITTED (01:06)
REPEATABLE READ (02:22)
SERIALIZABLE (00:37)
Blocking and Deadlocks (04:05)
EAD COMMITTED Demo (03:54)
EAD UNCOMMITTED Demo (05:20)
Using Snapshot Isolation (00:48)
Snapshot Isolation Caveats (01:06)
Enabling Snapshot Isolation (02:43)
SNAPSHOT ISOLATION (03:27)
View Code Used to Clean Up (04:47)
Summary (00:41)

Management Studio (25:38)

Introduction (00:38)
Connecting to SSMS (01:38)
Configuring SSMS Options (05:36)
SSMS Toolbars (00:44)
SSMS Menu Options (00:17)
File/Edit Menus (01:13)
Advanced Menu (00:19)
View/Query Menus (01:07)
Project/Tools menus (01:11)
Window/Community/Help (00:54)
Configuring SSMS Windows (02:57)
Exploring the Object Explorer (01:02)
Right-Click Menu Options (01:08)
Server and Database Objects (01:02)
Displaying/Filtering Objects (01:02)
Viewing Dependent Objects (00:55)
Object Summary Pages (01:31)
Edit Objects (01:40)
Summary (00:35)

Query Editor and Help (26:08)

Introduction (00:35)
Open a Query Editor Window (01:01)
Multiple Code Window (01:43)
Formatting and Editing Code (00:24)
Indenting Code (00:53)
Search w/ Find and Replace (01:25)
Scripting with SSMS (00:34)
CREATE TABLE Script (00:47)
Creating an INSERT Script (01:15)
Execute Stored Proc. Script (01:04)
Using the Template Explorer (01:34)
Executing Queries (01:48)
Setting Options (01:11)
Displaying the Execution Plan (01:37)
Displaying the Client Statistics (00:45)
Saving Query Results (00:50)
Running in SQLCMD Mode (00:47)
Creating Projects & Solutions (02:49)
Working Offline (00:44)
Getting Help in SSMS (00:31)
Accessing F1 Help (00:54)
SQL Server Books Online (01:39)
Exploring the Toolbar (00:32)
Summary (00:34)

Module 8

T-SQL Functions (34:59)

Introduction (02:20)
Batches and Scripts (03:51)
Variables (03:41)
Delimiters and Operators (00:24)
Using CAST (02:29)
Type Coercion & Precedence (01:39)
Data Type Precedence (02:51)
Using an Explicit Cast (01:18)
Using CONVERT (02:59)
Using CONVERT with Style (02:49)
Using STR (01:10)
Working with Nulls (02:32)
IS NULL (00:01)
ISNULL (01:30)
NULLIF (01:27)
COALESCE (02:49)
Summary (01:01)

T-SQL Flow Control (25:42)

Introduction (02:08)
Using IF...ELSE (01:56)
Using BEGIN...END (03:21)
Using GOTO (01:43)
Using RETURN (03:40)
Using CASE (04:16)
Replace IIF/Conditional (03:20)
Using WHILE (02:05)
Using WAITFOR (02:25)
Summary (00:44)

T-SQL Ranking Functions (13:50)

Introduction (02:13)
New Ranking Func. Explained (05:27)
UnitPrice, Listed by UnitPrice (01:44)
UnitPrice, Listed by ProdName (00:53)
Rank Using PARTITION (01:05)
Overall, Listed by ProdName (01:27)
Summary (00:58)

Module 9

Transactions (31:49)

Introduction (01:50)
Passing the ACID Test (01:56)
Features that Support ACID (01:34)
Transaction Types (02:26)
Isolation Levels & Transactions (01:09)
Transactions and Locking (01:55)
Viewing Lock Information (00:58)
Deadlocks: The Problem (02:20)
Deadlocks: Solutions (06:00)
Creating Transactions in Apps. (00:33)
Designing Trans. Support (03:04)
Creating Efficient Transactions (02:28)
Compile/Runtime Errors (01:00)
Compile Trans. Error Demo (02:08)
Runtime Trans. Error Demo (02:03)
Summary (00:17)

Explicit Trans./Error Handling (38:04)

Introduction (01:08)
Creating Explicit Transactions (02:48)
Using @@TRANSCOUNT (01:20)
T-SQL Error Handling in Trans. (02:43)
Using Simple Error Handling (01:12)
Stored Procs to Manage Trans. (02:17)
Err. Handling in a Stored Proc. (07:43)
Error Severity Levels Explained (01:36)
Creating a User-Defined Error (01:59)
Using Try/Catch (01:58)
Try/Catch Details (02:30)
Error Handling Functions (01:12)
Display Detailed Error Info. (01:11)
RAISERRROR in a Try/Catch (04:41)
Trans. with XACT_STATE (01:57)
Using SET XACT_ABORT ON (01:04)
Summary (00:36)

Creating Views (42:51)

Introduction (01:53)
Why Use Views? (01:47)
Views and Security (02:15)
View Rules (06:26)
Syntax and Options (06:48)
Creating Views Using SSMS (10:38)
Alter a View (02:38)
Script View as a SELECT (00:29)
Creating Views from Templates (03:44)
Try to Create a Sorted View (03:31)
Summary (02:38)

Module 10

Working with Views (45:39)

Introduction (00:38)
Some View Examples (00:29)
Mailing Label View Example (01:19)
View Example Using Top (05:07)
Correlated Subquery View Ex. (02:34)
View Example Using a JOIN (01:01)
Derived Tables & Nesting View (03:41)
Common Table Expressions (03:36)
Encrypting View Definitions (02:09)
Using CHECK OPTION (02:08)
Updating Rules (02:52)
Updating Views (01:01)
Views w/ Computed Column (00:57)
Updating Views with Joins (01:12)
Creating a Computed Column (02:44)
IsPersisted Prop./PERSISTED (02:19)
Indexed View Explained (00:26)
Indexed View Requirements (03:46)
Partitioned Views (06:15)
Summary (01:16)

Stored Procedures (42:49)

Introduction (01:21)
Stored Procedure Features (03:27)
SP Performance Benefits (07:38)
Using Schema Qualifiers (03:07)
Reasons to Use SPs (03:26)
Stored Procedure Syntax (02:09)
Creating Stored Procedures (05:34)
Using Parameters in a SP (04:04)
Returning Results from a SP (05:03)
Return Values in a Result Set (01:28)
Using Variables in SPs (02:46)
Limitations in Statements (01:58)
Summary (00:42)

Module 11

Stored Proc. Debug/Triggers (33:01)

Introduction (01:10)
Debugging SPs with PRINT (02:44)
Other SP Debugging Tips (03:14)
Overview of Creating Triggers (04:34)
Uses / Limits for Triggers (02:10)
Trouble with Triggers (03:42)
How Triggers Work (03:04)
How to Create Triggers (01:34)
Creating Triggers Demo (02:42)
INSTEAD OF Trigger Demo (03:14)
DDL Triggers (01:43)
Stop Adding a Table Trigger (01:39)
Drop a DDL Trigger (00:38)
Summary (00:46)

User-Defined Functions (36:16)

Introduction (01:27)
Scalar Functions (00:31)
Inline Table-Valued Function (01:28)
Multi-Statement Table-Valued (02:04)
CLR Functions Explained (01:51)
Create a Scalar Function (05:36)
Scalar Function w/Parameters (03:09)
User-Defined Function Options (05:06)
Call Scalar Func. w/Param. (02:58)
Create an Inline TVF (02:32)
Calling an Inline TVF (03:51)
Inline Function w/Parameters (01:24)
Update From Inline Function (03:06)
Summary (01:05)

Multi-Statement Functions (18:27)

Introduction (01:32)
Create a Multi-Statement Func. (06:02)
Call a Multi-Statement Function (01:30)
Filter on a Field (01:58)
Join Functions to Tables (01:25)
Use within Scalar Function (02:26)
Use within a Subquery (02:26)
Summary (01:04)

Module 12

Using .NET Code (39:05)

Introduction (01:26)
Hosting .NET Code (02:38)
Ways to Use SQLCLR (00:44)
Enabling SQLCLR (00:41)
Enabling SQLCLR Demo (01:27)
Writing SQLCLR Code (01:13)
SQLCLR Code Manually (05:51)
SQLCLR Code Using VS 2005 (06:51)
SQL Data Types (02:40)
Data Type Comparison (01:17)
Accessing Local Data (03:23)
Returning Results (03:07)
Return Data w/ Stored Proc. (03:40)
Return Rows of Data (03:21)
Summary (00:39)

SQLCLR Code Modules (40:10)

Introduction (00:49)
Stored Procedures/Triggers (02:55)
Creating an SQLCLR Trigger (03:55)
Test the New Trigger (03:26)
User-Defined Functions (01:56)
Create a Hashing UDF (00:54)
Test the Hashing UDF (00:59)
Aggregates (04:49)
Create a Concatenate Aggreg. (00:51)
View C.A. Code (09:00)
System Catalogs (00:25)
View sys.assemblies (01:24)
View sys.assembly_files (02:02)
View sys.assembly_modules (00:41)
sys.procedures/sys.triggers (00:46)
Debugging Using Visual Studio (02:50)
Viewing Loaded Assemblies (01:10)
Profiler/Performance Counters (00:34)
Summary (00:35)

SQLCLR Security (20:00)

Introduction (01:18)
Broken SQLCLR Code Demo (02:09)
Code Access Security (CAS) (05:32)
SQLCLR Permission Sets (04:02)
Fixed SQLCLR Code Demo (01:32)
SET TRUSTWORHY ON (01:36)
Benefit of SQLCLR Code (02:13)
Summary (01:36)

Module 13

Planning/Configuring Full Text (27:39)

Introduction (01:23)
Advant. of Full-Text Search (05:51)
Full-Text Enhancements in '05 (00:15)
Indexing Performance (01:05)
DDL (01:02)
Integrated Backup/Restore (01:14)
XML Support (01:14)
Thesaurus Support (00:33)
Multi-Language Support (02:08)
FT Search T-SQL Queries (01:59)
Create a Full-Text Index Demo (10:33)
Summary (00:18)

Full-Text Search Queries (27:33)

Introduction (00:32)
Writing Full-Text Queries (02:19)
CONTAINS Predicate (02:12)
CONTAINS with Simple Terms (01:50)
CONTAINS w/ Gen. Terms (01:37)
CONTAINS with Thesaurus (00:31)
View/Modify the Thesaurus (06:23)
CONTAINS with Prefix Terms (00:30)
CONTAINS w/ Proximity Terms (00:57)
FREETEXT Predicate (01:26)
Ranking Relevance (01:48)
Joins to Fetch Text Values (00:58)
Limiting the Number of Results (02:38)
Use Weights to Influence Rank (00:37)
Using Full-Text DDL (01:37)
Summary (01:31)

FOR XML AUTO/RAW (21:39)

Introduction (01:54)
Using AUTO Mode (04:50)
Nesting Hierarchy in AUTO (02:31)
Adding a Root Element (00:49)
Generate Elements in AUTO (01:25)
Generating XML Schema (02:52)
Using RAW Mode (01:25)
Naming the Row Element (01:17)
Generate Elements in RAW (00:55)
Including Null Values (02:47)
Summary (00:49)

FOR XML EXPLICIT/PATH (31:41)

Introduction (01:28)
Using FOR XML EXPLICIT (01:23)
Constructing the UNION Query (04:11)
Special Column Aliases (05:22)
Using PATH Mode (00:22)
Elements/Subelements/Attrib. (04:22)
Subqueries to Nest Results (06:30)
PATH Mode Col. Alias Exp. (06:03)
Summary (01:56)

Module 14

APPLY/Common Table Exp. (29:55)

Introduction (01:36)
Using APPLY (00:16)
Comparing APPLY/Using Joins (01:36)
Using CROSS/OUTER APPLY (02:22)
Things We Can't Do with a Join (04:37)
APPLY w/ Table-Valued Func. (02:30)
Combine APPLY w/ FXP (03:32)
Creating Recursive Queries (00:31)
Classic Recursive Queries (06:51)
Recursive Query Using a CTE (05:24)
Summary (00:36)

Pivot and Dynamic SQL (33:38)

Introduction (01:27)
Col. into Headings w/ PIVOT (02:57)
QUOTENAME / SQL Injection (04:40)
Converting Nulls to Zeros (01:44)
Using Dynamic SQL (01:05)
Overcoming PIVOT Limitations (09:24)
Parameters w/ sp_executesql (09:53)
Summary (02:24)

Pivot/Dynamic SQL Continued (25:56)

Introduction (01:31)
Using QUOTENAME() (03:06)
Defend Against SQL Injection (03:24)
QUOTENAME with Quotes (02:18)
Output w/ sp_executesql (03:46)
Signing Stored Proc. w/ Cert. (02:12)
Create a Signed Cert. Demo (07:02)
Test the Signed Certificate (01:01)
Run the Clean Up Code (00:29)
Summary (01:03)

Module 15

Overview and Authentication (40:20)

Introduction (01:26)
Some Security Threats (03:22)
Security Design Philosophy (03:54)
Two Stages of Security (01:49)
Authentication (03:09)
Configuring Security Settings (00:31)
Windows Integrated Auth. (02:16)
Adding a Win. Login Demo (03:55)
Win. Logins via Transact-SQL (00:21)
SQL Server Authentication (05:59)
SQL Server Logins via T-SQL (01:17)
SQL Server Security Settings (00:08)
Win. Logins/SQL Server Login (01:23)
Beware of the sa Login (01:55)
Password Policy & Enforce (01:56)
Local Security Settings applet (03:40)
Enable/Disable Pwd. Policy (02:31)
Summary (00:40)

Authorization and Permissions (01:06:21)

Introduction (01:34)
Principals (01:28)
Roles (00:55)
Server Roles (02:24)
Add a User to a Server Role (01:49)
SQL Server Login via T-SQL (00:26)
Return List of All Server Roles (00:21)
Get Desc. of a Single SR (00:10)
Get List of the DiskAdmin Role (01:42)
Database Roles (03:51)
Add User to a Database Role (01:00)
The Public Role (00:58)
dbo (Database Owner) Role (01:14)
User-Defined Roles (01:44)
Create a UDR Demo (01:26)
Securable Objects (03:19)
Permission Types (05:20)
Permission Statements (02:25)
Granting Permissions Demo (05:47)
Schemas (03:58)
Roles/Permissions/Schemas (01:42)
Schemas to Assign Perm. (01:57)
Execution Context (08:48)
The EXECUTE AS Clause (05:21)
Metadata Security (06:05)
Summary (00:25)

Encryption (18:40)

Introduction (02:55)
Encryption Keys (03:52)
Key Management (01:40)
Encrypting Data Demo (07:58)
Decrypting Data Demo (01:19)
Summary (00:54)

Module 16

Evaluating Performance (32:37)

Introduction (02:03)
SQL Server Monitoring Tools (03:07)
SQL Server Profiler (01:16)
Activity Monitor (00:32)
Display Est. Exec. Plan (00:39)
DB Engine Tuning Advisor (01:03)
Windows Performance Monitor (00:43)
Windows Event Viewer (00:18)
SQL Server Profiler Term. (03:11)
Diagnosing CPU Bottlenecks (02:01)
SQL Server Profiler Demo (08:29)
Run the Trace (03:52)
Query the Trace File (03:18)
Summary (02:00)

Tuning Queries (29:08)

Introduction (03:48)
How Join Types Affect Perf. (06:13)
Join Hints (01:05)
Join & Subquery Perf. Issues (03:06)
Limit Num. of Tables in a Join (03:48)
Avoid Cursors (00:48)
Cursor Alternatives (01:13)
Compare Subqueries & Joins (07:41)
Summary (01:23)

Indexes and Partitions (26:50)

Introduction (00:31)
What is an Index? (01:19)
Non-Clustered/Clustered Index (07:01)
Index Recommendations (06:19)
How the Optimizer Decides (00:19)
What is a Partition? (01:34)
DB Engine Tuning Advisor (02:45)
Indexing and Partition Rcmd. (03:12)
View Tuning Options (02:43)
Summary (01:03)

Identifying Bottlenecks (30:06)

Introduction (02:00)
Using Activity Monitor (11:01)
Using Performance Monitor (09:20)
Using Dynamic Mgmt. Views (03:22)
Memory Usage Demo (02:21)
Summary (01:59)

Module 17

Automating Admin. Tasks (30:05)

Introduction (01:42)
SQL Server Agent (02:02)
Start SQL Server Agent (01:34)
Configure SQL Server Agent (02:39)
Create an Operator (02:02)
Create a Job (06:23)
Create Alerts (03:13)
Maintenance Plans (02:09)
Maint. Plan Wizard Demo (05:50)
Modify the Maintenance Plan (01:59)
Summary (00:28)

SMO (24:04)

Introduction (01:24)
SMO Namespaces (01:13)
The SMO Object Model (01:18)
SMO Object Model Details (02:19)
SMO Demo Application (02:59)
View the Connect Code (05:13)
View Create Database Code (01:57)
View Backup Database Code (02:56)
View Object Scripting Code (03:53)
Summary (00:47)

Replication Concepts (33:05)

Introduction (00:53)
Publishing Model (02:20)
Replication Types: Snapshot (02:10)
Replication Types: Trans. (03:13)
Replication Types: Merge (04:02)
Replication Metadata (03:04)
Replication Agents (06:22)
Replication Prog. Interfaces (02:21)
Replication Stored Procedures (02:53)
Replication Mgmt. Objects (02:22)
Agent Exec./ActiveX Controls (02:07)
Summary (01:14)

Replication Demo (32:30)

Introduction (00:30)
Set Up Replication Demo (02:00)
Configuration Wizard (07:26)
Create a Merge Publication (04:04)
Define a Filter for the Pub. (02:44)
Add a Join to the Publication (04:38)
Define Security Settings (01:38)
Final Wizard Settings (00:21)
Create the Publication (01:43)
Generate the Snapshot (02:39)
Generate Scripts (04:17)
Summary (00:26)

Module 18

Coding Replication (34:18)

Introduction (01:12)
Create a Subscriber Database (00:59)
View Replication Code (02:48)
Create a Subscription in Code (06:23)
Run the Create Code (01:30)
Sync. the Subscription in Code (04:01)
Run the Synchronize Code (03:07)
Use Replication Monitor (01:43)
Monitor Activity in Code (03:20)
Run the Monitor Code (01:16)
View the ChangeArticle Code (03:20)
View the ReInitialize Code (01:56)
View a Subscription History (01:44)
Summary (00:53)

Introducing SSIS (30:16)

Introduction (02:45)
When to Use SSIS (01:36)
SSIS Architecture (05:15)
Import and Export Wizard (07:09)
Package Development Tools (03:11)
Control Flow Design Surface (02:49)
Data Flow Design Surface (02:36)
Event Handlers Design Surface (00:37)
Package Explorer DS (00:55)
Execute Package Utility (03:05)
Summary (00:12)

Building an SSIS Package (17:49)

Introduction (01:46)
SSIS Demo: Import Orders (04:52)
Data Flow Design Surf. Demo (07:35)
Truncated Fields (00:42)
Running the Design Flow (01:03)
Progress Tab (01:08)
Summary (00:40)

Add Control/Data Flow Tasks (30:00)

Introduction (01:07)
Add a Task to Control Flow (02:55)
Transform / Clean Up Data (05:40)
Tips on Date/Time Conversion (00:49)
Adding a Script Component (09:32)
Defining the Destination (04:38)
Mapping Input/Output Columns (01:05)
Organizing the Data Flow (00:26)
TRUNCATE the Order Table (01:35)
Review the Output (01:10)
Summary (00:58)

Module 19

Troubleshooting Packages (11:30)

Introduction (00:54)
Starting Package Logging (00:43)
Choosing What to Log (02:40)
Review Visual Warnings (01:10)
Execution Results (00:54)
Create a Data Viewer (03:47)
Break Points (00:56)
Summary (00:22)

Analysis Services Overview (35:38)

Introduction (01:28)
The Problem (01:24)
The Solution (01:50)
Fact Table / Measures (00:57)
Dimension Table (00:30)
Dimensions / Cubes (04:37)
Dimensional DB Schemas (02:01)
Cube Storage: MOLAP (01:39)
Cube Storage: ROLAP (00:49)
Cube Storage: HOLAP (01:00)
Cube Storage: Partitions (00:29)
Unified Dimensional Model (08:28)
UDM: Perspectives (00:45)
UDM: KPIs (01:12)
UDM: Attribute Semantics (01:48)
UDM: Custom Aggregations (01:37)
UDM: Write-Back (01:13)
UDM: Actions (01:08)
UDM: Security (02:06)
Summary (00:26)

Creating a Cube (39:36)

Introduction (00:55)
AdventureWorksDW Sample (03:25)
Creating a Data Source View (02:01)
Create a New Data Source (00:21)
Define Connection Information (03:00)
Define Impersonation Info. (02:08)
Create the Data Source View (03:34)
View DSV Designer (01:06)
Create Friendly Table Names (01:00)
View Relationships of Tables (01:27)
Add/Modify Relationships (00:29)
Create a New Cube (02:05)
Detect/View Fact/Dim. Tables (02:25)
Select a Time Dimension Table (01:56)
Select / Modify Measures (02:04)
Review / Modify New Dim. (02:19)
Cube Designer: Cube Structure (01:04)
CD: Dimension Usage (01:22)
CD: Calculations / KPIs (01:11)
CD: Actions / Partitions (00:30)
CD: Perspectives (00:25)
CD: Translations (00:24)
CD: Browser (00:15)
Deploy the Cube (02:49)
Reload in the Browser (00:48)
Summary (00:20)

Module 20

Browsing/Enhancing Cubes (26:54)

Introduction (00:44)
Browse the Cube Data (03:54)
Add to the Subcube Area (01:17)
Modify Properties of a Measure (02:43)
Modify Attrib. in a Dimension (01:45)
Organize Attrib. into a Folder (01:15)
Modify the Hierarchy (02:55)
Rebuild / Deploy the Project (01:22)
Add a New Named Calculation (04:19)
Bind the New Named Calc. (01:24)
Browse the Modified Cube (03:48)
Summary (01:22)

Data Mining (30:15)

Introduction (01:07)
Data Mining Algorithms (07:09)
Creating a Data Mining Project (01:25)
Create New Data Source View (01:35)
Define a Relationship (00:50)
Create a New DM Structure (04:30)
Set Algorithm Parameters (01:35)
Build / Deploy the Structure (00:47)
Process / View DM Model (01:42)
Inspecting the Data (00:20)
Filter the Itemsets (01:42)
Probability / Importance (02:19)
View the Dependency Network (01:47)
Adjust Link Strength (01:17)
Summary (02:03)

Reporting Services (14:08)

Introduction (01:15)
Server Components (03:17)
Report Server Demo (02:22)
Command Prompt Utilities (00:31)
Client Components (01:12)
Reporting Services Req. (00:52)
Reporting Services Installation (00:58)
Using the Configuration Tool (03:26)
Summary (00:10)

Creating Reports (17:15)

Introduction (00:29)
Report Server Wizard (04:47)
Build a Report From Scratch (01:11)
Create a New DataSource (01:02)
Create a New DataSet (01:03)
Add Data to the Report (02:16)
Preview the Report (00:42)
Deploy the Report (01:06)
View the Report (00:39)
Using/Managing Pub. Reports (00:53)
Exploring the Report Manager (00:53)
Upload a Report File (01:45)
Summary (00:22)