SQL Server: LOCK_ESCALATION setting

DBA/Developer authored the following Transact-SQL statement:

CREATE PARTITION FUNCTION PFunction1 (int)

AS RANGE LEFT FOR VALUES (2000, 3000);
CREATE PARTITION SCHEME PScheme1
AS PARTITION PFunction1 TO (fg1, fg2, fg3);
CREATE TABLE
TransDetails (TransID int, Description varchar(50), Region varchar(20),
Date datetime, TypeID int, EmpID int,Status bit)
ON PScheme1(TypeID);

DBA/Developer opens a session and executes the following Transact-SQL:

ALTER TABLE TransDetails
SET (LOCK_ESCALATION = TABLE);
GO BEGIN
TRANSACTION UPDATE TransDetails
SET TypeID = TypeID + 1000
WHERE TypeID > 4000;
GO

ALTER TABLE statement set LOCK_ESCALATION for the TransDetails table to TABLE, which is actually the default. Normally, table-level lock escalation is fine. However, with partitioned tables, DBA/Developer may not want to lock the entire table, but only a single partition, such as the partition containing the rows being updated in this scenario. This would allow queries against other partitions in the table to execute successfully without being blocked. By default, SQL Server will escalate locks to the table level as needed. However, to override this default behavior, or to disable lock escalation altogether, DBA/Developer can set the LOCK_ESCALATION setting for a table. The LOCK_ESCALATION setting can have one of the following values:

TABLE: Lock escalation is performed to the table level, which is the default.

AUTO: Lock escalation is performed to the table level for non-partitioned tables, and to the partition level for partitioned tables.

DISABLE: Lock escalation is disabled, and lock escalation does not typically occur. SQL Server only escalates locks in specific situations where it is absolutely required to ensure data integrity.

In this scenario, DBA/Developer could instruct SQL Server to escalate locks to the partition level only using the following statement:

ALTER TABLE TransDetails SET (LOCK_ESCALATION=AUTO);

Doing so would prevent lock escalation to the table level, and allow other queries to execute successfully on other unaffected partitions of the table. To identify the LOCK_ESCALATION setting for a table, DBA/Developer can query sys.tables. For example, the following statement would display the current LOCK_ESCALATION setting for the TransDetails table:

SELECT lock_escalation_desc
FROM sys.tables
WHERE name = 'TransDetails';