How To Take This Class

Course Overview

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 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 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.

What You'll Learn

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 Duration

4 Days

Course Outline

  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