MS SQL Server Locking

Table level locking = Table level locking on all indexes

Index Level locking = Specific index level locking

Default setting when index gets created = (ROW = ON, PAGE = ON)

- SP statement to enable/disable specific index’s page level locking

exec SP_INDEXOPTION ‘tableName.indexName', 'AllowPageLocks', FALSE = ALTER INDEX [indexName] ON ..[tableName] SET ( ALLOW_PAGE_LOCKS = OFF)

- SP statement to enable/disable table’s page level locking

exec SP_INDEXOPTION ‘tableName', 'AllowPageLocks', FALSE = ALTER INDEX ALL ON ..[tableName] SET ( ALLOW_PAGE_LOCKS = OFF)

Possible values for enabling locking = TRUE, ON, 1, YES

Possible values for disabling locking = FALSE, OFF, 0, NO

Possible values for locking types = AllowPageLocks(case insensitive), AllowRowLocks(case insensitive), disAllowPageLocks(case insensitive), disAllowRowLocks(case insensitive)

Forcing sql server to use specific index while selecting

select * from tableName with(INDEX(T1_INDEX))