MCTS Database Management & SQL Server 3 — Questions and Answers
Question 1: Which SQL Server backup type backs up only the transaction log records since the last log backup?
- Full backup
- Differential backup
- Transaction log backup (Correct answer)
- Copy-only backup
Correct 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.
Question 2: What does the CHECKSUM option do when used with SQL Server BACKUP commands?
- Encrypts the backup file with AES-256
- Computes and stores a checksum to detect corruption during restore (Correct answer)
- Compresses the backup to reduce file size
- Splits the backup across multiple media families
Correct 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.
Question 3: A query uses a MERGE statement. Which DML operations can a single MERGE statement perform simultaneously?
- Only INSERT and UPDATE
- Only UPDATE and DELETE
- INSERT, UPDATE, and DELETE (Correct answer)
- Only SELECT and INSERT
Correct 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.
Question 4: Which SQL Server feature automatically maintains statistics about data distribution in table columns?
- Auto-parameterization
- Auto-update statistics (Correct answer)
- Query Store
- Resource Governor
Correct 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.
Question 5: What is the difference between TRUNCATE TABLE and DELETE FROM table in SQL Server?
- TRUNCATE can include a WHERE clause; DELETE cannot
- TRUNCATE is fully logged per row; DELETE uses minimal logging
- TRUNCATE uses minimal logging and resets identity; DELETE logs each row and preserves identity (Correct answer)
- TRUNCATE fires DML triggers; DELETE does not
Correct 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.
Question 6: In SQL Server, what is a covering index?
- An index that encrypts all included columns
- A non-clustered index that includes all columns needed by a query, eliminating key lookups (Correct answer)
- A clustered index that spans multiple filegroups
- An index that automatically covers all foreign key columns
Correct 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.
Question 7: Which SQL Server tool graphically shows the estimated or actual execution plan of a query, including cost percentages per operator?
- SQL Server Profiler
- SQL Server Management Studio (SSMS) execution plan viewer (Correct answer)
- Database Engine Tuning Advisor
- Extended Events session viewer
Correct 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.
MCTS Database Management & SQL Server 3
Which SQL Server backup type backs up only the transaction log records since the last log backup?