This course covers Microsoft’s SQL Server database programming techniques using Transact-SQL (T-SQL) programming constructs, with an emphasis on stored procedures and scripts. The curriculum also involves writing SQL scripts of increasing complexity. Additional course topics include creating and implementing functions using T-SQL and SQL CLR; views, triggers and transactions; how to protect your data with constraints; and structured error handling. 

• Structured Query Language (SQL) Level 1 or equivalent knowledge 
• Structured Query Language (T-SQL) Level 2 or equivalent knowledge 

Lesson 1: Automate data insertion and modification using scripts and basic programming constructs 

• Create scripts to perform insert and update tasks within the database 
• Add flow-control statements and logic to perform branched processing based on conditions 

Lesson 1 Assignments 

• Lab #1 

Lesson 2: Automate data insertion and modification using scripts and basic programming constructs 

• Code loops that make the script execute the workflow repeatedly over a series of data 
• Build in error handling routines using Try…Catch clauses 

Lesson 2 Assignments 

• Lab #2 
• Skill check #1 

Lesson 3: Convert SQL Scripts into procedures and functions to make the functionality available to other database users 

Transform scripts into packaged procedures and functions that can be stored in the database and executed as required 

• Use input and output parameters to pass information “on-the-fly” to/from the procedure/function 

Lesson 3 Assignments 

• Labs #3 and #4 

Lesson 4: Convert SQL Scripts into procedures and functions to make the functionality available to other database users 

Use the built-in debugging tool to help find errors in newly written procedures and functions. 

• Transform scripts into packaged procedures that can be stored in the database and executed as required 

Lesson 4 Assignments 

• Labs #5–#7 

Lesson 5: Convert SQL Scripts into procedures and functions to make the functionality available to other database users 

Transform scripts into packaged functions that can be stored in the database and executed as required 

• Integrate assemblies from other programming languages into SQL Server 

Lesson 5 Assignments 

• Lab #8 

Lesson 6: Convert SQL Scripts into procedures and functions to make the functionality available to other database users  

Lesson 6 Assignments 

• Lab #9 
• Skill check #2 

Lesson 7: Manage transactions in order to increase concurrent usage of the database  

• Create transaction-based workflows to ensure all required work is executed completely as a unit and does not produce any errors 
• Resolve issues related to lock contention and deadlock 
• Apply best practices for T-SQL programming to use transactions effectively and minimize potential concurrency pitfalls 

Lesson 7 Assignments 

• Lab #10 
• Skill check #3 

Lesson 8: Create SQL programming functions to provide flexible data integrity and error-handling rules 

• Code T-SQL scripts to provide advanced tests for the correctness of information entered into the database by users 

Create triggers that enable the SQL database to automatically take action based on a particular event occurring 

• Transform a script into a trigger which will execute automatically when the user makes a modification (i.e., insert, update or delete) to the database 

Lesson 8 Assignments 

• Skill check #4 

Lesson 9: Create triggers that enable the SQL database to automatically take action based on a particular event occurring 

• Build an audit trail of database inserts, updates and deletes using triggers to keep track of changes and previous values 

Lesson 9 Assignments 

• Lab #11 
• Skill check #5 

Lesson 10: Manage database query results row-by-row using SQL Cursors 

• Declare cursors to interact with the data a record at a time 
• Interact with the cursor using a loop to fetch data 

Lesson 10 Assignments 

• Lab #12 

Lesson 11: Manage database query results row-by-row using SQL Cursors 

• Navigate through the cursor results backward and forward in a scrollable fashion 
• Detect underlying data changes using a cursor 

Lesson 11 Assignments 

• Skill check #6