Today, at my workplace, we talked about locking and DBCC ROWLOCK came up. Many of you know, but I still want to say – it has been deprecated now.
If I am not mistaken, DBCC ROWLOCK was used in ver 6.5 to lock a row explicitly in INSERT operations. But that is automatic now. Locking is mostly managed automatically by SQL Server. However ROWLOCK hint can still be used. Its one of the table hints in SQL Server.
In SQL Server, you can have ROW level locking, page level, table level, etc. Starting from SQL Server 2008, you can also have partition-level locking.
Lock Escalation is how the Database Engine escalates lock from an object at lower hierarchy to an object at higher in the hierarchy (from example from ROW to Table)
Just a small writeup, I would talk a little more later !