Advanced SQL

Advanced SQL Course Outline

Advanced SQL Course Outline

Stored Procedure Basics

  • Pros and cons of stored procedures
  • Creating stored procedures
  • Three ways to execute
  • System stored procedures

Variables

  • Declaring variables
  • SET versus SELECT
  • Tricks with variables and rowsets
  • So-called global variables

Parameters and Return Values

  • Passing parameters
  • Default values and WHERE clauses
  • Output parameters
  • Using RETURN

Avoiding Scalar Functions

  • What are scalar functions?
  • Some examples
  • Disadvantages of scalar functions
  • Three advantages

Testing Conditions

  • IF/ELSE statement
  • Using CASE where possible

Looping

  • Syntax of 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

Creating Tables

  • Creating Tables in SQL
  • Inserting data
  • Inserting single rows
  • Inserting multiple rows

Temporary Tables and Table Variables

  • Using temporary tables
  • Creating table variables
  • Pros and cons of each approach

Table Valued Functions

  • In-line table-valued functions
  • Multi- Statement table-valued functions
  • Limitations of user-defined functions

Derived Tables and CTEs

  • Using derived tables
  • Common Table Expressions (CTEs)
  • Recursive CTEs

Subqueries

  • The concept of a subquery
  • Using ALL, ANY, and IN
  • Correlated subqueries
  • Using EXISTS

Cursors

  • Syntax of fetching rows
  • When to use (and when not to)

Error-Handling

  • Using TRY/CATCH
  • System error functions
  • Custom error messages
  • The obsolete @@ error function

Debugging

  • Version differences
  • The Visual Studio debugger
  • The SQL Server debugger
  • Debugging (breakpoints, etc.)