Transact SQL Programming (TSQL)

Duration: 3 Days

Objectives

This course is aimed at SQL Server database developers, administrators and analysts who need a little bit more than SQL can do alone. SQL Server provides a powerful scripting language, Transact SQL, which enables developers to extend the capabilities of the SQL language. Using Transact SQL (TSQL) powerful scripts, stored procedures, functions and triggers can be produced to enable extracted information to be processed further, or to enable complex transformations of data prior to, during or after an insert, update or delete, or to implement automated updates and audit trails. Covers SQL Server 2005, SQL Server 2008, SQL Server 2012 and SQL Server 2014.

Pre-requisites

Delegates should have SQL Server SQL language experience to the level of our Advanced Querying of SQL Server Databases course.

Course includes

A comprehensive set of reference notes covering course topics and follow-up telephone support.

Course outline

Transact SQL Scripting

  • The USE Statement
  • Using Variables
  • Data Types
  • Variable Scope
  • Table Variables
  • The PRINT Statement
  • The IF Statement
  • BEGIN and END
  • The WHILE Statement

Error Handling

  • The @@ERROR Variable
  • The RETURN Statement
  • The RAISERROR Statement
  • Structured Error Handling
  • TRY CATCH Blocks

Transaction Logging

  • BEGIN TRAN
  • COMMMIT TRAN
  • ROLLBACK TRAN

Locking

  • Row Level Locking
  • Key Level Locking
  • Page Level Locking
  • Extent Level Locking
  • Table Level Locking
  • Database Level Locking
  • Transactions and Locks
  • Transaction Isolation Levels
  • Deadlocks

Implementing Cursors

  • Transact-SQL Cursors
  • Monitoring Transact-SQL Cursor Activity
  • Declaring A Cursor With The DECLARE Statement
  • Opening A Cursor With The OPEN Statement
  • Fetching Records with The FETCH Statement
  • Closing The Cursor With The CLOSE Statement
  • Working With Very Large Data Sets
  • Changing Data Through Cursors
  • READ ONLY Cursors
  • Limited UPDATE Cursors
  • Programming With Cursors
  • Cursors & Locking
  • Freeing A Cursor With The DEALLOCATE Statement
  • Working With Cursors
  • INSENSITIVE Cursor
  • SCROLL Cursors
  • Fetching Data With A SCROLL Cursor
  • The @@CURSOR_ROWS Variable

Stored Procedures

  • Pre-Compiled Code
  • Creating Stored Procedures
  • Encrypting Stored Procedures
  • Recompiling Stored Procedures
  • Creating A Stored Procedure WITH RECOMPILE
  • Altering Stored Procedures
  • Modular Design
  • Deleting A Stored Procedure
  • Stored Procedure Security
  • Passing In Parameters
  • Initialising Parameters
  • Outputting Values
  • The RETURN Statement
  • User-Defined Functions
  • What is a User-Defined Function?
  • Usage of User-Defined Functions
  • Creating a User-Defined Scalar Function
  • Using a User-Defined Scalar Function
  • Table-Valued Functions
  • Creating a User-Defined Inline Table-Valued Function
  • Multi-Statement Table-Valued Functions
  • Viewing Existing User-Defined Functions
  • Modifying and Dropping User-Defined Functions

Triggers

  • Triggers & Constraints
  • Realistic Application Of Triggers
  • Automatic Updates
  • Data Integrity
  • Auditing
  • Stored Procedures & Triggers
  • Creating Triggers
  • Update & Insert Triggers
  • Altering Triggers

Course Dates

London CentralLondon GreenwichBristol

London Greenwich

Dates Price Book Now
4 - 6 Jul £1195 Book Now
1 - 3 Aug £1195 Book Now
26 - 28 Sep £1195 Book Now
30 Oct - 1 Nov £1195 Book Now
4 - 6 Dec £1195 Book Now
See more dates...
All our courses run in London and Bristol, or can be tailored to requirements at your own offices. For alternative dates, please email or call us on 0208 269 4260
All prices are per person and are subject to VAT
15% discount for groups of four delegates.
10% discount for groups of three delegates.
(This offer applies for delegates on the same public scheduled course on the same date.)

Related Courses


"Very thorough course and enthusiastic trainer who was very helpful and responsive to individual needs."

NVIDIA

"The course was excellent - exactly to my company's needs. All questions were answered and concepts explained very clearly."

Hannah Griffiths

"I wish all training courses were run this well!"

RPS Design



 

 

© Transmedia . 277 Greenwich High Road London SE10 8NB  UK . 0208 269 4260
Designed by Transmedia's Web Team