Is it possible to force row level locking in SQL Server

Question

I can see how to turn off row level and page level locking in SQL Server  but I cannot find a way to force SQL Server to use row level locking  Is there a way to force SQL Server to use row level locking and NOT use page level locking

User · Answer

Use the ALLOW PAGE LOCKS clause of ALTER CREATE INDEX   ALTER INDEX indexname ON tablename SET  ALLOW PAGE LOCKS   OFF

User · Answer

You can t really force the optimizer to do anything  but you can guide it    UPDATE Employees WITH  ROWLOCK  SET Name  Mr Bean  WHERE Age gt 93   See   - Controlling SQL Server with Locking and Hints

User · Answer

You can use the ROWLOCK hint  but AFAIK SQL may decide to escalate it if it runs low on resources   From the doco      ROWLOCK Specifies that row locks are   taken when page or table locks are   ordinarily taken  When specified in   transactions operating at the SNAPSHOT   isolation level  row locks are not   taken unless ROWLOCK is combined with   other table hints that require locks    such as UPDLOCK and HOLDLOCK    and     Lock hints ROWLOCK  UPDLOCK  AND XLOCK   that acquire row-level locks may place   locks on index keys rather than the   actual data rows  For example  if a   table has a nonclustered index  and a   SELECT statement using a lock hint is   handled by a covering index  a lock is   acquired on the index key in the   covering index rather than on the data   row in the base table    And finally this gives a pretty in-depth explanation about lock escalation in SQL Server 2005 which was changed in SQL Server 2008    There is also  the very in depth  Locking in The Database Engine  in books online   So  in general   UPDATE Employees WITH  ROWLOCK  SET Name  Mr Bean  WHERE Age gt 93   Should be ok  but depending on the indexes and load on the server it may end up escalating to a page lock

[sql-server] Is it possible to force row level locking in SQL Server?

Examples related to sql-server

Examples related to sql-server-2005

Examples related to sql-server-2008

Examples related to rowlocking