Advanced SQL
Need course dates or locations to suit you? CLICK HERE TO SEND ENQUIRY...  
Advanced SQL

Course Contents
The contents of our Advanced SQL course are as follows (any items marked with a * will be covered if time allows):

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

Deleting and updating
- Using DELETE and UPDATE
- Sys.Objects
- Dropping objects

Creating tables
- Creating tables in SQL
- Primary keys and indexes
- Setting constraints
- Creating tables from existing data

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
- Mult-statement table-valued functions
- Limitations of user-defined functions

Derived tables and CTEs
- Using dervied 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.)

Dynamic SQL (*)
- Building up dynamic SQL
- Executing dynamic SQL
- Pros and cons

 
Need course dates or locations to suit you? CLICK HERE TO SEND ENQUIRY...  
My Location
Results for location are highlighted in the table below. Click here to remove highlight
The course either has new dates in data loading, or is only run as a dedicated or In Company course.
Tools
 
Key Details
Duration2 days
 
£695
per delegate
 
 
Bookings Helpline: 01933 233884 (Monday to Thursday - 8:00am to 5:30pm and Friday 8:00am to 5:00pm).