Transact SQL Server 2008

In this course, you 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 you will learn how to build effective views, stored procedures, triggers, and user-defined functions using Transact-SQL. You will 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 your databases more scalable through partitioning.

SQL Server 2008 includes a rich set of tools that go beyond the basics of querying and manipulating data. You will learn how to take advantage of the user-friendly management console that integrates both authoring and administrative tasks. You will learn how to take advantage of SQL Server's tools for analyzing and tuning your databases.

Class Agenda:

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

Designing and Creating a Database

  • Relational Database Design Principles
  • Implementing the Design

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

Transact-SQL Programming

  • Overview of Transact-SQL
  • Using Built-In Functions
  • Controlling Flow
  • Ranking Results

Transactions and Error Handling

  • Transaction Concepts
  • Applications and Transactions
  • Creating Explicit Transactions
  • Using TRY/CATCH Error Handling

Data Selection Queries

  • Understanding Transact-SQL
  • The SELECT Statement
  • The WHERE Clause
  • Using ORDER BY to Sort Data
  • The GROUP BY Clause
  • Joining Tables

Advanced Data Types

  • Introduction
  • The HierarchyID Data Type
  • Sparse Columns and Column Sets
  • FILESTREAM Storage
  • Spatial Data

Complex Querying

  • Working with NULL Values
  • Ranking Grouped Data
  • Writing Correlated Subqueries
  • Using Common Table Expressions

Modifying Data

  • Modifying Data with Transact-SQL
  • Inserting Data
  • Updating Data
  • Deleting Data
  • Understanding Transaction Isolation

Working with XML

  • The XML Data Type
  • XML Schema Collections
  • Querying XML
  • Best Practices

Creating Views

  • What Is a View?
  • Creating Views
  • Updating Data Using a View
  • Using Computed Columns
  • Indexed Views
  • Partitioned Views

Implementing SQL Server Partitions

  • Overview of Table-Based Partitions
  • Creating Partitioned Tables
  • Querying Partitions
  • Managing Partitions

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

Creating Stored Procedures and Triggers

  • Creating Stored Procedures
  • Creating Triggers

Advanced Query Techniques

  • Full-Text Search
  • Advanced T-SQL Techniques
  • Executing Dynamic SQL

Advanced Techniques

  • Complex Data and Structures
  • Writing More Efficient Queries
  • Working with Complex Queries
  • Maintaining Query Files

Prerequisites

To get the most out of the Microsoft T-SQL Server 2008 course, you 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 Benefits:

  • 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.
  • How to 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.
  • Discover 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.
  • How to partition data to store data in ways that improves the efficiency of queries.
  • Investigate the XML data type and its properties and methods, and how it supports both free form and relational data.

Materials Furnished:

  • Student Manual

Course Length: Four Days

List Price: $2195