Course at a glance
Included in these subscriptions:
- Dev & IT Pro Video
- Dev & IT Pro Power Pack
| Release date | 11/10/2007 | |
| Level | Intermediate | |
| Runtime | 9h 21m | |
| 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
SQL Server 2005 includes a rich set of tools that go beyond the basics of querying and manipulating data. 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 about the new enhancements to the Transact-SQL programming language and how programmers can now use .NET languages like C# and Visual Basic to build database objects.
Prerequisites
This course does not require any prior experience with Microsoft SQL Server or with .NET programming. However, several modules do include examples of .NET code. Students without any prior programming experience may find the code difficult to understand, but these sections comprise a very small portion of the course. The code samples for these sections appear in both Visual Basic and Visual C# versions.
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 (27:59)
- Introduction (01:30)
- 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
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 4
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 and 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)
Module 5
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)
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)
Module 6
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)
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)
Module 7
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)
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)
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)