MCTS Database Management & SQL Server 3

Question 1

Which SQL Server backup type backs up only the transaction log records since the last log backup?

Accepted Answer

Transaction log backup

Answer

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?

Accepted Answer

Computes and stores a checksum to detect corruption during restore

Answer

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?

Accepted Answer

INSERT, UPDATE, and DELETE

Answer

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?

Accepted Answer

Auto-update statistics

Answer

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?

Accepted Answer

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

Answer

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.

(MCTS) Microsoft Certified Technology Specialist Practice Test

(MCTS) Microsoft Certified Technology Specialist Practice Test

MCTS Database Management & SQL Server 3