TSQL1 - Transact-SQL Programming
| Categories |
Database Training, SQL Training |
| Duration |
2 days (14 hours) |
| Pre-requisites |
MSSQL - SQL Using Microsoft SQL Server or equivalent experience using SQL in a relational database management system. Assumes a basic understanding of SQL and database terminology. Programming experience helpful but not required. |
|
|
|
| Courseware |
TSQL-BS-01 (108 pages) |
| Price |
$790 |
| Notes |
This course uses Microsoft SQL Server. The Transact-SQL language taught can also be used on Sybase with minor modifications.
|
This SQL training course covers programming techniques using Transact-SQL on Microsoft SQL Server. The goal of this SQL training course is to provide students with a solid foundation in Transact-SQL programming concepts for the purpose of creating and using stored procedures and triggers.
This SQL training course includes coverage of the enhancements included in the Transact-SQL language, Transaction Control, Locking, Stored Procedures and Triggers, Parameters, Variables, Control Statements, Data Types, Cursors, and more. This course also provides an overview of built-in System Procedures, Extended Procedures, and AutoStart Procedures. Students work hands-on with Transact-SQL to solve a variety of programming problems.
"[The instructor] was awesome! He's extremely knowledgable of the material and was willing to work with a begginer like myself. Take the ANSI class first.." -- Todd G. from San Diego, CA
"Excellent. The instructor was very clear and to the point. He managed the time very well and focused on issues that needed more attention.." -- Sassan R. from Torrance, CA
"[The instructor] is very clear and comfortable with the material that he is presenting. His expertise is quite visible.." -- Marybell Pelayo C. from San Diego, CA
"Everthing was great, I like the material (very important) and the lab setting was pefect." -- Robert J. from San Diego, CA
"I would recommend other people ro take this course. [The instructor] has a very professional attitude and it has been a pleasure taking this class.." -- Nellie V. from San Diego, CA
Overview of Transact-SQL
What is Transact-SQL?
Set-based Processing
Programming Support
Temporary Objects
Creating Temporary Objects
Temporary Tables
Functions and Procedures
Type of Functions
System Stored Procedures
Executing System Stored Procedures
Extended Stored Procedures
Cursors
Aggregate Operators
Transaction Control
Implicit Transactions
Explicit Transactions
Tracing and Debugging
DBCC Functions
SET Commands
Using the Built-in Debugger
Exception and Error Handling
RAISERROR
Practice Transact-SQL
Programming in Transact-SQL
Running Transact-Code Code
Transact-SQL Script Files
The GO Statement
Transact-SQL Batches
Variables
Declaring Variables
Assigning Values to Variables
Variable Scoping
Global Variables
Declaring Global Variables
Displaying Global Variables Values
Data Types
Types Conversions
Implicit Conversions
Explicit Conversions
CONVERT()
NULL
Expressions
Operators
Operator Precedence
Values
Literal Values
Column Values
Variable Values
Expression Values
Function Return Values
SELECT Statement Return Values
Conditions
Comparison Operators
Logical Operators
Writing Conditions in Transact-SQL
Statements
Expression Support
BETWEEN
IN
EXISTS
IS NULL
Statement Blocks
Branching
Conditional Processing
The IF Statement
Handling Multiple ELSE Parts
The CASE Function
Looping
Transact-SQL and SELECT Statements
The USE Statement
Using Transact-SQL with SELECT Statements
Practice - Programming in Transact-SQL
User Defined Functions
Function Types
Scalar Functions
Calling Functions
Parameters
Inline Table-Valued Functions
Multi-Statement Table-Valued Functions
Limitations of User Defined Functions
Practice - User Defined Functions
Transactions
Commands
BEGIN TRAN
COMMIT TRAN
SAVE TRAN
ROLLBACK TRAN
Implicit Transactions
Explicit Transactions
The ACID Test
Atomic
Consistent
Isolated
Durable
Errors in Transactions
@@ERROR
Named Transactions
Savepoints
Nested Transactions
Problems with Nested Transactions
@@TRANCOUNT
Transactions in Stored Procedures
The Transaction Log
DUMP TRANSACTION
Reducing Transaction Log Size
Practice - Transactions
Procedures
Creating Procedures
WITH ENCRYPTION
WITH RECOMPILE
Executing Stored Procedures
EXECUTE
Executing in a Batch
Executing from a Variable
EXEC()
Executing a Procedure
Executing Multiple SQL Statements
Executing Dynamic SQL
Guidelines for using EXEC()
Viewing Stored Procedures
Viewing Stored Procedure Text
Parameters
Input Parameters
Parameter Defaults
Output Parameters
Implicit Parameter Ordering
Explicit Parameter Ordering
The RETURN Statement
Storing Return Values
Procedure Types
Permanent
Temporary
Global Temporary Stored Procedures
Local Temporary Stored Procedures
System Stored Procedures
Creating System Stored Procedures
Extended Procedures
Creating Extended Stored Procedures
Registering Extended Stored Procedures
Remote Procedures
Auto-execution Procedures
Working with Procedures
Nested Procedures
Dependent Procedures
Practice - Procedures
Triggers
Creating Triggers
Pseudotables
Pseudotables and INSERT
Pseudotables and UPDATE
Pseudotables and DELETE
Testing for Update Status
UPDATE()
Pseudotables and JOINS
Using a Pseudotable for Lookups
Practice - Triggers
IF UPDATE()
Appendix A - Data Models