SQL Server 2005 T-SQL with Advanced Topics Training Class

Course ID: 20862

SQL Server 2005 T-SQL with Advanced Topics Training Seminar

In this course, you'll start by learning about the basic features available in SQL Server, including how to design and create a database and how to build queries using Transact-SQL, the language of SQL Server. Then, you'll learn to build effective views, stored procedures, triggers, and user-defined functions using Transact-SQL. You'll explore 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. You'll also examine enhancements made to SQL Server 2005 for handling complex data structures, using partitioning, and working with XML.


	Agenda
	1. A Tour of SQL Server 2005 SQL Server 2005 Editions, Components, and Tools Using SQL Server Management Studio (SSMS) Working with Tables and Queries New Transact-SQL Features Business Intelligence Services 2. Designing and Creating a Database Relational Database Design Principles Implementing the Design 3. Data Selection Queries Understanding Transact-SQL The SELECT Statement The WHERE Clause Using ORDER BY to Sort Data The GROUP BY Clause Joining Tables 4. Modifying Data Inserting Data Updating Data Deleting Data Understanding Transaction Isolation 5. Working with SQL Server Management Studio Getting Started with SSMS Exploring the Object Explorer Working with the Query Editor Using SQL Server Books Online 6. Transact-SQL Programming Overview of Transact-SQL Using Built-In Functions Controlling Flow Ranking Results 7. Transactions and Error Handling Transaction Concepts Applications and Transactions Creating Explicit Transactions Using TRY/CATCH Error Handling 8. Creating Views What Is a View? Creating Views Updating Data Using a View Using Computed Columns Indexed Views Partitioned Views 9. Creating Stored Procedures and Triggers Creating Stored Procedures Creating Triggers 10. Creating User-Defined Functions User-Defined Function Overview Scalar Functions Inline Table-Valued Functions Multi-Statement Table-Valued Functions Using Functions, Views, and Stored Procedures 11. Using .NET Code in SQL Server 2005 Introduction Writing SQLCLR Code SQLCLR Code Modules Managing Code Modules SQLCLR Security T-SQL vs. .NET Code 12. Advanced Query Techniques Full-Text Search Generating XML with FOR XML Using APPLY Creating Recursive Queries Creating Pivot Queries Executing Dynamic SQL 13. Implementing SQL Server Partitions Overview of Table-Based Partitioning Creating Partitioned Tables Querying Partitions Managing Partitions 14. Complex Querying Working with NULL Values Ranking Grouped Data Writing Correlated Subqueries Using Common Table Expressions 15. Advanced Techniques Complex Data and Structures Writing More Efficient Queries Working with Complex Queries Maintaining Query Files 16. Working with XML The xml Data Type XML Schema Collections Querying XML Best Practices


	Audience
	Database administrators, developers, Business Intelligence Administrators and Analysts and report writers for SQL server, as well as other relational database professionals, developers, business analysts, report writers, and ETL professionals.


	Pre-requisites
	Working experience with SQL Server 2000 or 2005; basic relational database concepts (e.g., tables, queries, and indexing); general knowledge of XML, Transact-SQL, and a fundamental understanding of networking and security concepts.