Overview
-
Transition from writing one-off queries to creating repeatable code elements. Explore the types of objects that can be deployed to automate systems and maintain database integrity.
Syllabus
-
Introduction
- Program databases with SQL Server
- What you should know
- Using the exercise files
- Save a query as a view object
- Explore existing views
- SchemaBind a view
- Leverage view objects with indexes
- 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
- 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
- Ensure data consistency with transactions
- Create a transaction
- Transaction savepoints
- Automatically roll back transactions
- Challenge: Create a transaction
- Solution: Create a transaction
- 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
- Implicit data type conversions
- Understand NULL values
- The ISNULL function
- Next steps
