Course at a glance
Included in these subscriptions:
- Dev & IT Pro Video
- Dev & IT Pro Power Pack
| Release date | 5/3/2006 | |
| Level | Beginner | |
| Runtime | 11h 48m | |
| Closed captioning | N/A | |
| Transcript | N/A | |
| eBooks / courseware | N/A | |
| Hands-on labs | N/A | |
| Sample code | Included | |
| Exams | N/A | |
| Platform | Internet Explorer Only |
Course description
Want to get up to speed on Microsoft SQL Server 2005? SQL Server 2005 is a major upgrade from its very successful predecessor, SQL Server 2000. This course introduces you to the new technological advances in SQL Server 2005 that provide businesses decreased downtime, increased scalability and performance, and set a new standard on data security. You will also learn about many new features that greatly reduce application development time allowing developers to be highly productive.
Meet the expert
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
Intro to SQL Server 2005 (36:32)
- Introduction (03:06)
- Database Engine (05:04)
- Security (05:47)
- Availability and Recovery (02:25)
- Analysis Services (01:55)
- Integration Services (01:19)
- Reporting Services (01:42)
- Service Broker (02:40)
- Notification Services (00:54)
- Installation (01:11)
- Installation Demo (09:16)
- Summary (01:08)
Tools (30:33)
- Introduction (01:01)
- Management - Dev Tools (01:21)
- Management Studio (07:46)
- Management Studio Options (03:40)
- Summary Screen (02:38)
- Reports (04:24)
- Writing Code (03:14)
- Template Explorer (01:59)
- Objects with Projects (01:41)
- Help (02:09)
- Summary (00:36)
More SQL Management Tools (31:27)
- Introduction (00:52)
- SQL Server Profiler (05:44)
- Database Tuning Advisor (05:30)
- Configuration Manager (03:29)
- Surface Area Configuration (06:49)
- SQLCMD (08:09)
- Summary (00:50)
Module 2
Authorization (32:13)
- Introduction (01:50)
- Strong Password Policies (02:32)
- Permissions Catalog View (01:05)
- Password Policies (01:32)
- Password Expiration Policy (01:14)
- Changing a Login (00:56)
- Permissions (02:45)
- SQL 2005 Principals (01:32)
- Database-Level Principals (00:56)
- Securable Objects (01:31)
- New Permissions (02:26)
- Permissions Demo (04:26)
- ImplyingPermissions Demo (02:29)
- Metadata Visibility (02:25)
- Metadata Visibility Demo (03:42)
- Summary (00:44)
Security in Practice (36:47)
- Introduction (01:09)
- Execution Contexts (01:44)
- Ownership Chains (02:41)
- EXECUTE AS Options (04:17)
- EXECUTE Demo (05:58)
- When to Use Each Option (03:11)
- Schemas (03:43)
- Users and Schemas (02:29)
- Users and Schemas Demo (06:33)
- Synonyms (01:13)
- Default Schemas (02:28)
- Summary (01:16)
Encryption (13:45)
- Introduction (02:22)
- Algorithms (03:21)
- Encryption Support (02:57)
- Encryption Hierarchy (04:09)
- Summary (00:55)
More on Encryption (47:36)
- Introduction (00:50)
- Encryption Demo (03:34)
- Service Master Key (04:58)
- Database Master Key (07:14)
- Create a Certificate (03:29)
- Use a Certificate (04:04)
- Asymmetric Keys (09:21)
- Symmetric Keys (10:38)
- Encryption Catalog Views (01:37)
- Summary (01:47)
Module 3
SQLCLR (41:34)
- Introduction (01:40)
- Introduction to SQLCLR (02:00)
- Hosting the CLR (03:55)
- Why Use SQLCLR Code? (04:40)
- Code Module Options (01:08)
- Creating Raw SQLCLR Code (01:41)
- Create SQLCLR Code Demo (08:31)
- Create with Visual Studio (01:46)
- Create a UDF with VS (08:25)
- Deploy the UDF (00:40)
- Run from within VS (01:42)
- Deployment Notes (01:02)
- Data Access in SQLCLR (01:07)
- Return Results to Caller (02:26)
- Summary (00:46)
More SQLCLR (57:48)
- Introduction (01:22)
- User-Defined Functions (01:56)
- A UDF in Visual Studio (01:52)
- Stored Procedures (00:59)
- Write a SQLCLR Stored Proc. (08:37)
- User-Defined Aggregates (10:38)
- Triggers (01:48)
- User-Defined Types (01:39)
- Keeping Track of Objects (01:26)
- System Catalog Demo (03:24)
- Troubleshooting Code (02:20)
- Security in SQLCLR (02:43)
- DBA Perspective on SQLCLR (04:49)
- Permission Sets (03:30)
- Considerations for Non-SAFE (02:09)
- Secure Coding Practices (01:55)
- T-SQL vs SQLCLR (03:01)
- Middle Tier or Server? (02:26)
- Summary (01:05)
Module 4
SQL Management Objects (54:06)
- Introduction (00:53)
- Management Frameworks (02:25)
- SQL Management Objects (03:45)
- Why Use SMO? (02:53)
- SMO Object Model (01:42)
- SMO Object Model Diagram (01:26)
- SMO Class Types (01:23)
- SMO Features Demo (04:44)
- Code Behind the SMO Demo (01:09)
- Code to Find all Available DB (02:45)
- Code to Connect to the Server (05:22)
- Code to Select a Database (01:09)
- Code to Select an Object Type (03:55)
- Code to Select an Object (02:03)
- Create a Database (03:05)
- Delete a Database (01:26)
- Create a Table (01:55)
- Backup a DB (03:01)
- Script Tables (04:11)
- Capture SQL Statements (04:00)
- Summary (00:45)
SQL Express & VS Integration (29:30)
- Introduction (01:16)
- Problems/Limits with MSDE (03:25)
- Introducing SQL Express (05:39)
- Limitations of SQL Express (00:55)
- Client and Management Tools (01:38)
- Design Time Features (01:56)
- Auto Detach Feature (00:58)
- Create a DB App in VS 2005 (03:36)
- Deploy the Database App (01:13)
- Overview of Server Explorer (01:08)
- Server Explorer Features (02:13)
- SQL Reporting Services (01:32)
- Integrate SQL Reporting Serv. (03:06)
- Summary (00:48)
ADO.NET Enhancements (38:35)
- Introduction (01:04)
- New Data Types (02:02)
- Multiple Active Result Sets (03:06)
- Asynchronous Queries (03:24)
- Asynchronous Methods (01:16)
- Query Completion (01:38)
- Error Handling (01:25)
- Synchronous Query Example (00:49)
- Asynchronous Query Example (00:41)
- Async. Query Example Code (03:15)
- Sync. Query Example Code (01:13)
- Query Notifications (03:08)
- Using a Notification (02:43)
- Working with Service Broker (01:35)
- Rules for SqlDependency (03:51)
- Setup DB for Service Broker (01:54)
- App to Test SqlDependency (01:02)
- View the Code (01:11)
- TestSqlDependency() (01:54)
- OnDependencyChanged() (00:44)
- Summary (00:28)
Module 5
Client Access Enhancements (35:30)
- Introduction (01:23)
- Batch Updates (03:06)
- Bulk Copy (01:26)
- Run the Bulk Copy Demo (00:37)
- View BulkCopyClass() Code (02:12)
- View InsertByBatch() Code (02:03)
- Setup/Run SQL Server Profiler (00:52)
- View the Profiler Trace Results (01:03)
- Changes to the DataTable (02:08)
- Stream Data into DT/DS (01:40)
- DataTable Example Code (03:04)
- Run the DataTable Example (01:04)
- DataTableReader (01:14)
- Run a DataTableReader (00:30)
- DataTableReader Ex. Code (02:27)
- DataTable from a DataView (02:09)
- Convert a DV to a DT (01:42)
- Rowstate has been Enhanced (01:11)
- New Rowstate Features Ex. (00:40)
- View Code for Rowstate Ex. (03:43)
- Summary (01:07)
XML (01:44:48)
- Introduction (01:37)
- XML Data (03:22)
- XML Data Type (02:33)
- Typed XML Data (04:08)
- Indexing XML Data (03:41)
- Secondary XML Indexes (01:45)
- XML Schema Demo (03:57)
- Access XML Schema Info (02:58)
- Using the XML Schema (04:01)
- XML Indexes Demo (01:24)
- View the Execution Plan (02:02)
- Querying XML (03:27)
- XML Data Type Methods (04:51)
- XQuery SQL Statements (03:49)
- XML Data Type query() Ex. (30:59)
- query() - Order Count (02:51)
- query() - Path Expression (01:23)
- query() - FLWR Expression (01:45)
- query() - Conditional Exp. (02:52)
- query() - Quantified Exp. (02:38)
- query() method - Sorting (00:53)
- value() method Examples (02:42)
- exist() method Examples (02:42)
- nodes() method Examples (02:35)
- modify() method Examples (06:24)
- XQuery Limits in SQL Server (02:19)
- Summary (00:58)
Module 6
T-SQL XML Enhancements (35:04)
- Introduction (01:16)
- Retrieving XML (03:25)
- FOR XML AUTO (02:26)
- XML AUTO, TYPE (00:53)
- XML AUTO, TYPE, ROOT (00:55)
- Path for Returning XML Data (04:31)
- Include Multiple Paths (00:55)
- Output Column Data as a TN (01:12)
- Handle Missing Element Info (01:46)
- Include an Inline XML Schema (01:47)
- Nested FOR XML Queries (01:57)
- Shredding XML (01:35)
- OpenXML Enhancements (06:22)
- XML Bulk Load (01:39)
- Load XML Data With Bulk Load (03:12)
- Summary (01:07)
Web Services (20:06)
- Introduction (01:30)
- Exposing Web Services (02:23)
- WS on Windows Server 2003 (02:03)
- HTTP Endpoints (03:42)
- Create a Stored Procedure (01:08)
- Create a Web Service (02:29)
- View the WSDL (00:45)
- Create Client to Consume WS (04:32)
- Test the Web Service (00:34)
- Summary (00:56)
T-SQL Enhancements - Part 1 (30:36)
- Introduction (01:23)
- New Data Types (02:09)
- TOP (01:30)
- Using TOP (04:33)
- DML (01:13)
- Using DML Output (04:28)
- TABLESAMPLE (02:13)
- Using TableSample (02:32)
- Pivot/Unpivot Operators (00:57)
- Using Pivot Operator (03:21)
- Using Unpivot Operator (01:15)
- Intersect/Except Operators (02:06)
- Using Intersect (01:20)
- Using Except (00:47)
- Summary (00:45)
T-SQL Enhancements - Part 2 (31:27)
- Introduction (01:33)
- Ranking Functions (02:38)
- Using ROW_NUMBER() (01:14)
- Using Other Ranking Functions (02:25)
- Common Table Expressions (02:11)
- Using CTE (04:43)
- DDL Triggers (01:45)
- Using DDL Triggers (04:39)
- Try/Catch Error Handling (01:34)
- Try/Catch Details (01:37)
- Using Try/Catch (05:29)
- Summary (01:35)