• That depends. There is a lock escalation hierarchy, usually after page it is an extent (group of 8 pages). For more on escalation hierarchy, refer the link below:

    http://msdn.microsoft.com/en-us/library/ms189849(v=sql.105).aspx

    Furthermore, this hierarchy can be altered with ALTER TABLE commanding using SET ( LOCK_ESCALATION = { AUTO | TABLE | DISABLE } )

    There's no it depends situation for lock escalation. Row or Page lock only ever escalate to partition or table lock. There's no lock escalation as row to page to table\partition. I've not seen such behaviour.

    Before SQL 2008, row or page lock only escalate to table lock

    From SQL 2008 onwards, the below LOCK_ESCALATION

    AUTO - escalate row or page lock to table partition if available, otherwise the behaviour will be similar to TABLE if partition is not present

    TABLE - as the description imply, escalate row or page to table lock. This is the default behaviour

    DISABLE - do not escalate row or page lock to table lock in "most cases". Scanning a heap under serializable isolation will still require table lock to protect data integrity

    Hope this helps to debunk a myth.

    Simon Liew
    Microsoft Certified Master: SQL Server 2008