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 feature stores query execution history and plans so DBAs can detect plan regressions after upgrades?

    Answer: Query Store

    Query Store persists query plans and runtime statistics to the database itself, enabling forced plans and regression detection across SQL Server version upgrades.

  2. What SQL Server constraint ensures that a column value exists as a primary key value in another table?

    Answer: FOREIGN KEY constraint

    A FOREIGN KEY constraint enforces referential integrity by requiring that values in a child column match existing values in the referenced parent table's primary or unique key.

  3. In a SQL Server AlwaysOn Availability Group, what is the role of the secondary replica by default?

    Answer: It receives and applies transaction log records from the primary replica

    Secondary replicas continuously receive and redo transaction log records from the primary, maintaining a synchronized or asynchronous copy of the databases.

  4. Which T-SQL statement is used to view the current lock activity and blocking chains in SQL Server?

    Answer: SELECT * FROM sys.dm_tran_locks

    sys.dm_tran_locks shows all currently held and pending lock requests, including resource type, lock mode, and owning session, making it essential for diagnosing blocking.

  5. What is the purpose of the SQL Server FILESTREAM feature?

    Answer: Stores BLOB data (binary large objects) in the NTFS file system while managing it via T-SQL

    FILESTREAM integrates the SQL Server database engine with NTFS, storing varbinary(max) BLOB data as files on disk while still accessible through T-SQL or Win32 streaming APIs.

  6. Which SQL Server index type is optimized for analytical queries that scan large ranges of rows in a data warehouse?

    Answer: Columnstore index

    Columnstore indexes store data column by column with high compression, enabling batch-mode execution and dramatically faster aggregate scans over large datasets typical in DW workloads.

  7. What happens when you execute DBCC CHECKDB on a SQL Server database?

    Answer: It checks the logical and physical integrity of all objects in the database

    DBCC CHECKDB validates allocation structures, page and row integrity, index consistency, and inter-table consistency for every object in the target database.