Explanation/Reference:
Explanation:
The NOLOCK hint allows SQL to read data from tables by ignoring any locks and therefore not being blocked by other processes.
This can improve query performance, but also introduces the possibility of dirty reads.
Incorrect Answers:
F: When SET XACT_ABORT is ON, if a Transact-SQL statement raises a run-time error, the entire transaction is terminated and rolled back.
G: DISABLE, not AUTO, would be better.
There are two more lock escalation modes: AUTO and DISABLE.
The AUTO mode enables lock escalation for partitioned tables only for the locked partition. For non- partitioned tables it works like TABLE.
The DISABLE mode removes the lock escalation capability for the table and that is important when concurrency issues are more important than memory needs for specific tables.
Note: SQL Server's locking mechanism uses memory resources to maintain locks. In situations where the number of row or page locks increases to a level that decreases the server's memory resources to a minimal level, SQL Server's locking strategy converts these locks to entire table locks, thus freeing memory held by the many single row or page locks to one table lock. This process is called lock escalation, which frees memory, but reduces table concurrency.
References: https://www.mssqltips.com/sqlservertip/2470/understanding-the-sql-server-nolock-hint/