Enabling Partition Locking

Use sp_chgattribute to enable or disable partition-level locking. By default, partition locking is disabled.

Partition-level locking can be enabled only on user tables with more than one data partition. Partition-level locking can not be enabled for system tables and temporary tables.

The syntax is:

sp_chgattribute  objectname, 'ptn_locking’, value

Parameters:

objectname – is the name of the table on which to change ptn_locking.

value – set to 1 to enable and 0 to disable partition-level locking.

Permissions:

Only the object owner can execute sp_chgattribute.

Examples

This example enables partition-level locking for the authors table:

sp_chgattribute authors, "ptn_locking", 1

This example disables partition-level locking for the authors table:

sp_chgattribute authors, "ptn_locking", 0