Transact-SQL Programming Objects for Developers

SQL300 : 2 Day Instructor-Led Course

The next level of T-SQL in SQL Server 2005/2008

This two-day instructor-led course provides students with product knowledge and skills needed to implement advanced T-SQL strategies on Microsoft SQL Server 2005/2008. The course focuses on teaching individuals how to use additional Transact SQL Server features and tools related to developing and querying a database.

Prerequisites: Before attending this course, students must have:
  • Basic to Intermediate knowledge of the Microsoft SQL Server database management system and its core functionality
  • Can write SQL SELECT statements using Joins, Subqueries and Grouping.
  • Can write SQL UPDATE, INSERT and DELETE statements.
  • Working knowledge of relational databases
In addition, it is required, that students have completed or have equivalent knowledge:
Audience: This course is intended for information technology (IT) professionals and developers who need to implement Microsoft SQL Server 2005/2008 database solutions beyond basic Transact SQL queries.
 
Topics Covered:
  • Implementing Data Integrity by Using DML Triggers
  • Using Procedural Transact-SQL language statements
  • Executing Transact-SQL scripts in batch and command files
  • Using XML
  • Implementing Views
  • Implementing Stored Procedures and Functions
  • Implementing .NET CLR Managed Code in the Database
  • Considerations for Writing Efficient Transact-SQL statements
 
At Course Completion:
After completing this course, students will be able to:
  • Write and execute Transact-SQL scripts utilizing procedural language statements .
  • Turn relational data into well-formed XML. Shred XML data into a relational tables.
  • Search Full-Text Indexes using the different Full-Text search functions.
  • Design and Create Views.
  • Design and Create Stored Procedures in both Transact-SQL and .NET CLR
  • Design and Create User-Defined Functions in both TransactSQL and .NET CLR.
  • Use query optimization techniques and read query execution plans
 
Course Outline:
 
Module 1: Getting Started with Databases and Transact-SQL in SQL Server 2005/2008
 
The student will be introduced to how client/server architecture works, and examine the various database and business tasks that can be performed by using the components of SQL Server 2005/2008. The student will also be introduced to SQL Server database concepts such as relational databases, normalization, and database objects. In addition, the student will learn how to use T-SQL to query databases and generate reports.
 
Lessons
  • Overview of SQL Server 2005/2008
  • Overview of SQL Server Databases
  • Overview and Syntax Elements of T-SQL
  • Working with T-SQL Scripts
  • Using T-SQL Querying Tools
 
Lab 1: Using SQL Server Management Studio, SQLCMD and Excel
  • Lesson 1: Exploring the Components and Executing Queries in SQL Server Management Studio
  • Lesson 2: Starting and Using SQLCMD
  • Lesson 3: Generating a Report from a SQL Server Database Using Microsoft Office Excel
 
After completing this module, students will be able to:
  • Describe the architecture and components of SQL Server 2008.
  • Describe the structure of a SQL Server database.
  • Explain the basics of the SQL language.
  • Describe the syntax elements of T-SQL.
  • Manage T-SQL scripts in Management Studio Projects.
  • Use T-SQL querying tools to query SQL Server 2005/2008 databases.
 
Module 2: Querying Metadata, XML, and Full-Text Indexes
 
The students will learn to query semi-structured and unstructured data. The students will also learn how SQL Server 2008 handles XML data and will query XML data. The students will also be introduced to full-text indexing in SQL Server 2005/2008.
 
Lessons
  • Querying Metadata Overview of XML
  • Querying XML Data
  • Overview of Full-Text Indexes
  • Querying Full-Text Indexes
 
Lab 2: Creating Querying Metadata, XML, and Full-Text Indexes
  • Lesson 1: Querying Metadata
  • Lesson 2: Querying XML Data
  • Lesson 3: Creating and Querying Full-Text Indexes
 
After completing this module, students will be able to:
  • Query metadata.
  • Describe the functionality of XML.
  • Query XML data.
  • Join Relational tables with XML data using CROSS APPLY.
  • Describe the functionality of full-text indexes.
  • Query full-text indexes.
 
Module 3: Creating Using Programming Objects for Data Retrieval
The students will be introduced to user-defined functions and executing various kinds of queries by using user-defined functions. The students will be introduced to SQL Server views that encapsulate data and present users with limited and relevant information. In addition, the students will be introduced to SQL Server stored procedures and the functionalities of the various programming objects. The students will learn how to perform distributed queries and how SQL Server works with heterogeneous data such as databases, spreadsheets, and other servers.
 
Lessons
  • Overview of Views
  • Overview of User-Defined Functions
  • Overview of Stored Procedures
  • Overview of Triggers
  • Overview of SQL CLR Writing Distributed Queries
 
Lab 3: Creating and Using Programming Objects and Data Retrieval
  • Lesson 1: Creating User-Defined Functions
  • Lesson 2: Creating Stored Procedures
  • Lesson 3: Writing Distributed Queries
 
After completing this module, students will be able to:
  • Encapsulate queries by using views.
  • Encapsulate expressions by using user-defined functions.
  • Create stored procedures to encapsulate T-SQL logic.
  • Define triggers, types of triggers, create a trigger.
  • Write distributed queries.
  • Use SQL .NET CLR to improve performance in User Defined Functions.
 
Module 4: Using Advanced Techniques
 
The students will be introduced to best practices for querying complex data. The students will also examine how to query complex table structures such as data stored in hierarchies and self-referencing tables. The students will analyze the recommended guidelines for executing queries and how to optimize query performance.
 
Lessons
  • Considerations for Querying Complex Data
  • Querying Complex Table Structures
  • Writing Efficient Queries
  • Using Different Techniques for Complex Queries
  • Maintaining Query Files
 
Lab 4: Using Advanced Query Techniques
  • Exercise 1: Breaking up a Complex Business Reporting Requirement
  • Exercise 2: Writing Complex Queries
  • Exercise 3: Rewriting Complex Queries
 
After completing this module, students will be able to:
  • Query multi-level hierarchies using Common Table Expressions or HierarchyID’s.
  • Create dynamic pivot queries using dynamic SQL and the PIVOT operator.
  • Query complex table structures.
  • Use various techniques to improve performance when working with complex queries.
  • Read and understand key points in a query execution plan.