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

Database Management & SQL Server 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 Database Management & SQL Server flashcards as text

  1. Which SQL Server isolation level prevents dirty reads but allows non-repeatable reads?

    Answer: READ COMMITTED

    READ COMMITTED prevents dirty reads by only reading committed data, but another transaction can modify the row before the current transaction re-reads it.

  2. What is the purpose of the NOLOCK query hint in SQL Server?

    Answer: Allows reading uncommitted data without acquiring shared locks

    NOLOCK (equivalent to READ UNCOMMITTED) allows a query to read data without acquiring shared locks, potentially returning dirty or phantom reads.

  3. Which filegroup type in SQL Server is used to store read-only data for improved performance?

    Answer: Read-only filegroup

    A read-only filegroup can be placed on slower storage since it never requires write I/O, and SQL Server skips logging for it.

  4. What does the SQL Server DMV sys.dm_exec_query_stats expose?

    Answer: Aggregate performance statistics for cached query plans

    sys.dm_exec_query_stats returns aggregate performance statistics (CPU, I/O, elapsed time, executions) for all cached query plans.

  5. A table has a clustered index on CustomerID. What happens when you INSERT a row with a CustomerID value that falls between existing rows?

    Answer: SQL Server inserts it in the correct logical position, potentially causing a page split

    Because a clustered index defines the physical order of data, inserting out-of-sequence values can cause page splits when a data page is full.

  6. Which SQL Server feature allows you to query historical data as it existed at a past point in time?

    Answer: Temporal Tables

    Temporal tables (system-versioned) automatically track row history in a history table, and FOR SYSTEM_TIME AS OF lets you query past states.

  7. What is the maximum number of columns allowed in a SQL Server composite index key?

    Answer: 16

    SQL Server allows a maximum of 16 key columns per index, with a combined key size limit of 900 bytes for non-clustered indexes (1700 for newer versions).