MS SQL Server - 57 - Row Versioning MCQ

57 – Row Versioning MCQ

1 / 25

1. What is row versioning in SQL Server?

A technique to maintain historical data versions.

A mechanism to delete rows permanently.

A way to create new rows in a table.

A method to lock rows for exclusive access.

2 / 25

2. Which feature in SQL Server allows you to track changes to rows over time?

Indexes

Primary keys

Row versioning

Triggers

3 / 25

3. What is the primary purpose of row versioning in SQL Server?

To improve query performance.

To provide a mechanism for locking rows.

To track changes to rows for data history.

To prevent concurrent access to data.

4 / 25

4. In SQL Server, which system database is used to store row versioning information?

model

tempdb

master

msdb

5 / 25

5. What is the primary benefit of using row versioning-based isolation levels in SQL Server?

Simplified query syntax.

Improved concurrency and reduced blocking.

Faster query execution.

Higher data consistency.

6 / 25

6. Which isolation level in SQL Server uses row versioning to provide a consistent snapshot of data?

SERIALIZABLE

READ COMMITTED

SNAPSHOT

READ UNCOMMITTED

7 / 25

7. How does SQL Server create row versions for data in tables?

By using external files to store versions.

By creating a new copy of the entire table for each version.

By deleting old versions and creating new ones.

By creating row-level copies of data as changes occur.

8 / 25

8. What is the purpose of the version store in SQL Server’s tempdb database?

To store backup versions of databases.

To store versions of data rows for row versioning.

To store indexes for faster data retrieval.

To store system metadata.

9 / 25

9. Which system dynamic management view (DMV) in SQL Server can be used to monitor the version store?

sys.dm_os_performance_counters

sys.dm_tran_version_store

sys.dm_exec_sessions

sys.dm_exec_requests

10 / 25

10. In SQL Server, which transaction isolation level uses row versioning to provide a consistent read-only snapshot of data?

READ COMMITTED

REPEATABLE READ

READ UNCOMMITTED

SNAPSHOT

11 / 25

11. What is a row version in SQL Server?

A snapshot of data at a specific point in time.

A user-defined data type.

A copy of the entire table.

A system-generated primary key value.

12 / 25

12. In SQL Server, which statement is true regarding row versioning-based isolation levels?

They use locks instead of row versions.

They require explicit versioning code in application queries.

They always provide the latest committed data.

They can reduce blocking and improve concurrency.

13 / 25

13. What happens to a row version when it is updated in SQL Server?

The original version is rolled back to its previous state.

The original version is locked until the update is committed.

The original version is marked as "modified."

The original version is deleted, and a new version is created.

14 / 25

14. In SQL Server, which isolation level can provide the highest level of data consistency with the least amount of locking?

SERIALIZABLE

SNAPSHOT

READ COMMITTED

READ UNCOMMITTED

15 / 25

15. What is the primary disadvantage of using row versioning in SQL Server?

Slower query performance.

Increased storage requirements.

Inability to track data changes.

Limited concurrency improvements.

16 / 25

16. Which SQL Server isolation level allows dirty reads but uses row versioning to prevent non-repeatable reads?

REPEATABLE READ

READ UNCOMMITTED

SNAPSHOT

READ COMMITTED

17 / 25

17. What is a common use case for row versioning in SQL Server?

Auditing changes to sensitive data.

Creating primary keys for tables.

Backing up databases.

Preventing all concurrent access to a table.

18 / 25

18. In SQL Server, what is a transaction’s interaction with the version store during a write operation?

It deletes all versions.

It locks the version store.

It compresses existing versions.

It creates a new version.

19 / 25

19. Which statement is true regarding SQL Server’s row versioning-based isolation levels?

They can improve concurrency but may increase tempdb usage.

They require manual version management.

They use locks exclusively for concurrency control.

They eliminate all forms of blocking.

20 / 25

20. What is the primary benefit of using row versioning-based isolation levels in SQL Server?

Improved query performance.

Increased data consistency.

Reduced storage requirements.

Enhanced data security.

21 / 25

21. In SQL Server, which isolation level uses row versioning to prevent non-repeatable reads but still allows phantom reads?

SNAPSHOT

READ UNCOMMITTED

READ COMMITTED

REPEATABLE READ

22 / 25

22. What is the purpose of the “SET TRANSACTION ISOLATION LEVEL” statement in SQL Server?

To set the default isolation level for the database.

To enable or disable row versioning.

To define a custom isolation level.

To specify a transaction's name.

23 / 25

23. What is a phantom read in SQL Server?

When a transaction reads a row that no longer exists.

When a transaction reads data from another transaction's version.

When a transaction reads the same row multiple times.

When a transaction reads uncommitted data.

24 / 25

24. Which system view in SQL Server can be used to monitor the tempdb version store?

sys.dm_os_performance_counters

sys.dm_exec_requests

sys.dm_tran_version_store

sys.dm_exec_sessions

25 / 25

25. In SQL Server, which isolation level uses row versioning to provide a consistent read-only snapshot of data, but allows dirty reads?

REPEATABLE READ

READ UNCOMMITTED

READ COMMITTED

SNAPSHOT

Your score is

MS SQL Server – 57 – Row Versioning MCQ