Default SQL Server 2008 locking behaviour

I am using SQL Server 2008.

-
What is the default lock behaviour with UPDATE?

• NOLOCK?

• ROWLOCK?

• PAGLOCK?

-
How can I tell what the current level of lock is for a table?

Thanks,
Bruce

It really depends on how much you're updating. Locks will escalate as the size of the query increases. If many rows within the same page will be modified, SQL Server will escalate to a PAGLOCK. If many pages will be modified, it will escalate to a TABLOCK.

StackExchange Database Administrators Q#9177, answer score: 3