SQL Server Database Development Flashcards

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?

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

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.

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?

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

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.

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?

Answer: Common Table Expression (CTE)

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.

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?

Answer: Use the `sp_settriggerorder` system stored procedure.

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.

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

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

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.

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?

Answer: An AFTER INSERT trigger on the `Employee` table.

An AFTER INSERT trigger is a special type of stored procedure that automatically executes after a data modification event (in this case, an INSERT) occurs on a table. By placing the logic to insert a record into the `EmployeeAudit` table within an AFTER INSERT trigger on the `Employee` table, you guarantee that the audit action will occur whenever an insert is successfully committed, regardless of the application or method used to perform the insert.