Practice Test Geeks(MCTS) Microsoft Certified Technology Specialist Practice Test
← All MCTS Flashcard Decks

SQL Server Database Development Flashcards

7 cards from real MCTS practice questions. Tap to flip, then mark Knew It or Still Learning — missed cards come back until you master them.

Read the first 7 SQL Server Database Development flashcards as text

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

    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.

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

    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.

  3. In SQL Server, what is a covering index?

    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.

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

    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.

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

    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.

  6. Which T-SQL function returns the number of rows in a window frame up to and including the current row?

    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.

  7. In SQL Server, what is the difference between UNION and UNION ALL?

    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.

SQL Server Database Development Flashcards — MCTS Study Cards with Answers