Learn your way! Get started

SQL Server 2008: Developer

with experts Don Kiely, Frank Tillinghast

Course at a glance

Included in these subscriptions:

Dev & IT Pro Video
Dev & IT Pro Power Pack

	Release date	2/17/2009
	Level	Advanced
	Runtime	19h 7m
	Closed captioning	N/A
	Transcript	N/A
	eBooks / courseware	Included
	Hands-on labs	Included
	Sample code	Included
	Exams	Included

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. Then, you will learn how to build effective views, stored procedures, triggers, and user-defined functions, using Transact-SQL. You will learn how to use the Transact-SQL programming language for error handling and hierarchical queries, dealing with complex data structures and processes, how to make your databases more scalable through partitioning, and how to use .NET languages like C# and Visual Basic to build database objects.

Prerequisites

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

Learning Paths

This course will help you prepare for the following certification and exam:
MCTS: SQL Server 2008, Database Development
70-433: TS: Microsoft SQL Server 2008, Database Development

Meet the experts

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.

Frank Tillinghast is a senior consultant with MTOW Software Solutions. He is a Microsoft Certified Solution Developer and has been developing applications for over 15 years. Most of his time is spent consulting for companies nationwide with troubled projects or mentoring projects to successful completion. When he is not developing software or mentoring clients, Frank is teaching other developers. He has taught thousands of application developers how to create business solutions with Visual Studio .NET. VB.NET, ASP.NET, Visual C#, and SQL Server.

Course outline

Module 1

Views (39:52)

Introduction (05:11)
Advantages of Views (01:17)
Views and Security (01:04)
Creating Views (00:57)
View Rules (01:40)
Views and ORDER BY (02:05)
Syntax and Options (01:21)
CHECK OPTION (01:02)
Other Options (01:46)
Tools for Creating Views (00:36)
Demo: Creating Views (05:10)
From Templates (01:30)
Edit Views (00:52)
View Examples (08:23)
Nesting Views (02:25)
CTE (01:48)
Encrypting (02:06)
Summary (00:30)

Updating View Data (24:36)

Introduction (00:51)
Updating Rules (00:54)
Updating Behavior (00:55)
Demo: Limiting Updates (05:18)
Using Computed Columns (02:20)
Demo: Computed Columns (02:16)
Indexed Views (01:27)
How Indexed View Work (01:03)
Performance Benefits (01:27)
Data Modifications (01:00)
Requirements (01:51)
Demo: Indexed Views (02:16)
Partitioned Views (02:10)
Summary (00:41)

Module 2

Stored Procedures (36:39)

Introduction (01:00)
Overview (03:15)
Performance Benifits (01:50)
Deferred Name Resolution (01:04)
Execution Plans (01:54)
Plan Recompilation (01:45)
Using Schema Qualifiers (01:37)
Reasons to Use (02:08)
Stored Procedure Syntax (01:31)
Creating Procedures (02:35)
SET NOCOUNT ON (02:12)
Parameters (02:06)
Optional Parameters (02:02)
Output Parameters (02:42)
Return (03:25)
Variables (03:34)
Debugging (01:28)
Summary (00:22)

Testing and Triggers (28:06)

Introduction (01:36)
Demo: Debugging (03:43)
PRINT Statements (02:20)
Output Paramaters (03:04)
Creating Triggers (01:42)
Types of Triggers (01:37)
Uses for Triggers (01:05)
Trouble with Triggers (02:02)
How Triggers Work (01:42)
Demo: After Trigger (03:05)
INSTEAD OF Trigger (03:20)
DDL Trigger (02:14)
Summary (00:30)

User Defined Functions (18:43)

Introduction (02:28)
Scalar Functions (01:39)
WITH Function Options (02:05)
Demo: Scalar Functions (02:37)
With Parameters (02:04)
Inline Functions (00:51)
Inline Syntax (00:28)
Demo: Inline Function (02:11)
Inline Parameters (01:35)
Updating Data (02:03)
Summary (00:36)

