Program Databases with Transact-SQL

Course details

This course focuses on a single category of objects within a SQL Server database: Programmability objects. With these objects, database developers can automate systems with triggers, ensure consistent user experiences with stored procedures, protect database integrity with transactions, and implement error-handling routines that enhance the database’s functionality. Join instructor Adam Wilbert as he takes a deep dive into the Programmability folder in a SQL Server database. Using real-world examples, he explores the types of objects that can be deployed to automate systems and maintain database integrity. To follow along, you just need some familiarity with the functionality of a relational database, as well as how to write Transact-SQL (T-SQL) queries. Discover how to write stored procedures that include input and output parameters; create scalar- and table-valued user defined functions; execute batches of commands as transactions; and more.

Concepts

Introduction

Program databases with SQL Server
What you should know
Using the exercise files

Create Views of the Data

Save a query as a view object
Explore existing views
SchemaBind a view
Leverage view objects with indexes

Create User-Defined Functions

Deterministic vs. nondeterministic functions
Scalar-valued user-defined functions
IF ELSE and CASE statements
Table-valued user-defined functions
Challenge - Create a function
Solution - Create a function

Work with Stored Procedures

Write and execute a stored procedure
Stored procedure input parameters
Stored procedure output parameters
Create a database trigger
Challenge - Use function in a stored procedure
Solution - Use function in a stored procedure

Create Transactions

Ensure data consistency with transactions
Create a transaction
Transaction savepoints
Automatically roll back transactions
Challenge - Create a transaction
Solution - Create a transaction

Implement Error Handling

What is error handling
Capture errors with TRY and CATCH
Generate errors with THROW
Manage transaction control
Challenge - Add error handling to a stored procedure
Solution - Add error handling to a stored procedure

Data Type Conversions and NULLs

Implicit data type conversions
Understand NULL values
The ISNULL function
Merge rows with COALESCE

Conclusion

Next steps