July 7, 2011 at 12:04 pm
Nice introduction to delete and truncate statements. Glad you mentioned the difference in how identity values are not reseeded with a delete command as that is definitely an important aspect to consider. That bit me once when I was starting out and I had to go back and fix the identity values manually. 🙁
July 7, 2011 at 8:06 pm
pnewhart (7/7/2011)
virat_108 (4/14/2011)
You will get better and easy explanation over here:http://dotnetpeoples.blogspot.com/2011/04/difference-between-delete-and-truncate.html
That blog post states that delete cannot be rolled back. This is inaccurate. Both truncate and delete can be rolled back if they are within a begin tran/commit/rollback statement.
That blog post also states that DELETE doesn't "activate triggers".
Careful with what you read because there's a lot of misinformation on the internet. 🙂
Best regards,
Best regards,
Andre Guerreiro Neto
Database Analyst
http://www.softplan.com.br
MCITPx1/MCTSx2/MCSE/MCSA
July 8, 2011 at 2:09 am
Hey Prashnat,
This is a beatiful article which you have poasted and very useful.
July 8, 2011 at 2:49 am
JustStarted (7/6/2011)
Good article. I knew that Truncate is minimal logged operation but didn't knew that it logs deallocation of data pages.I have one question though. I know Delete is row by row operations. What happens if we delete rows in bulk? How SQL Sever logs this operation? If we do bulk delete, is it possible to rollback if it is not wrapped in transaction?
Thanks..!
What do you mean by bulk delete? there is no 'bulk delete' such a command in sql.
Delete will write every rows in log file.
If you didn't start within the transaction then you can't rollback because SQL server by default auto commit the transaction.
Muthukkumaran Kaliyamoorthy
https://www.sqlserverblogforum.com/
July 8, 2011 at 9:39 am
Muthukkumaran Kaliyamoorthy, please refer to this post for your answer. I happen to post it in different window instead of replying to your post 😀
True there is no such command called "Bulk Delete" in SQL. But you can wrapped DELETE statement in transaction to delete records from your table. Deleting is bulk is faster than just issuing DELETE command. Here's the code snippet. It deletes records in batch of 100000 rows at a time.
BEGIN TRANSACTION
SET NOCOUNT ON;
DECLARE @totalRowint;
DECLARE @intRowCount int;
DECLARE @intErrNoint;
SELECT @totalRow = COUNT(*) FROM [Table] WITH (NOLOCK)
WHERE [Condition] OPTION(MAXDOP 2);
SELECT@intRowCount = @totalRow,
@intErrNo = 0;
WHILE @intRowCount > 0 AND @intErrNo = 0
BEGIN
SET ROWCOUNT 100000;
DELETE FROM [Table]
WHERE [Condition] OPTION(MAXDOP 2);
SELECT @intErrNo = @@ERROR
, @intRowCount = @@ROWCOUNT;
END;
SET ROWCOUNT 0; -- Reset batch size to "all"
END;
February 1, 2012 at 3:32 am
February 1, 2012 at 4:08 am
Hi for more practical explanation and conceptual funda visit
http://sqldebate.blogspot.in/2012/01/difference-between-truncate-and-delete.html
Viewing 7 posts - 61 through 66 (of 66 total)
You must be logged in to reply to this topic. Login to reply