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.)