Module 3

Table Valued Functions (17:54)

Introduction (00:52)
Multi-Statement Syntax (00:12)
Limitations (01:25)
TABLE Data Type (01:03)
Demo: Multi-Statement (06:38)
Using Functions, Views... (00:42)
Demo: Scalar Functions (02:05)
Table-Valued Functions (02:16)
Choosing Between (02:04)
Summary (00:33)

Transactions (33:04)

Introduction (01:25)
Passing the ACID Test (02:25)
Transaction Types (01:56)
Transaction Details (01:01)
Isolation Levels (01:28)
Locking (02:11)
Viewing Lock Info (01:11)
Avoiding Blocks (00:39)
Demo: Locking (03:48)
Deadlocks (03:32)
Preventing (02:48)
Applications (00:32)
Designing (01:32)
Efficient Transactions (02:01)
Compile\Runtime Errors (01:15)
Demo: Errors (04:33)
Summary (00:41)

Explicit Transactions (18:00)

Introduction (00:52)
Syntax (02:16)
Demo: Explicit (01:56)
Error Handling (01:11)
@@ERROR (00:50)
Demo: @@ERROR (02:14)
Stored Procedures (02:05)
Demo: Stored Procedure (05:58)
Summary (00:34)

Raise Error Try/Catch (18:24)

Introduction (01:10)
Demo: RAISERROR (00:42)
User-Defined Errors (02:08)
TRY/CATCH Overview (00:39)
Demo: TRY/CATCH (01:00)
Error Chaining (01:07)
Errors Not Handled (02:17)
Demo: Error Info (05:15)
XACT_STATE (02:04)
Demo: XACT_STATE (01:16)
Summary (00:42)

Module 4

SQLCLR (41:51)

Introduction (02:34)
Shared Responsibilities (02:27)
Ways to Use SQLCLR (01:21)
Demo: Enabling SQLCLR (01:03)
Writing SQLCLR Code (01:18)
Demo: SQLCLR Code (05:51)
SQLCLR Code In VS (06:56)
SQL Data Types (02:48)
Data Type Mapping (01:43)
Accessing Local Data (02:43)
Connection String (02:09)
Returning Results (03:08)
Demo: SQLCLR Example (07:13)
Summary (00:32)

SQLCLR Code Modules (32:55)

Introduction (00:38)
Triggers (01:24)
Method Attributes (01:07)
Demo: Trigger (05:16)
User-Defined Functions (01:41)
Demo: UDF (01:57)
SQLCLR Security (01:10)
Demo: Security (01:46)
Code Access Security (03:33)
How CAS Works (02:12)
Permission Sets (02:49)
Demo: Permissions (04:39)
T-SQL vs. .NET Code (02:38)
SQLCLR or T-SQL? (01:29)
Summary (00:31)

Module 5

Advanced SQLCLR (23:33)

Introduction (01:12)
Aggregates (02:16)
Demo: Aggregates (08:43)
Large Aggregates (01:26)
Demo: Large (04:31)
Multi-Parameter (01:15)
Demo: Multi-Parameter (03:41)
Summary (00:27)

SQLCLR User Defined Types (17:09)

Introduction (02:15)
Demo: StringList UDF (02:25)
ISNull (00:41)
Null (00:43)
Parse (02:08)
ToString (01:05)
Capacity (00:47)
Count (00:17)
GetAt (00:20)
AddItem (00:37)
RemoveAt (00:15)
Clear (00:35)
IBinarySerialize (02:55)
Testing (01:25)
Summary (00:33)

Ordered Table Value Functions (22:27)

Introduction (00:36)
Demo: Ordered Fuctions (02:03)
Create Assembly (08:11)
Managing Code Modules (00:23)
Demo: System Catalogs (05:33)
Troubleshooting (00:37)
Demo: Debuging (03:37)
Get Assemblies (00:46)
Summary (00:39)

Module 6

Full-Text Search (29:27)

