SQL Server Transact-SQL Programming

Course Outline

Transact-SQL is integral to the power of SQL Server as its features allow developers to create scalable, distributed applications for meeting the demanding requirements of modern organizations. In addition to comprehensive coverage of Microsoft SQL Server, this training course provides you with the skills to create stored procedures and triggers and develop T-SQL that utilizes SQL Server to the fullest.

SQL Server Transact-SQL Programming Benefits

Develop scalable, distributed applications with Transact-SQL to meet organizational requirements
Create modular code with stored procedures and formulate triggers
Develop reusable code with scalar- and table-valued functions
Handle Transact-SQL runtime errors to create robust software
Audit data changes using AFTER triggers
Leverage continued support with after-course one-on-one instructor coaching and computing sandbox

SQL Server Transact-SQL Programming Instructor-Led Course Outline

Important Course Information

Prerequisites

Learning Tree Learning Tree Course 2107: Microsoft SQL Server Training Course
Experience with the Windows user interface
Familiarity with the base features of SQL Server or DBMS

Who Should Attend?

This course benefits database developers, administrators, and analysts who want to expand their knowledge of SQL Server. Attendees should have experience with SQL Server at the level of Learning Tree Course 2107: Microsoft SQL Server Training Course

Exam Information

Learning Tree Exam provided at the end of class

Module 1: SQL Server Architecture

SQL Server edition overview
SQL Server Management Studio

Module 2: Managing Tables with DDL

Creating schemas

Referencing schemas versus using the default schema
Hiding schemas with synonyms

Building tables

Selecting appropriate SQL Server data types
Constructing tables with CREATE TABLE

Adding constraints

Enforcing uniqueness using PRIMARY KEY and UNIQUE constraints
Validating relationships using FOREIGN KEY

Module 3: Retrieving Data with Transact-SQL Stored Procedures

Batch and stored procedure processing

Minimizing network traffic using batches and procedures
Stored procedure compilation and execution
Using scalar functions

Selecting data

Developing stored procedures that extract data from multiple servers
Executing dynamic queries using OPENROWSET and OPENQUERY
Executing remote procedures
Capturing RETURN values from stored procedures

Declaring variables and parameters

Creating and utilizing local variables
Passing input and output parameters

Calling built-in scalar functions

Converting data using CAST and CONVERT
Ordering data with ranking functions

Module 4: Maintaining Data

Modifying data

Ensuring data consistency with transactions and distributed transactions
Managing concurrency with isolation levels
SQL Server locking fundamentals
Avoiding blocking problems with read-committed snapshot isolation

Programming procedural statements

Implementing conditions with IF...ELSE
Looping with WHILE and GOTO
Creating code blocks with BEGIN...END
Debugging T-SQL in Management Studio

Handling errors

Communicating problems to the client with RAISERROR
Intercepting errors with TRY ... CATCH
Dealing with open transactions

Producing server-side result sets

Building and using temporary tables
Processing rows on the server with a cursor
Taking advantage of table variables

Module 5: Developing Views, Functions, and Triggers

Storing queries on the server

Concealing complexity with views
Solving business problems using multi-statement table-valued functions

Creating user-defined functions

Calculating values with scalar functions
Taking advantage of schema binding

Formulating triggers

INSTEAD OF vs. AFTER triggers
Detecting row changes using the inserted/deleted tables
Tracing metadata changes with DDL triggers
Auditing user access using a LOGON trigger
Tracking data changes with the OUTPUT clause