Will remove SQL block tables that prevent new data from entering SQL Server 2005?

Question

If I try to remove the data brunch from the table. let's say

DELETE FROM myTable Where CreationDate < GetDate() 

which takes time to delete, will this table be locked and a new insert cannot be performed?

This table does not self-define itself. I would suggest that I can still insert new data while deleting it. Will delete sql use an exclusive lock that denies all access to the table?

thanks

Answer 1

You can use them to prevent locking the entire table:

 WHILE 1 = 1 BEGIN DELETE TOP 10000 FROM myTable WHERE CreationDate < GetDate() IF @@ROWCOUNT = 0 BREAK END 

Answer 2

There are many things that can change this behavior. However, if by and large you mean more than 5 thousand records than this is likely to be blocked. You can avoid this by using smaller lots or by specifying an isolation level that will not be blocked.