Introduction (02:08)
Demo: Full-Text Index (06:58)
Full-Text Queries (01:03)
CONTAINS Predicate (02:12)
Demo: Simple Terms (01:06)
Generation Terms (06:21)
Prefix Terms (00:59)
Proximity Terms (00:31)
FREETEXT (01:19)
CONTAINSTABLE (02:26)
Ranking Relevance (03:48)
Summary (00:30)

Advanced TSQL (54:33)

Introduction (00:58)
Using APPLY (00:49)
Demo: APPLY With Joins (06:33)
APPLY With TVF (02:53)
FOR XML PATH (01:30)
MERGE (02:32)
Five Clauses in MERGE (00:49)
WHEN Clause (03:02)
Demo: MERGE (06:22)
Recursive Queries (01:13)
Demo: CTE (05:35)
Grouping Sets (00:52)
Demo: Base Query (02:35)
Grouping Sets (06:11)
ROLLUP (02:13)
CUBE (03:12)
GROUPING_ID (02:36)
Pivot Queries (00:49)
Demo: Pivot (03:08)
Summary (00:32)

Module 7

Executing Dynamic SQL (25:31)

Introduction (00:52)
Demo: Dynamic SQL (03:32)
sp_executesql (00:33)
Demo: Parameters (04:26)
QUOTENAME (05:58)
Output Parameters (01:19)
Signing Stored Procedures (03:10)
Demo: Certificates (05:04)
Summary (00:34)

HierarchyID (55:12)

Introduction (02:14)
Overview (02:33)
Indexing a Hierarchy (00:42)
Depth-first (01:27)
Breadth-first (01:24)
Manipulating (01:41)
Demo: HierarchyID (16:22)
Sparse Columns (02:08)
Restrictions (02:15)
Column Sets (01:01)
Restrictions (00:45)
Recommendations (00:37)
Demo: Sparse Columns (06:02)
Column Sets (06:27)
Filtered Indexes (02:15)
Using Filtered Indexes (01:48)
Demo: Filtered Indexes (04:57)
Summary (00:25)

Module 8

FileStream Spatial Data (52:39)

Introduction (02:53)
FILESTREAM Storage (01:18)
Data Access Methods (01:17)
Gotchas & Limitations (01:39)
Demo: FILESTREAM (09:41)
Spatial Data (01:04)
Geometry vs. Geography (03:28)
Geometric Plane (00:46)
Mother Ship (00:28)
Projection Distortion (02:41)
Data Standards (01:33)
Types of Spatial Data (01:35)
Spatial Objects (01:54)
Geospatial Data (02:03)
Demo: Spatial Data (19:36)
Summary (00:35)

SQL Server Partitions (20:54)

Introduction (00:44)
Overview (00:50)
Why Partition? (01:55)
Partitioning (00:44)
6.5 and Earlier (01:09)
SQL Server 7.0 (01:31)
SQL Server 2000 (00:47)
SQL Server 2005/2008 (00:52)
SQL Server 2008 (01:04)
Range Partitions (01:28)
Partition Key (02:25)
Multiple Filegroups (04:49)
Index Partitioning (02:05)
Summary (00:24)

Module 9

Creating Partitioned Tables (33:09)

Introduction (00:38)
Overview (02:37)
LEFT and RIGHT (04:52)
Datetime Values (02:09)
RANGE RIGHT (01:04)
Demo: Partition Function (01:39)
Partition Scheme (06:24)
Demo: Filegroups (02:02)
Partition Scheme (01:39)
Tables and Indexes (03:16)
Demo: Partitioned Table (02:41)
Partitioned Index (03:35)
Summary (00:27)

Querying Partitions (18:23)

Introduction (00:30)
Overview (01:29)
$PARTITION Function (02:00)
Demo: $PARTITION (05:59)
Catalog Views (02:05)
sys.partition_functions (00:53)
sys.partition_range_values (00:22)
sys.partition_schemes (00:37)
sys.data_spaces (00:31)
Demo: Catalog Views (03:28)
Summary (00:23)

