Writing Queries Using Microsoft SQL Server 2005 T-SQL
Printer friendly

Course Overview
  In this course, you'll learn how to build basic queries using Transact-SQL, the language of SQL Server. Then, you'll learn how to build effective views, stored procedures, triggers, and user-defined functions, using Transact-SQL. You'll learn about the new enhancements to the Transact-SQL programming language including improved support for error handling and hierarchical queries, and programmers can now use .NET languages like C# and Visual Basic to build database objects.

Objectives
  In this course, you will learn to:
  • Build queries in Transact-SQL to insert, update, delete, and retrieve data.
  • Create stored procedures to promote enhanced security and performance.
  • Take advantage of triggers and user-defined functions.
  • Utilize SQL server transactions for insure data integrity.
  • Leverage their .NET programming skills with SQLCLR.
  • Create highly optimized queries using advanced techniques.

Audience
  

Prerequisites
  This course is designed for database developers and SQL Server administrators who need to understand Transact-SQL. To get the most out of this 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 Number
  NHSQLSrvr05TSQL
Course Length
  3 Days
Course Price
  $1,650.00
Category
  2
Topics
  Outline

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
Designing and Creating a Database
  • Relational Database Design Principles
  • Implementing the Design
Data Selection Queries
  • Understanding Transact-SQL
  • The SELECT Statement
  • The WHERE Clause
  • Using ORDER BY to Sort Data
  • The GROUP BY Clause
  • Joining Tables
Modifying Data
  • Modifying Data
  • Inserting Data
  • Updating Data
  • Deleting Data
  • Understanding Transaction Isolation
Working with SQL Server Management Studio
  • Getting Started with SSMS
  • 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
Creating Views
  • What Is a View?
  • Creating Views
  • Updating Data Using a View
  • Using Computed Columns
  • Indexed Views
  • Partitioned Views
Creating Stored Procedures and Triggers
  • Creating Stored Procedures
  • Creating Triggers
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
Using .NET Code in SQL Server 2005
  • Writing SQLCLR Code
  • SQLCLR Code Modules
  • Managing Code Modules
  • SQLCLR Security
  • T-SQL vs. .NET Code
Advanced Query Techniques
  • Full-Text Search
  • Generating XML with FOR XML
  • Using APPLY
  • Creating Recursive Queries
  • Creating Pivot Queries
  • Executing Dynamic SQL