I passed the exam!  Woo hoo!  It feels great to have passed this milestone.  I want to finish up this series so I can help you pass too!  I will say there are some things I feel I need to study more after taking the exam, but I feel that way after every exam.  For now, we’ll jump right into the next section of the 70-457:

Modify Data

• Create and alter stored procedures (simple statements).
  • This objective may include but is not limited to: write a stored procedure to meet a given set of requirements; branching logic; create stored procedures and other programmatic objects; techniques for developing stored procedures; different types of stored procedure results; create a stored procedure for data access layer; program stored procedures, triggers, and functions with T-SQL
• Modify data by using INSERT, UPDATE, and DELETE statements.
  • This objective may include but is not limited to: given a set of code with defaults, constraints, and triggers, determine the output of a set of DDL; know which SQL statements are best to solve common requirements; use output statement

The trick with this section is you’ve got to know how applying a constraint and trigger could change what happens with a T-SQL statement.  For example, if you have a table with an instead of trigger that only flips a deleted flag to 1 when you run the delete command, you have to understand you aren’t going to be able to actually remove the row without disabling the instead of trigger.

If you have a value constraint on a column and you try to set that column to a value outside the valid range, you have to understand that an error will be raised.

Be comfortable with how these features affect the base operations INSERT, UPDATE, and DELETE.

• Work with functions.
  • This objective may include but is not limited to: understand deterministic and non-deterministic functions; scalar and table values; apply built-in scalar functions; create and alter user-defined functions (UDFs)

The short of deterministic versus non-deterministic is this: if your function returns the same results for the same input parameters, you’re dealing with a deterministic function.  For example, think about the LEFT(string, len) function, for a given string and length, it’s always going to return the same result.  The LEFT function is deterministic

Now consider the GETDATE() function.  It has no parameter, and every time you run it, you get a new result.  This function is non-deterministic.

Scalar functions return one value, and you can include them in your select list.

Table value functions return one or more rows, and one or more columns.  You have to APPLY these functions.

 

This is a shorter article, I think that’s because I do a lot of modifying data.  If you have any questions about the topics above, let me know… I’ll add content here to make it more complete!

Good luck in your attempts to pass the 70-457!