6. February 2012
Locking is a necessary thing for better concurrency. SQL Server manages its locks automatically. In general: when a statement issues about 5000 locks on a single table or index, lock manager issues an escalation. Lock escaltion details can be found in BOL at here: http://msdn.microsoft.com/en-us/library/ms184286.aspx
What if I have partitioned table? Do I really need to lock the whole table or index? I believe, most of the cases, answer is no. It may be enough to lock that particular partition I'm working with. You can achieve this on your table with the script below:
ALTER TABLE [Table1] SET (LOCK_ESCALATION = AUTO);
When you have locking issues, you may consider to change thie setting on your table.