MCTS - Microsoft Certified Technology Specialist SQL Server Database Development Questions and Answers

Question 1

A developer needs to ensure that a series of data modifications within a stored procedure are treated as a single, atomic unit.
If an error occurs at any point, all prior modifications within the procedure should be undone.
Which T-SQL construct is most appropriate for handling this requirement?

Accepted Answer

A TRY...CATCH block combined with a transaction.

Answer

A cursor to iterate through the statements.

Answer

A temporary table to log changes before applying them.

Answer

An AFTER trigger on the target table.

Question 2

You are designing a large table that will store order history. The most frequent query pattern will be to retrieve all orders for a specific date range. To optimize for this query pattern, which of the following columns would be the best candidate for the clustered index key?

Accepted Answer

The OrderDate column, which is of the DATETIME data type.

Answer

A UNIQUEIDENTIFIER column populated with NEWID().

Answer

A combination of CustomerID and ProductID.

Answer

A NVARCHAR(255) column for order notes.

Question 3

A developer needs to write a complex query that involves several levels of aggregation and joins. To improve readability and maintainability, they want to break the query into logical, named steps. The intermediate result set from one step will be used immediately in the next step within the same query. Which SQL Server feature should be used?

Accepted Answer

Common Table Expression (CTE)

Answer

Temporary Table

Answer

Table Variable

Answer

View

Question 4

You have three AFTER INSERT triggers defined on a single table, `dbo.Orders`. There is a business requirement that `TriggerA` must execute before `TriggerB`, and `TriggerC` must execute after `TriggerB`. How can you enforce this specific execution order?

Accepted Answer

Use the `sp_settriggerorder` system stored procedure.

Answer

Rename the triggers alphabetically (e.g., trg_1_A, trg_2_B, trg_3_C).

Answer

Combine all trigger logic into a single trigger.

Answer

Set the trigger recursion server configuration option.

Question 5

Which of the following statements about error handling with TRY...CATCH blocks in T-SQL is TRUE?

Accepted Answer

A TRY block must be immediately followed by exactly one CATCH block within the same batch.

Answer

They can catch compilation errors, such as syntax errors.

Answer

They can catch errors with a severity level of 21.

Answer

The `ROLLBACK` command is automatically executed within the CATCH block.

Question 6

A developer is writing a stored procedure to insert a new employee record. The procedure must also add a corresponding record to the `EmployeeAudit` table, logging the user who performed the action. Which of the following is the most appropriate tool to ensure the audit record is always created when a new employee is successfully inserted, even if the insert happens outside of the stored procedure?

Accepted Answer

An AFTER INSERT trigger on the `Employee` table.

Answer

A scheduled SQL Server Agent job.

Answer

A Common Table Expression (CTE) within the insert procedure.

Answer

An INSTEAD OF INSERT trigger on the `Employee` table.