T-SQL with SQL Server 2008 Training

Course Duration:

4 Days

Course Description:

In this Transact-SQL Server 2008 Training course, students will learn about the features that are available in SQL Server like how to design and create a database, and how to build basic queries using Transact-SQL, the language of SQL Server. Then students will learn how to build effective views, stored procedures, triggers, and user-defined functions using Transact-SQL. Students will also learn how to use the Transact-SQL programming language for error handling and hierarchical queries, dealing with complex data structures and processes, and how to make databases more scalable through partitioning. SQL Server 2008 includes a rich set of tools that go beyond the basics of querying and manipulating data. Students will learn how to take advantage of the user-friendly management console that integrates both authoring and administrative tasks, and will learn how to take advantage of SQL Server’s tools for analyzing and tuning databases.

Course Objectives:

Upon successful completion of this course, students will be able to:

• Understand the differences between the available editions of SQL Server 2008.
• Create a SQL Server database based on sound design principles
• Create constraints, triggers, and indexes.
• Use Transact-SQL INSERT, UPDATE and DELETE statements.
• Configure and use SQL Server Management Studio (SSMS).
• Use Transact-SQL with data types, delimiters and variables.
• Understand the differences between views, stored procedures, triggers, and user-defined functions.
• Write and use stored procedures to provide a safe, controlled, efficient way to execute Transact-SQL code to access and update data and database objects.
• Understand how transactions can protect the integrity of your data and ways to handle errors that occur when code executes.
• Understand the complex data types in SQL Server, such as for spatial and hierarchical data, and how they support complex data operations.
• Partition data to store data in ways that improves the efficiency of queries.
• Understand the XML data type and its properties and methods, and how it supports both free form and relational data.

Course Audience:

This course is designed for developers, analysts and others who want to attain a solid understanding of basic T-SQL syntax and uses within SQL Server 2008.

Course Prerequisites:

To get the most out of the Microsoft T-SQL Server 2008 course, students should have a solid understanding of relational databases. No particular programming experience is required, but the course is taught from a developer’s perspective.

Course Syllabus:

1. A Tour of SQL Server 2008
• SQL Server 2008 Editions, Components, and Tools
• Using SQL Server Management Studio (SSMS)
• Working with Tables, Queries, and Views
• Business Intelligence Services
2. Designing and Creating a Database
• Relational Database Design Principles
• Implementing the Design
3. Working with SQL Server Management Studio
• Getting Started with Management Studio
• Exploring the Object Explorer
• Working with the Query Editor
• Using SQL Server Books Online
4. Transact-SQL Programming
• Overview of Transact-SQL
• Using Built-In Functions
• Controlling Flow
• Ranking Results
5. Transactions and Error Handling
• Transaction Concepts
• Applications and Transactions
• Creating Explicit Transactions
• Using TRY/CATCH Error Handling
6. Data Selection Queries
• Understanding Transact-SQL
• The SELECT Statement
• The WHERE Clause
• Using ORDER BY to Sort Data
• The GROUP BY Clause
• Joining Tables
7. Advanced Data Types
• Introduction
• The HierarchyID Data Type
• Sparse Columns and Column Sets
• FILESTREAM Storage
• Spatial Data
8. Complex Querying
• Working with NULL Values
• Ranking Grouped Data
• Writing Correlated Subqueries
• Using Common Table Expressions
9. Modifying Data
• Modifying Data with Transact-SQL
• Inserting Data
• Updating Data
• Deleting Data
• Understanding Transaction Isolation
10. Working with XML
• The XML Data Type
• XML Schema Collections
• Querying XML
• Best Practices
11. Creating Views
• What Is a View?
• Creating Views
• Updating Data Using a View
• Using Computed Columns
• Indexed Views
• Partitioned Views
12. Implementing SQL Server Partitions
• Overview of Table-Based Partitions
• Creating Partitioned Tables
• Querying Partitions
• Managing Partitions
13. 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
14. Creating Stored Procedures and Triggers
• Creating Stored Procedures
• Creating Triggers
15. Advanced Query Techniques
• Full-Text Search
• Advanced T-SQL Techniques
• Executing Dynamic SQL
16. Advanced Techniques
• Complex Data and Structures
• Writing More Efficient Queries
• Working with Complex Queries
• Maintaining Query Files