Course at a glance
Included in these subscriptions:
- Dev & IT Pro Video
- Dev & IT Pro Power Pack
| Release date | 11/20/2007 | |
| Level | Advanced | |
| Runtime | 27h 52m | |
| Closed captioning | N/A | |
| Transcript | N/A | |
| eBooks / courseware | Included | |
| Hands-on labs | Included | |
| Sample code | Included | |
| Exams | Included | |
| Platform | Internet Explorer Only |
Course description
In this course, you will learn 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 fuctions using Transact-SQL. You will learn about the new enhancements to the Transact-SQL programming language including improved support for error handling and hierarchical queries, and how programmers can now use .NET languages like C# and Visual Basic to build database objects.
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)
Module 2
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)
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)
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)
- Schemas Explained (05:38)
- Create a Table/DataType (01:45)
- Summary (01:01)
- Character-based Data Types (04:06)
Data Types Explained (45:32)
- Schemas Explained (05:38)
- Create a Table/DataType (01:45)
- Introduction (00:25)
- Character-based Data Types (04:06)
- 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)
Module 6
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)
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)
Module 7
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)
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)
Module 8
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)
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)
Module 9
More on T-SQL Functions (36:58)
- Introduction (00:38)
- IsNumeric (02:35)
- RAND (02:55)
- ROUND (02:35)
- Combining RAND and ROUND (02:39)
- REPLACE and STUFF (01:36)
- LEN, LEFT, and RIGHT (00:37)
- SUBSTRING (00:53)
- CHARINDEX (02:32)
- PATINDEX (03:49)
- SPACE (00:55)
- CHAR and ASCII (00:53)
- LOWER and UPPER (00:07)
- Case-insensitive Filtering (00:53)
- COLLATE (01:53)
- LTRIM and RTRIM (00:33)
- GETDATE (00:10)
- MONTH, DAY, and YEAR (00:16)
- DATEPART and DATENAME (00:48)
- DATEADD and DATEDIFF (02:29)
- @@ROWCOUNT (01:44)
- @@IDENTITY (01:13)
- @@TRANCOUNT (00:20)
- @@ERROR (02:24)
- Summary (01:17)
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 10
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)
Module 11
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)
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)
Module 12
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)
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)
Module 13
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)
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)
Module 14
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)
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)
Module 15
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 16
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)
Partitions (15:47)
- Introduction (00:45)
- Table-based Partitioning (00:39)
- Why Partition? (01:45)
- Partition History in SQL Server (05:07)
- Range Partitions (01:05)
- Partition Key (01:55)
- Multiple Filegroups (02:43)
- Index Partitioning (01:24)
- Summary (00:19)
Module 17
Create Partitioned Tables (20:34)
- Introduction (00:38)
- Overview (00:51)
- Create Partition Function (00:49)
- LEFT/RIGHT Boundaries (02:58)
- Demo: Partition Function (01:46)
- Datetime Values (01:15)
- Partition Scheme (04:17)
- Demo: Partition Scheme (01:11)
- Create Table/Index (02:25)
- Demo: Create Table/Index (03:53)
- Summary (00:25)
Query Partitions (37:32)
- Introduction (00:41)
- Overview (01:01)
- $PARTITION Function (02:31)
- Demo: $PARTITION (05:41)
- Catalog Views (01:25)
- sys.partition_functions (00:42)
- sys.partition_range_values (00:18)
- sys.partition_schemes (00:22)
- sys.data_spaces (00:22)
- Demo: Catalog Views (02:26)
- Manage Partitions (00:30)
- Mod Part Tables/Indexes (00:28)
- Mod Partition Function (01:56)
- Split Partitions (03:36)
- Merge Partitions (03:44)
- Switch Partitions (03:19)
- Modify Partition Scheme (01:39)
- Backup Partitions (02:13)
- Performance Considerations (02:29)
- Summary: Partitions (01:33)
- Summary (00:26)
Work with Nulls (24:47)
- Introduction (00:37)
- Overview (00:18)
- SqlTypes vs. CLR Types (03:35)
- System.Data.SqlTypes (02:34)
- Review: SqlTypes/CLR Types (01:41)
- ANSI_NULLS Option (00:33)
- Demo: ANSI_NULLS Option (04:28)
- NULLs and SqlBoolean (02:12)
- Demo: NULLs and SqlBoolean (02:31)
- Assign NULL Values (00:39)
- CLR Integration (00:23)
- Direct Assignment (00:50)
- NULLIF / COALESCE (00:55)
- Demo: Assign NULL Values (01:18)
- Demo: COALESCE (01:42)
- Summary (00:23)
Ranking (14:21)
- Introduction (00:33)
- Grouped Data (00:55)
- ROW_NUMBER (01:15)
- RANK (01:09)
- DENSE_RANK (00:32)
- Demo: ROW_NUMBER (01:57)
- Demo: RANK (02:05)
- Demo: DENSE_RANK (01:15)
- NTILE (02:10)
- Demo: NTILE (02:09)
- Summary (00:15)
Module 18
Correlated Subqueries (19:00)
- Introduction (00:32)
- Overview (00:58)
- Subquery Basics (01:15)
- Correlated Subquery (03:12)
- Where Clause (01:37)
- ANY / SOME / ALL Modifiers (01:02)
- Demo: Subquery (01:31)
- HAVING (02:03)
- UPDATE (01:26)
- JOINS / Temp Tables (04:59)
- Summary (00:18)
CTE's (18:30)
- Introduction (00:41)
- Recursive CTE (05:13)
- Summary (00:13)
- Overview (00:24)
- Data Type Precedence (02:15)
- IMPLICIT Conversion (01:02)
Complex Data / Structures (27:20)
- Recursive CTE (05:13)
- Introduction (00:36)
- Overview (00:24)
- Data Type Precedence (02:15)
- IMPLICIT Conversion (01:02)
- Multinational Data (03:14)
- Collation Labels (01:52)
- Collation Precedence (02:06)
- Demo: COLLATE Clause (03:30)
- Query Hierarchical Data (01:37)
- Multi-Referential (00:49)
- Adjacency List (02:28)
- Demo: CTE w/Recursion (05:26)
- Summary (00:24)
Write Efficient Queries (19:32)
- Introduction (00:41)
- Overview (01:37)
- EXISTS Clause (02:05)
- Demo: EXISTS Clause (03:12)
- JOINS vs. Subqueries (05:07)
- One-Pass Queries (01:00)
- Demo: One-Pass Queries (05:20)
- Summary (00:26)
Module 19
Write Efficient Queries 2 (25:41)
- Introduction (00:33)
- Multiple ResultsSets (00:58)
- Temporary Tables (04:54)
- Table Variables (04:29)
- UDFs (03:31)
- Demo: UDFs (10:43)
- Summary (00:30)
Write Efficient Queries 3 (20:23)
- Introduction (00:35)
- Common Table Expression (01:34)
- Demo: CTEs (03:40)
- Worktables (01:44)
- Demo: Worktables (12:31)
- Summary (00:17)
Working w/Complex Query (23:03)
- Introduction (00:40)
- Overview (00:58)
- Implicit Transactions (03:48)
- Demo: Implicit Transactions (03:35)
- Use Sub Tasks (03:33)
- Demo: Use Sub Tasks (10:08)
- Summary (00:18)
Maintain Query Files (27:03)
- Introduction (00:37)
- Overview (01:23)
- Source Control (02:05)
- Visual SourceSafe (02:24)
- Put Files in Source Control (00:45)
- Demo: Source Control (06:26)
- Keyword Expansion/Comment (02:44)
- Enable Keyword Expansion (01:15)
- Demo: Keyword Expansion (02:37)
- Version Information (01:18)
- Demo: Retrieve Version Info (03:04)
- Demo: Compare Versions (01:54)
- Summary (00:25)
Module 20
XML Data Types (25:19)
- Introduction (00:32)
- Summary (01:31)
- Overview (01:01)
- Declare XML Objects (00:57)
- Demo: XML Data Type (02:51)
- XML Data Type Restrictions (01:54)
- Load XML Data (00:43)
- Demo: Load XML Data (02:09)
- SELECT...FOR XML (01:44)
- Demo: SELECT...FOR XML (02:07)
- Bulk Load w/OPENROWSET (02:13)
- Demo: OPENROWSET (02:45)
- Indexing Columns (03:42)
- Demo: Indexing Columns (02:08)
XML Schema Collections (25:32)
- Introduction (00:43)
- Overview (00:58)
- Typed/Untyped XML (01:30)
- Schema Basics (01:08)
- Demo: Examine XSD File (04:26)
- Register Schemas (02:39)
- Demo: Register Schema (06:55)
- View Stored Schema (01:44)
- Catalog Views/Functions (00:27)
- sys.xml_schema_collections (00:29)
- sys.xml_schema_namespaces (00:25)
- sys.xml_schema_components (01:33)
- xml_schema_namespace (02:01)
- Summary (00:28)
Query XML (26:15)
- Introduction (00:37)
- Overview (00:41)
- XQuery (01:21)
- query() Method (00:26)
- value() Method (00:53)
- exist() Method (00:45)
- modify() Method (00:26)
- nodes() Method (00:40)
- Demo: XQuery (04:58)
- FOR XML (01:05)
- OPENXML (03:37)
- Demo: FOR XML (05:48)
- Demo: OPENXML (04:32)
- Summary (00:21)
Best Practices (10:07)
- Introduction (00:39)
- XML Support (03:20)
- Storing XML (01:31)
- Index XML Columns (02:30)
- Querying (01:33)
- Summary (00:32)