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 backup type backs up only the transaction log records since the last log backup?

    Answer: Transaction log backup

    A transaction log backup captures all log records since the previous log backup, enabling point-in-time recovery and truncating the inactive log.

  2. What does the CHECKSUM option do when used with SQL Server BACKUP commands?

    Answer: Computes and stores a checksum to detect corruption during restore

    BACKUP WITH CHECKSUM computes a checksum over the backup data and stores it, so RESTORE can verify integrity and detect bit rot or media errors.

  3. A query uses a MERGE statement. Which DML operations can a single MERGE statement perform simultaneously?

    Answer: INSERT, UPDATE, and DELETE

    A MERGE statement can perform INSERT, UPDATE, and DELETE in a single pass based on whether source rows match, don't match, or match with specific conditions.

  4. Which SQL Server feature automatically maintains statistics about data distribution in table columns?

    Answer: Auto-update statistics

    Auto-update statistics detects when data changes exceed a threshold and automatically updates column/index statistics to help the query optimizer generate efficient plans.

  5. What is the difference between TRUNCATE TABLE and DELETE FROM table in SQL Server?

    Answer: TRUNCATE uses minimal logging and resets identity; DELETE logs each row and preserves identity

    TRUNCATE TABLE is minimally logged, resets IDENTITY seeds, and cannot be used with WHERE clauses, whereas DELETE is fully logged per row and fires triggers.

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

    Answer: A non-clustered index that includes all columns needed by a query, eliminating key lookups

    A covering index includes all columns referenced by a query (via key columns or INCLUDE columns), allowing the query to be satisfied entirely from the index without a clustered index lookup.

  7. Which SQL Server tool graphically shows the estimated or actual execution plan of a query, including cost percentages per operator?

    Answer: SQL Server Management Studio (SSMS) execution plan viewer

    SSMS can display graphical estimated or actual execution plans showing each operator, its cost percentage, row estimates, and warnings for missing indexes.