Truncate Vs Delete and Table Locks SQL Server 2012

Question

Right now I am having an issue with a stored procedure that is locking up when running.

It's a conversion from Sybase.

The stored procedure originally would do

TRUNCATE TABLE appInfo

Then repopulate the data within the same stored procedure, but in SQL Server this seems to be causing locks to the users.

Its not a high traffic database.

The change I tried was the to do

BEGIN TRAN
DELETE TABLE appInfo
COMMIT TRAN

Then repopulate the data, but the users are getting a NO_DATA_FOUND error on this one.

So if I TRUNCATE they get data, but it causes a lock

If I do a delete there is no data found.

Anyone have any insight into this condition and a solution? I was thinking of taking the truncate out to a separate stored procedure and called from within the parent procedure, but that might just be pushing the issue down the road and not actually solving it.

Thanks in advance

Answer 1

When you truncate a table the entire table is locked (from MSDN https://technet.microsoft.com/en-us/library/ms177570%28v=sql.105%29.aspx - TRUNCATE TABLE always locks the table and page but not each row.) When you issue a delete table it locks a row, deletes it, and then locks the next row and deletes it. Your users are continuing to hit the table as it is happening. I would go with truncate because its almost always faster.