SQL Server Transact-SQL Programming

Course 532

  • Duration: 4 days
  • Labs: Yes
  • Language: English
  • Level: Intermediate

Transact-SQL is integral to the power of SQL Server as its features allow developers to create scalable, distributed applications for meeting the demanding requirements of modern organizations. In addition to comprehensive coverage of Microsoft SQL Server, this training course provides you with the skills to create stored procedures and triggers and develop T-SQL that utilizes SQL Server to the fullest.

SQL Server Transact-SQL Programming Delivery Methods

  • In-Person

  • Online

SQL Server Transact-SQL Programming Course Benefits

Develop scalable, distributed applications with Transact-SQL to meet organizational requirements

Create modular code with 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

Leverage continued support with after-course one-on-one instructor coaching and computing sandbox

SQL Server Transact-SQL Programming Instructor-Led Course Outline

Prerequisites

Who Should Attend?

This course benefits database developers, administrators, and analysts who want to expand their knowledge of SQL Server. Attendees should have experience with SQL Server at the level of Learning Tree Course 2107: Microsoft SQL Server Training Course

Exam Information

Learning Tree Exam provided at the end of class

  • SQL Server edition overview
  • SQL Server Management Studio

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

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

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

Storing queries on the server

  • Concealing complexity with views
  • Solving business problems using multi-statement 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

Need Help Finding The Right Training Solution?

Our training advisors are here for you.

SQL Server Transact-SQL Programming FAQs

Yes! This course benefits database developers, administrators, and analysts who want to expand their knowledge of SQL Server. This is a great follow-on for those who just came out of the introduction course!

Yes! Learning Tree introduced this course as a replacement course for the Microsoft legacy course retirement; Querying Data with Transact-SQL (20761).  

Yes, this course benefits database developers, administrators, and analysts who want to expand their knowledge of SQL Server. Attendees should have experience with SQL Server at the level of Learning Tree Course 2107: Microsoft SQL Server Training Course

Chat With Us