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

The combination of a TRY...CATCH block and a transaction is the standard and most robust method for ensuring atomicity in SQL Server. The data modification statements are placed within a BEGIN TRANSACTION...COMMIT TRANSACTION block, which is itself inside a TRY block. If an error occurs, control passes to the CATCH block, where a ROLLBACK TRANSACTION statement can be executed to undo all the changes made since the transaction began.

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 clustered index determines the physical order of data in a table. For range queries, such as retrieving orders within a specific date range, using the 'OrderDate' column as the clustered index key is highly efficient. This is because all the data for a given range will be stored physically close together, minimizing I/O operations. A UNIQUEIDENTIFIER created with NEWID() would lead to random inserts and heavy page splitting, while a composite key on CustomerID and ProductID is less optimal for the primary query pattern described.

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

A Common Table Expression (CTE), defined using the WITH clause, is ideal for improving the readability and structure of complex queries. It allows you to define a temporary, named result set that exists only for the duration of a single statement. This is perfect for multi-step logical operations within one query without the overhead of creating a physical temporary table in tempdb.

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

SQL Server does not guarantee the execution order of multiple triggers on the same event by default. The `sp_settriggerorder` system stored procedure is specifically designed to control this behavior by allowing you to designate one trigger as the 'First' and another as the 'Last' to fire for a specific action (INSERT, UPDATE, or DELETE). By setting TriggerA as 'First' and TriggerC as 'Last', the desired order can be achieved.

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

A TRY...CATCH construct requires that the CATCH block immediately follows the END TRY statement of its corresponding TRY block, with no other statements in between, and they must exist in the same batch of T-SQL code. They cannot catch compile-time syntax errors or severe errors (severity 20 or higher) that terminate the session. Furthermore, transactions must be explicitly rolled back within the CATCH block; it is not an automatic action.

(MCTS) Microsoft Certified Technology Specialist Practice Test

(MCTS) Microsoft Certified Technology Specialist Practice Test

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