0345 4506120

(TSQL) SQL Server Transact SQL Programming

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 called 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 through to SQL Server 2016.

  Reset

Pre-Requisites

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

Course Content

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
  • Freeing A Cursor With The DEALLOCATE Statement
  • Working With Cursors
  • INSENSITIVE Cursor
  • SCROLL Cursors
  • Fetching Data With A SCROLL Cursor
  • The @@CURSOR_ROWS Variable
  • Working With Very Large Data Sets
  • Changing Data Through Cursors
  • READ ONLY Cursors
  • Limited UPDATE Cursors
  • Programming With Cursors
  • Cursors & Locking

Stored Procedures

  • Pre-Compiled Code
  • Creating Stored Procedures
  • Encrypting Stored Procedures
  • Recompiling Stored Procedures
  • Creating A Stored Procedure WITH RECOMPILE
  • Altering Stored Procedures
  • Deleting A Stored Procedure
  • Stored Procedure Security
  • Passing In Parameters
  • Initialising Parameters
  • Outputting Values
  • The RETURN Statement
  • Modular Design

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

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

Exams & Certification

No Exam

However, this course will help you to prepare for exam 70-761 Querying Data with Transact-SQL (M20761) and exam 70-762:  Developing SQL Databases  (M20762).

Related Courses

Privacy Notice

In order to provide you with the service requested we will need to retain and use your contact information in accordance with our Privacy Notice. If you choose to provide us with this information you explicitly consent to us using the information as necessary to provide the request service to you. If you do not agree please do not proceed to request the service from us.

Marketing Permissions

Would you like to receive our newsletter and other information on products and services which we think will be of interest to you by email. We will always treat your information with care and in accordance with our Privacy Notice. You are free to withdraw this permission at any time.

 

Our Customers Include