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 organisations. 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 utilises SQL Server to the fullest.
SQL Server Transact-SQL Programming Delivery Methods
SQL Server Transact-SQL Programming Course Benefits
Develop scalable, distributed applications with Transact-SQL to meet organisational 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
- 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
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
