SQL Programming @ Learning Tree

Learning Tree ,London, United Kingdom

Jun 2005 – London, United Kingdom

I attended this Transact-SQL Programming course in 2005 in order to enhance my SQL queries to ensure the CMS I build for UKTV would (and did over the past 10 years) withstand the growing demand and tighten security.

Course Content:

  • SQL Server Architecture
    • SQL Server edition overview
    • SQL Server Management Studio
  • Managing Tables with DDL
  • Creating schemas
    • Referencing schemas versus using the default schema
    • Hiding schemas with synonyms
  • Building tables
    • Selecting appropriate SQL Server data types
    • Constructing tables with CREATE TABLE
  • Adding constraints
    • Enforcing uniqueness using PRIMARY KEY and UNIQUE constraints
    • Validating relationships using FOREIGN KEY
  • Retrieving Data with Transact-SQL Stored Procedures
  • Batch and stored procedure processing
    • Minimising network traffic using batches and procedures
    • Stored procedure compilation and execution
    • Using scalar functions
  • Selecting data
    • Developing stored procedures that extract data from multiple servers
    • Executing dynamic queries using OPENROWSET and OPENQUERY
    • Executing remote procedures
    • Capturing RETURN values from stored procedures
  • Declaring variables and parameters
    • Creating and utilising local variables
    • Passing input and output parameters
  • Calling built-in scalar functions
    • Converting data using CAST and CONVERT
    • Ordering data with ranking functions
  • Maintaining Data
  • Modifying data
    • Ensuring data consistency with transactions and distributed transactions
    • Managing concurrency with isolation levels
    • SQL Server locking fundamentals
    • Avoiding blocking problems with read-committed snapshot isolation
  • Programming procedural statements
    • Implementing conditions with IF…ELSE
    • Looping with WHILE and GOTO
    • Creating code blocks with BEGIN…END
    • Debugging T-SQL in Management Studio
  • Handling errors
    • Communicating problems to the client with RAISERROR
    • Intercepting errors with TRY…CATCH
    • Dealing with open transactions
  • Producing server-side result sets
    • Building and using temporary tables
    • Processing rows on the server with a cursor
    • Taking advantage of table variables
  • Developing Views, Functions and Triggers
  • Storing queries on the server
    • Concealing complexity with views
    • Solving business problems using multistatement table-valued functions
  • Creating user-defined functions
    • Calculating values with scalar functions
    • Taking advantage of schema binding
  • Formulating triggers
    • INSTEAD OF vs. AFTER triggers
    • Detecting row changes using the inserted/deleted tables
    • Tracing metadata changes with DDL triggers
    • Auditing user access using a LOGON trigger
    • Tracking data changes with the OUTPUT clause