MS SQL Course, Advanced, 2-Day Course, Online Instructor-Led

Derived Tables and CTEs ·Using SQL derived tables ·SQL Common Table Expressions (CTEs) ·Recursive CTEs Create, alter and drop Views, Indexes SQL Stored Procedure Basics ·Stored Procedure Principles ·Creating stored procedures ·Executing stored procedures ·System stored procedures Variables ·Declaring variables ·SET versus SELECT Parameters and Return Values ·Passing parameters ·Default values and WHERE clauses ·Output parameters ·Using RETURN Testing Conditions ·The IF/ELSE statement ·CASE ·CHOOSE Looping ·Syntax of SQL WHILE ·Breaking out of a loop ·Basic transactions ·Beginning a transaction ·Committing/rolling back ·Deleting and updating ·Using DELETE and UPDATE ·Sys.Objects ·Dropping objects User defined Functions Table Valued Functions ·In-line table-valued functions ·Multi- Statement table-valued functions ·Limitations of user-defined functions Scalar Functions ·What are scalar functions? ·Some examples ·Disadvantages of scalar functions ·Three advantages Cursors ·Syntax of fetching rows ·When to use (and when not to) Triggers Create or replace triggers Creating Tables in SQL ·Create, alter and drop Tables ·Inserting values, single rows and multiple rows using queries Constraints: Not Null Unique Default Check Primary Key Foreign Key Identity / Auto-increments Sequencing Indexes SQL Temporary Tables and Table Variables ·Create and use SQL temporary tables ·Create and use SQL table variables ·SQL Tables vs. Table Variables Error-Handling ·SQL TRY/CATCH ·SQL System error functions ·Customizing SQL error messages ·@@ error function Debugging ·The SQL Server debugger, Debugging (breakpoints, etc.)