Practice Test Geeks(MCM) Microsoft Certified Master Practice Test
← All MCM Flashcard Decks

SQL Server Flashcards

7 cards from real MCM 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 flashcards as text

  1. Which SQL Server feature enables you to encrypt data at rest by encrypting the entire database files, including log files, using a Database Encryption Key?

    Answer: Transparent Data Encryption (TDE)

    TDE encrypts the entire database at the file level using a Database Encryption Key, protecting data at rest without requiring application changes.

  2. In SQL Server, what happens to a transaction that encounters a deadlock?

    Answer: The transaction with the lowest deadlock priority is chosen as the victim and rolled back

    SQL Server's deadlock monitor selects a victim based on the lowest deadlock priority (SET DEADLOCK_PRIORITY) and, if equal, the transaction that is cheapest to roll back.

  3. Which extended event action captures the T-SQL text of a batch or statement currently executing when an event fires?

    Answer: sql_text

    The sql_text action captures the full text of the SQL batch or statement associated with the event at the time it fires.

  4. What is the function of a SQL Server 'Resource Governor' classifier function?

    Answer: Routes incoming sessions to workload groups based on session attributes

    The classifier function is a scalar T-SQL function that Resource Governor calls at session login to determine which workload group (and thus resource pool) the session belongs to.

  5. In SQL Server replication, which type of replication is best suited for scenarios where subscribers occasionally connect and need to synchronize accumulated changes?

    Answer: Merge replication

    Merge replication tracks changes at both publisher and subscriber and reconciles them during synchronization, making it ideal for occasionally-connected subscribers.

  6. Which SQL Server feature allows a DBA to capture a workload on a production server and replay it on a test server for performance analysis?

    Answer: Distributed Replay

    Distributed Replay captures a workload trace and replays it across multiple client machines to simulate concurrent production load on a test server.

  7. In SQL Server, what does the NOLOCK table hint actually do at the engine level?

    Answer: Reads data without acquiring any shared locks, allowing dirty reads

    NOLOCK (equivalent to READ UNCOMMITTED) skips shared lock acquisition entirely, allowing the query to read uncommitted data including rows being modified or rolled back.