Managing Partitions (21:54)

Introduction (00:35)
Overview (00:43)
Tables or Indexes (00:51)
Partition Function (01:53)
Splitting Partitions (02:12)
Merging Partitions (02:20)
Switching Partitions (03:36)
Partition Scheme (01:45)
Backing Up Partitions (02:20)
Performance (00:36)
Join Queries (00:56)
Multiple Disk Drives (00:58)
Lock Escalation (02:34)
Summary (00:27)

Module 10

Complex Queries (25:43)

Introduction (00:35)
NULL Values (00:21)
SqlTypes and CLR Types (04:25)
Demo: Uninitialized Variables (04:02)
ANSI_NULLS Option (02:40)
Demo: ANSI_NULLS (02:09)
NULLS and SqlBoolean (01:39)
Demo: SqlBoolean (02:53)
Assigning NULL Values (00:32)
CLR Integration (01:16)
Direct Assignment (00:56)
NULLIF (00:15)
COALESCE (00:41)
Demo: Assigning NULLS (02:51)
Summary (00:21)

Ranking (16:35)

Introduction (00:34)
Ranking Grouped Data (01:02)
ROW_NUMBER (02:38)
RANK (01:42)
DENSE_RANK (01:09)
NTILE (04:01)
Demo: ROW_NUMBER (01:42)
RANK (01:15)
DENSE_RANK (00:53)
NTILE (01:21)
Summary (00:15)

Correlated Subqueries (17:20)

Introduction (00:34)
Writing Subqueries (01:08)
Subquery Basics (01:15)
Correlated Subquery (00:36)
Demo: Subquery (01:14)
Correlated (01:53)
WHERE Clause (01:55)
ANY, SOME, ALL (00:45)
HAVING Clause (00:23)
Updates (00:38)
Demo: UPDATE (01:28)
Joins and Temp Tables (02:59)
Demo: Using a Join (02:04)
Summary (00:20)

Common Table Expressions (16:32)

Introduction (00:42)
When to Use CTEs (01:46)
CTE Syntax (00:50)
Overview (00:01)
Demo: Defining a CTE (02:49)
Recursive CTEs (04:33)
Demo: Recursive CTE (05:30)
Summary (00:17)

Module 11

Complex Data And Structures (28:54)

Introduction (00:32)
Overview (00:19)
Issues with Data Types (02:50)
Demo: Explicit Conversion (02:06)
Multinational Data (05:52)
Demo: COLLATE (03:00)
Hierarchial Data (05:46)
Demo: hierarchyid (07:53)
Summary (00:33)

Efficient Queries (33:40)

Introduction (00:51)
Overview (00:56)
EXISTS Clause (01:15)
Join vs. Subqueries (02:55)
One-Pass Queries (00:46)
Demo: UNION ALL (01:51)
One-Pass (01:39)
Multiple Resultsets (00:44)
Temp Tables (02:38)
Table Variables (02:29)
UDFs (02:34)
Demo: UDFs (06:27)
CTEs (00:46)
Worktables (00:39)
User-Defined Table Types (01:15)
Demo: Table Types (04:49)
Summary (00:58)

Module 12

Working With Complex Queries (19:54)

Introduction (00:54)
Overview (00:50)
Implicit Transactions (03:03)
Demo: Implicit Set On (03:07)
Keeping it Simple (02:19)
Demo: Sub Tasks (04:40)
UDF (01:48)
Demo: Procedure (02:47)
Summary (00:21)

Maintaining Query Files (24:33)

Introduction (00:48)
Overview (01:29)
Source Control (02:18)
SourceSafe (02:48)
Files Under Control (00:50)
Demo: Source Control (03:52)
Keyword Expansion (02:24)
Enabling (01:27)
Demo: srcsafe.ini (03:21)
Version Info (00:30)
File Versions (00:39)
Demo: Versions (03:31)
Summary (00:30)

Design Delpoyment VS (25:14)

