INF: Support of Page and Table Locking in SQL Server (46170)

This article was previously published under Q46170

SQL Server will lock only the page containing the updated row if an index was used to locate that row. If an index was not used to locate the row, by (for example) using the sp_lock() function, the entire table will be locked.

Using NOT in a WHERE clause or creating an index before data is loaded and not UPDATING STATISTICS can cause SQL Server to not use indexes.

If a non-clustered index is used to locate a row, the data page will be locked as well as the leaf-level index page used to locate the row, even if the value of the indexed column is not changed. If the row was located via clustered index, only the data page will be locked (unless the values of indexed columns are changed).