MCTS SQL Server Database Development 3

Question 1

Which type of trigger fires AFTER the DML statement completes but allows the operation to be rolled back?

Accepted Answer

AFTER trigger

Answer

AFTER triggers fire after the triggering DML statement and its constraint checks, but within the same transaction, so a ROLLBACK inside the trigger undoes the operation.

Question 2

What is the correct syntax to create a user-defined table type in SQL Server?

Accepted Answer

CREATE TYPE dbo.MyType AS TABLE (col1 INT, col2 VARCHAR(50))

Answer

CREATE TYPE ... AS TABLE is the correct syntax for defining a user-defined table type that can be used as a table-valued parameter.

Question 3

In SQL Server, what is a covering index?

Accepted Answer

A non-clustered index that includes all columns needed to satisfy a query without accessing the base table

Answer

A covering index is a non-clustered index that contains all the columns referenced by a query, eliminating the need for a key lookup.

Question 4

Which command is used to rebuild all indexes on a table and update statistics simultaneously?

Accepted Answer

ALTER INDEX ALL ON TableName REBUILD

Answer

ALTER INDEX ALL ON TableName REBUILD rebuilds every index on the table and automatically updates statistics as part of the rebuild process.

Question 5

What does the TRY...CATCH construct in T-SQL NOT catch?

Accepted Answer

Errors with severity level 10 or below (informational) and some severity 20+ that terminate the connection

Answer

TRY...CATCH does not catch informational messages (severity ≤10) or fatal errors (severity ≥20) that terminate the database connection.

(MCTS) Microsoft Certified Technology Specialist Practice Test

(MCTS) Microsoft Certified Technology Specialist Practice Test

MCTS SQL Server Database Development 3