Introduction (02:04)
Database Development (03:24)
VS Integration (01:42)
VS Tasks (03:22)
Database Project (01:24)
Demo: Database Project (12:58)
Summary (00:16)

Module 13

Generating Test Data (33:12)

Introduction (01:27)
Demo: Data Gen Plan (13:06)
Generate Data (09:02)
DB Management (00:45)
Demo: Unit Tests (05:56)
Build Solution (02:31)
Summary (00:23)

Refactoring (25:26)

Introduction (01:37)
Demo: Schema (02:30)
Comparing Schemas (02:58)
Comparing Data (03:50)
Execute T-SQL (02:58)
Partial DB Projects (03:14)
Demo: Partial Projects (07:44)
Summary (00:31)

Module 14

XML Data Type (21:16)

Introduction (00:42)
Overview (01:07)
Declaring XML Objects (05:02)
Loading Data (00:39)
SELECT...FOR XML (01:40)
OPENROWSET (01:43)
Demo: Direct Assign (04:30)
Indexing Columns (00:45)
Primary Indexes (01:17)
Secondary Indexes (01:44)
Demo: Indexes (01:38)
Summary (00:23)

XML Schema Collections (22:08)

Introduction (00:52)
Overview (01:07)
Typed and Untyped XML (01:35)
Schema Basics (01:18)
Demo: XSD (02:34)
Lax Validation (03:12)
Full dateTime Support (01:02)
Registering Schemas (01:54)
Viewing Stored Schemas (01:46)
Collections (00:29)
Namespaces (00:18)
Components (00:45)
Namespace (01:30)
Demo: Schema Info (03:15)
Summary (00:23)

Querying XML (25:17)

Introduction (00:41)
Overview (00:44)
Using XQuery (01:02)
query() (00:30)
value() (00:48)
exist() (00:36)
modify() (00:25)
nodes() (00:36)
Using XQuery (00:34)
Demo: XQuery Methods (04:03)
FLWOR (03:49)
FOR XML (01:05)
Demo: FOR XML (04:21)
OPENXML (02:26)
Demo: OPENXML (03:04)
Summary (00:26)

Module 15

Best Practices (09:11)

Introduction (00:40)
Overview (00:48)
Relational vs. XML (02:03)
Storing XML (01:19)
Reasons to Index (02:22)
Querying (01:24)
Summary (00:32)

Integration Services (49:41)

Introduction (02:36)
Overview (00:17)
What is SSIS? (03:00)
When To Use SSIS (01:10)
Architecture (02:15)
Control Flow Tasks (02:10)
Maintenance Plan Tasks (01:19)
Data Flow Task (02:27)
Import/Export Data (01:12)
Demo: Import/Export (08:23)
Tools (00:52)
BIDS (00:56)
Demo: BIDS (00:24)
Design Surfaces (04:15)
Control Flow (03:32)
Control vs. Data Flow (02:38)
Demo: Data Flow (03:56)
Event Handlers (01:05)
Package Explorer (03:08)
Execute Package Utility (03:21)
Summary (00:36)

Module 16

Analysis Services (54:42)

Introduction (01:30)
OLAP Terminology (01:55)
Cubes, Dimensions... (00:24)
Dimensions Tables (01:25)
Fact Tables (00:34)
Measures (00:28)
Schemas (01:09)
Cube Storage (02:44)
Partitions (01:07)
UDM (01:48)
UDM Capabilities (02:58)
Creating a UDM (01:25)
Demo: Data Source View (10:31)
Create Dimension (03:04)
Create Cube (03:48)
Browsing Cube (17:51)
Summary (01:50)

Reporting Services (12:38)

Introduction (02:01)
Overview (01:00)
Demo: Config Tool (04:14)
Report Wizard (00:53)
Data Source (00:48)
Query (00:23)
Report Type (00:11)
Design Table (00:29)
Table Style (00:14)
Deployment Location (00:48)
Preview Report (00:28)
Summary (01:03)