MCTS SQL Server Database Development 3 — Questions and Answers
Question 1: Which type of trigger fires AFTER the DML statement completes but allows the operation to be rolled back?
- INSTEAD OF trigger
- AFTER trigger (Correct answer)
- CLR trigger
- DDL trigger
Correct answer: AFTER trigger
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?
- CREATE TYPE dbo.MyType AS TABLE (col1 INT, col2 VARCHAR(50)) (Correct answer)
- CREATE TABLE TYPE dbo.MyType (col1 INT, col2 VARCHAR(50))
- DECLARE TYPE dbo.MyType TABLE (col1 INT, col2 VARCHAR(50))
- CREATE OBJECT TYPE dbo.MyType AS TABLE (col1 INT)
Correct answer: CREATE TYPE dbo.MyType AS TABLE (col1 INT, col2 VARCHAR(50))
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?
- An index that covers all tables in a JOIN
- A non-clustered index that includes all columns needed to satisfy a query without accessing the base table (Correct answer)
- An index that enforces referential integrity
- A filtered index that covers a partial range of rows
Correct answer: A non-clustered index that includes all columns needed to satisfy a query without accessing the base table
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?
- ALTER INDEX ALL ON TableName REORGANIZE
- ALTER INDEX ALL ON TableName REBUILD (Correct answer)
- UPDATE STATISTICS TableName WITH FULLSCAN
- DBCC INDEXDEFRAG(0, 'TableName')
Correct answer: ALTER INDEX ALL ON TableName REBUILD
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?
- Arithmetic overflow errors
- Constraint violation errors
- Errors with severity level 10 or below (informational) and some severity 20+ that terminate the connection (Correct answer)
- Deadlock errors
Correct answer: Errors with severity level 10 or below (informational) and some severity 20+ that terminate the connection
TRY...CATCH does not catch informational messages (severity ≤10) or fatal errors (severity ≥20) that terminate the database connection.
Question 6: Which T-SQL function returns the number of rows in a window frame up to and including the current row?
- LEAD()
- LAG()
- SUM() OVER (ORDER BY col ROWS UNBOUNDED PRECEDING) (Correct answer)
- FIRST_VALUE()
Correct answer: SUM() OVER (ORDER BY col ROWS UNBOUNDED PRECEDING)
SUM() with the OVER clause and a ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW frame specification computes a running total.
Question 7: In SQL Server, what is the difference between UNION and UNION ALL?
- UNION ALL removes duplicate rows; UNION keeps them
- UNION removes duplicate rows; UNION ALL keeps all rows including duplicates (Correct answer)
- UNION can combine different column counts; UNION ALL cannot
- UNION ALL requires matching data types; UNION does not
Correct answer: UNION removes duplicate rows; UNION ALL keeps all rows including duplicates
UNION performs a DISTINCT operation to eliminate duplicate rows, while UNION ALL returns all rows including duplicates and is generally faster.
MCTS SQL Server Database Development 3
Which type of trigger fires AFTER the DML statement completes but allows the operation to be rolled back?