SQL Server Transact-SQL Programming

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 organisations. 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 utilises SQL Server to the fullest.

SQL Server Transact-SQL Programming Delivery Methods

• In-Person

• Online

SQL Server Transact-SQL Programming Course Benefits

• Develop scalable, distributed applications with Transact-SQL to meet organisational 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® Introduction
• 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® Introduction

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

• Minimising 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 utilising 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