SQL Advanced

Course Overview (2 days)

This two day course assumes that you know how to query data using SQL, and shows you how to really progam in SQL Server

Course Content

Stored Procedures

  • 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 alternatives

 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
  • 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 @@error function

Debugging

  • The SQL Server debugger
  • Debugging (breakpoints, etc.)