Code: 
532
Duration: 
4 Day(s)
Price: 
£1596-2000

Overview

  • Adding a column to a database table using ALTER TABLE
  • Passing data into a stored procedure using parameters
  • Intercepting errors with TRY...CATCH
  • Calling a user-defined function in a SQL statement
  • Writing triggers to carry out advanced validation
  • Tracing metadata changes with DDL triggers

Your Choice

Customize your hands-on Learning Method with your choice of PC usage: Pair Up or Sole Use. Learn More ยป

Audience

Database developers, administrators and analysts who want to expand their knowledge of SQL Server and who have experience with SQL Server at the level of Course 2107, " SQL Server 2012 Comprehensive Introduction ."

Skills Gained

  • Develop scalable, distributed applications with Transact-SQL to meet organizational requirements
  • Create modular code using stored procedures and formulate triggers
  • Develop reusable code with scalar- and table-valued functions
  • Handle Transact-SQL runtime errors to create robust software
  • Audit data changes using AFTER triggers

Course Specifics

Course Outline

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
  • Minimizing 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 utilizing 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

Thinking about Onsite?

If you need training for 3 or more people, you should ask us about onsite training. Putting aside the obvious location benefit, content can be customised to better meet your business objectives and more can be covered than in a public classroom. It's a cost effective option.

Submit an enquiry from any page on this site, and let us know you are interested in the requirements box, or simply mention it when we contact you.

Upcoming Dates

Course Location Days Cost Date
Onsite
4 1596 £1596 2018-07-16
Cardiff
4 2000 £2000 2017-10-23
Liverpool
4 2000 £2000 2017-10-16
Worcester
4 1596 £1596 2017-10-02
Leeds
4 2000 £2000 2017-08-09
Manchester
4 2000 £2000 2017-08-08
Glasgow
4 2000 £2000 2017-07-25
Reading
4 2000 £2000 2017-07-24